Forum Discussion

_anomDiebolt_'s avatar
_anomDiebolt_
Qrew Elite
10 years ago

Got CSV To Import?

I wanted to show you a simple way to read a CSV file and process it before sending it to QuickBase. Normally when a file input control is on a form you select a file from the local file system and the file gets sent directly to the server when the form posts. However, with the HTML5 File API you can actually read the selected file, manipulate the contents and send the results to the server through an AJAX call so the host page never reloads.

To simplify the procedure even further I wrote a carefully crafted piece of code to make the process dead simple and allow the greatest level of customization for integration with QuickBase. Here is the code:

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=280

In a nutshell all you have to do is place a file control on your page and give it an id such as myFile shown below:

<input type="file" id="myFile" accept=".csv">

After a file is selected by the user, the CSV data will magically show up and can be accessed through the variable csv in the following short snippet of code where the same id is passed to GotCSV():

gotCSV("myFile").then(function(csv){

//your code here

console.log(JSON.stringify(csv, null, " "));

});

So if the CSV file contained the following data:

name,phone,email

John,(800) 555-1212,john@doe.com

Jane,(888) 555-1212,jane@doe.com

Baby,(877) 555-1212,baby@doe.com

You will be able to access the csv variable with properties corresponding to the column names:

[

{

"name": "John",

"phone": "(800) 555-1212",

"email": "john@doe.com"

},

{

"name": "Jane",

"phone": "(888) 555-1212",

"email": "jane@doe.com"

},

{

"name": "Baby",

"phone": "(877) 555-1212",

"email": "baby@doe.com"

}

]

Once you have the CSV represented as a JavaScript object you can go to town manipulating it. It now becomes a trivial matter to transform or filter the incoming CSV data prior to importing it into QuickBase.

Notes:

(1) The function gotCSV() wraps the two asynchronous processes (a) of the user selecting a local file and (b) reading the local file using the HTML5 File API and returns a promise.

(2) When the promise resolves the data in the selected file will be converted to an object and available as the JavaScript variable csv.

(3) This code is very generic and will allow you a perform a wide variety of tasks such as:

  • cleaning up dirty data prior to import
  • normalizing data
  • removing duplicate data
  • prevent importing a redundant data
  • converting quantities prior to import
  • supplying default data prior to import
  • split the raw CSV data into parent/child components prior to import

(4) to use the code the only thing you have to do is place your custom code where the comment "your code here" resides. The promise returned by gotCSV() will insure that your code automatically runs as soon as the file is selected and read by the HTML5 File API.

(5) This code should work in all evergreen browsers including IE11:

http://caniuse.com/fileapi

(6) It may take some time to grok what this code is doing but I assure you this is as extremely powerful technique that can probably greatly streamline your workflow if implemented.

(7) Enjoy!

APIs and custom code
Data import and export
NotificationsEmail

21 Replies

