I found a way using a combo of HTML and Javascript/jquery code to upload data from a CSV file into any form in your live app row by row. It is heavily based on a bit of code that @PeterDay82531 put onto the forum a while ago which was a huge help in getting this to work (thank you!). I wanted to be able to get data from the file like in Peter’s code, but I needed the data to still follow any rules that I so carefully designed. Therefore I made it so the data still has to go through the form and the user still has to hit “submit” after the data uploads into the form.
Here’s what you need to know for this code:
- view_XXX is the view number of the form you are going to fill out
- All of the field numbers for the object that the form submits and how they match with the columns of data on your CSV file (always use the same template file for uploading)
- For this to work, you need to make a rich text view on your form’s page which creates the button for a file selection. In the HTML of the rich text view, paste this:
- Optional: make another rich text view where your uploaded data will display, but leave it empty. This view is called view_DDD in the code below.
I hope this is helpful for others like Peter’s code was for me. I hope it can be improved and adapted in this forum over time!
$(document).on('knack-view-render.view_XXX',function(event,view,data){
//1. Function to process data in row "n" from a file
function processData(allText,n){
var allTextLines=allText.split(/\r\n|\n/);
var headers=allTextLines[0].split(',');
var lines=[];
var MyData={};
for(var i=1;i<allTextLines.length;i++){
var data=allTextLines[n].split(',');
if(data.length==headers.length){
var tarr=[];
for(var j=0;j<headers.length;j++){
tarr.push("<br><strong>"+headers[j]+":</strong> "+data[j])}
lines.push(tarr); //This will give you a variable called "lines" for if/when you //want to display the data from row n somewhere. The headers are bold and each one is separated by a line break(replace"<br>" with your desired separator)
//In your CSV file, match the columns in it to the fields in the object that the form corresponds to starting with zero:
MyData={field_001:data[0],
field_002:data[1],
field_003:data[2]
//continue with all fields in your object...
};
newrecord(MyData);//Call the function that populates the fields in the form
displaydata(lines[n])//Call the function that displays the uploaded data into a blank rich //text HTML element on your page.
}}};
//2. Function to populate form fields--------------------------------------------------------------
//Different kinds of fields have slightly different formatting:
//For a free entry field: $('#field_AAA').val(data.field_AAA)
//For a multiple choice or date field: $('#view_XXX-field_AAA').val(data.field_AAA)
//For a connection field:$('#view_XXX_field_AAA_chzn > div > div > input[type=text]').val(data.field_AAA)
// ^unfortunately the best I could do with these is auto populating the connection field's search bar with the file data value. You still need to manually select the desired connection.
function newrecord(data){
$('#field_001').val(data.field_001);
$('#view_XXX-field_002').val(data.field_002);
$('#view_XXX_field_003_chzn > div > div > input[type=text]').val(data.field_003);
//Continue with all fields in your form...
};
//Function to display the data in a HTML element if desired-------------------------------------
//If you'd like the uploaded data from the row to display on the page so the user can see it, use this function.
//First, make a rich text view on the page where your form is and leave it blank. Let's say it's called view_DDD.
function displaydata(text){
$('#view_DDD').html("DATA UPLOADED:<br>"+text+"<br>Display a message here too if desired.")};
//HOW TO MAKE A FILE UPLOAD BUTTON-------------------------------------------------------
// This code is triggered when the prescribed button is clicked. A file upload button can be embedded
// in a Rich Text view on the builder app by pasting the following into the HTML area of the Rich Text field:
// <input type="file" id="file"><input type="button" id="load-file" value="Load">
// or you can create a .kn-button and replace an event for this button being clicked.
// Once the file is loaded it calls processData which loops through it, strips out the ‘,’ and
// loads the data into an array called MyData
//Function to prompt the user for a row number, read file, and call the above functions when "load" is pressed.
$('#load-file').click(function(){
var MyData={};
var userInput=prompt("Which data row would you like to load?:");
var row=parseFloat(userInput);
if(!window.FileReader){
return alert('FileReader API is not supported by your browser.')}
var $i=$('#file'),
input=$i[0];
if(input.files&&input.files[0]){
file=input.files[0];
fr=new FileReader();
fr.onload=function(){
processData(fr.result,row)}; //Calls the functions above.
fr.readAsText(file);
alert("Row "+row+" loading.")}
else{alert("File not selected or browser incompatible.")}})
});
