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:
(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!