Sort By
  • DineshRampal's avatar
    DineshRampal
    Qrew Cadet
    10 years ago
    Hi dan, Very supportive of you and thanks for quick response. I will definitely try this.
    Have some simple followup questions.
    1)- Once my Code is done and i wish to execute the import functionality, do i call the API to import CSV or i can pass the control to QB Native import functioality, which does a lot...for e.g. it matches .csv columns with QB column names. As i understand and let me take a guess (obviosuly no marks for it), your answer would be to call the API. if i do so, don't i loose the column matching capability of QB default import functionality. Looks like that is a small giveway or am i wrong somewhere?
    I will try this, for sure.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    You have to call the API. This code reassembles the CSV to call API_ImportFromCSV:

    gotCSV("myFile").then(function(csv){
      console.log(JSON.stringify(csv, null, "  "));
      var names = _.pluck(csv, "name");
      console.log(names);
      
      var csv_blob = _.map(csv, function(row) {
        var items = [];
        items.push(row["name"]);
        items.push(row["phone"]);
        items.push(row["email"]);
        return items.join(",");
      }).join("\n");
      console.log(csv_blob);

      var promise = $.post(dbid, {
        act: "API_ImportFromCSV",
        records_csv: csv_blob,
        clist: clist,
        skipfirst: "0"
      });

      $.when(promise).then(function(xml){
        alert("imported!");
      });

    });

    The above code didn't transform the data in any way buy you can imagine that any manner of filtering or transformation can take place once you have the csv object isolated. I threw in a pluck instance for you (look for names).
    • WilliamEvans1's avatar
      WilliamEvans1
      Qrew Cadet
      6 years ago
      Hi,

      I was able to implement this, based on the example here - and it worked great except if a field contains a non-ASCII character such as a Spanish word (e.g. ma�ana) then it's imported as ma�1�2ana .... 

      Any tips as to how to fix this? Thanks!
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      6 years ago
      You can remove diacriticals with code similar to this example:

      var str = "�, �, �, �, �, �, �, �, �, �, �, �";
      str.normalize('NFD').replace(/[\u0300-\u036f]/g, "") 
      // "c, a, e, i, o, u, a, e, o, a, o, a"
    • WilliamEvans1's avatar
      WilliamEvans1
      Qrew Cadet
      6 years ago
      Thanks, this worked well... I spent ages trying to get Encoding to work in the CSV import but could not get it right... although native quickbase's import table function respects file encoding, getting it to work with the API seems very difficult... but normalizing it is fine as the whole app is in English anyway
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    Dan - thank you as just getting into coding with our application and this is very helpful for what we need to do. However, I'm not clear as to how (or if) this would work from the main dashboard using the widgets. I tested your code by creating a html page and then attach it to a web page widget. The html button to select a file seems to work but it never gets to the js function. Is there something else I need to do?
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    I posted a self contained HTML file that you can use anywhere - inside or outside of QuickBase. What the file demonstrates is that you can read the csv out of a file selected through a file control and log the csv to the console. To see the console output press F12 in any modern browser. The demo stops there. You have to do something further with the CSV other than log it to the console to make the example useful. If you are new to script you should learn how to use the JavaScript console as it will be your greatest aide in debugging and developing script.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    Thank you - the console advice is very helpful. Before my last post I tested your HTML outside of QB with a simple alert statement and it worked fine. That's why I figured it was something I was doing wrong with the web page widget. The console has allowed me to see that is it something with 'ran insecure content' with the 'd3.v3.min.js' script reference. If I comment out that script line and the subsequent parse call, it works fine and gets to my alert so I will further investigate my issue. Thanks again.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    One additional issues I am running into with code to mimic this approach. I have a master table called Opportunity that has children in table Task. For a given opportunity, I want to modify the records in the task table (say delete the completed date for every task related to the opportunity). This technique seems perfect as I planned to grab the record ids for each task after doing a simple API_DoQuery based on related opportunity. Then I will build a csv string and call API_ImportFromCSV. The problem with the code below is that instead of listing each task record id and then a new line, it concatenates all the ids together then does a new line. This is repeated for how ever many tasks I have. The xml returned looks fine, so could it be something with my each loop?

    // parentid is the record id of the opportunity
    // column 48 is related opportunity
    var query2 = "{48.EX." + parentrid + "}";
    var clist2 = "3";

    // initialize ajax
    $.ajaxSetup({data: {apptoken: apptoken}});

    // find all task record ids for a certain opportunity
    var promise4 = $.get(tbl_tsk,{
    act: "API_DoQuery",
      query: query2,
      clist: clist2
    });

    // query then build csv string
    $.when(promise4).then(function(xml4){
         var csv = "";
        $(xml4).find('record').each(function() {
              csv += $("record_id_",xml4).text() + "/n";
           });
         console.log(csv);
    });
  • eduardoeduardo's avatar
    eduardoeduardo
    Qrew Trainee
    10 years ago
    I have no experience in programming, but using the cut a paste technique (JavaScript, jquery etc.) with success, cause I do understand the logic.

    I have learned a lot with your answers Daniel, and implemented most of your techniques (IOL , getscript and so). Thanks a lot on advance.

    I do spend lot of time with language syntax cause sometimes things �obvious for the profs, are not so obvious for the newbies.

    Automating Importing CSV files (or tab delimited) was something my process needed.

    I have successfully implemented the lines of code in this thread and are working.

    My dream is to be able to manipulate the CSV JavaScript object prior to importing to match my table. (before creating the csv_blob)
    Starting with two features

    1.-MY csv file has to columns
    [Date]: YYYYMMDD, which I want to format DDMMYYYY
    [Value]: xxxxxx.yy, which I want to format xxx.xxx,yy

    2. - Want to create a new column in the object, so as to create a Unique Record, previous to importing, thus reducing the possibility of duplicating entries.

    {New column] concatenating [NAME+Date+Value]. This creates a unique record in my configuration.

    My idea is to include the code where you say �your code comes here�

    Could you help me or assist me where to find something simple.