Importing a CSV File using your own code

Its has been a while since I posted. But thought I would share a bit of code I have just developed. Its raw but works for my purpose. It will let you upload a csv file into a nominated object (Database). I hope the community finds it useful and improves on me first attempt.

 

Peter

 

$(document).on('knack-view-render.view_##', function (event, view, data) {

 

//-----------------------------------------------------------------------------------------

// This function will take the csv file that is uploaded using #load-file click

//event below

//------------------------------------------------------------------------------------------ 

 

function processData(allText) {

   

    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[i].split(',');

        if (data.length == headers.length) {

 

            var tarr = [];

            for (var j=0; j<headers.length; j++) {

                tarr.push(headers[j]+":"+data[j]);

            }

            lines.push(tarr);

         

         

//Create the data for the record POST     

          MyData = {

                      

                field_108 : data[0],//Replace with your fields in your object. Should match your csv file

                field_109 : data[1],

                field_110 : data[2],

                field_111 : data[3]

               

              }

//Create a new record for each line of the csv file            

             newrecord(MyData)

        }

    }

     //alert(lines); //used if just showing the output

}

 

 

 

 

 

 

 

 

 

/*---------------------------------------------------------------------

Peter Day 27/1/2019

Function to create a new record

---------------------------------------------------------------------*/

function newrecord(Mydata)

{

   $.ajax({

    url: 'https://api.knack.com/v1/objects/”Your object ie. Object_5”/records',

    type: 'POST',

    headers:

    {

   'X-Knack-Application-Id': "API ID",

   'X-Knack-REST-API-Key': "API Key"

    },

    data : Mydata,

     success: function(data)

    {

     alert('Successful New');

    }

  });

}

 

//-------------------------------------------------------------------------------

// Load file actions when the prescribed button is clicked. This can be embedded

// in a Rich Text Field  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 simply 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 into an array to POST using the API

//--------------------------------------------------------------------------------

 

  $( '#load-file' ).click( function () {

            //alert("here");          

 

     var MyData = {};     

    if ( ! window.FileReader ) {

                                    return alert( 'FileReader API is not supported by your browser.' );

                        }

                        var $i = $( '#file' ), // Put file input ID here

                                    input = $i[0]; // Getting the element from jQuery

                       

    if ( input.files && input.files[0] ) {

           

      file = input.files[0]; // The file

                                    fr = new FileReader(); // FileReader instance

                                    fr.onload = function () {

                                    processData(fr.result);

             };

                                    fr.readAsText( file );

                                    //fr.readAsDataURL( file ); // Swap with the above if a JSON file

                        } else {

                                    // Handle errors here

                                    alert( "File not selected or browser incompatible." )

                        }

     });

 

});

 

 

 

Does anyone know how to change the code so that the scene reloads once all the records are imported?

Or, how should the code be changed to show an Alert once ALL the records are imported (instead of the alert that now shows every time a new record is added)?

Thanks!

Thanks Justin,

Appreciate the information.  I'll look into this and see if I can move it forward.  Any findings will post up ;-)

 

Vin

For Connections you need to post the guid (knack calls record.id) rather than the identifier. If coming from a csv you will not have that value. You would need to add a function to the script that searches for each identifier and returns the corresponding guid, then use that value in place of whatever value was in that place on the csv. Not easy to write but it would be an awesome upgrade to this script. you could also parse a connection value from the page you're on it if happens to be there already. ie you are on a customer page and want to upload transactions to that customer and every connection field that you need to populate is represented on the rendered page you are currently on. that would look something like this:

//Use the inspector to identify the selector of the connection field displayed on the page.

var myConnectionField = $('#getSelector).text();  //replace getSelector with your selector.

//guid is stored in the span so use .text()

console.log('my connection is: ', myConnectionField); //test that you are getting the record id value from the span

//should look like this '54325435454rerefd5454efdr54' ie a random string of letters and numbers.

managed to get this working cool! However I'm having dificaulty with relationhip fields being populated

I know this is an old post.  But I'm new to knack and not sure at present how customisation works.  Would it be possible for someone to talk me through the process above including adding buttons etc..  I unaderstand the concept but its just not working for me this side. 


Thanks in advance

Vin

Wow! I have some code to do this same thing (which I found online years ago.) It was hundreds and hundreds of lines of code.

This is so much more readable and manageable. Thank you!

This is some excellent bit of code! Thanks Peter.
Is there an easy way that you'd use the ID of the active page to create connected records?

EDIT: I managed to figure it out by adding a variable outside the processData function that gets the record ID from the rendered Details view, and a line to the MyData initialisation, field_5 being the connected record field, and [recordID] being the variable of the connected ID.
Haven't yet tried to get more than one connection field, nor a connection field with a many relationship.

Thank you for this Peter!

 

The records in the CSV file are added to my object perfectly. I also need another field in the same object to be automatically updated with the specific logged in user's name - so that the added fields are linked to the connected user.

How do I manage that?