Custom Javascript code that lets you upload data through the front-end into a form from a CSV file one row at a time

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:

  1. view_XXX is the view number of the form you are going to fill out
  2. 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)
  3. 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:
  4. 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.")}})
});
6 Likes

I’ve updated this code so that there’s a button to load the next row so you don’t have to remember which one you’re on every time. It involves adding a new button into that rich text HTML on the Knack builder, so paste this:

So to be more clear, the whole HTML content of that rich text view should be this:



It also involves adding a global variable for the row number, which I call “currentRow”. You can add this wherever as long as it’s after the first row of code. I’m going to put it right above the click functions to show what it looks like. From there, everything is the same until the "$(‘load-file’).click function. Here’s the new “.click” functions:

  var currentRow = 0;
 $('#load-file').click(function(){
        var MyData={};
        var userInput=prompt("Which data row would you like to load? (start at 1...):");
        currentRow=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,currentRow)};
                fr.readAsText(file);
                alert("Row "+currentRow+" loading.")}
        else{alert("File not selected or browser incompatible.")}});

//Function to load the next row when the load next row button is clicked
    $('#next-row').click(function(){
        currentRow++;
        var MyData={};
        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(){
                processLossData(fr.result,currentRow)};
                fr.readAsText(file);
                alert("Row "+currentRow+" loading.")}
        else{alert("File not selected or browser incompatible.")}})

I found that this makes the data uploading process much faster. Hope this helps!

2 Likes

Not sure why the HTML won’t show up but here it is again:

<p>
<input type="file" id="file">
<input type="button" id="load-file" value="Load">
<input type="button" id="next-row" value="Load Next Row"><br>
</p>
2 Likes

This is great. Do you happen to have a page where I can see/test how this works?

Hi there,
Unfortunately, I do not have the ability to create and/or share any kind of “dummy” site to allow users to try this out, as I use Knack at the company I work for. Sorry about that! Calling any Knack employees or forum gurus- do you have any sites you could “lend” me as a builder so I could demonstrate this code? Thanks!

1 Like