Loading csv file into jQuery?

Question

I have a CSV file that I would like to use as source data for a jQuery flot graph.

Should I:

1. Find a jQuery plugin that will load the CSV file directly?
2. Convert the CSV file into JSON and use that instead?
3. Do something completely different?

I'm not having much luck finding a jQuery plugin that can cope with an external CSV file, but maybe I'm missing something.

Answer 1

No server required...

This can be accomplished without the server if you employ a little ingenuity.

You'll need 2 things:

• A browser that is HTML5 File API capable
• The jQuery-CSV library

The jquery-csv parser library specializes in parsing RFC 4180 compliant CSV, with many capabilities above and beyond just transforming CSV data into JavaScript data. For the purpose of this demonstration we're going to use two features:

The first is the toArrays() method. It takes a multi-line CSV string and transforms it into a 2D array.

The second is a user-defined parser hook that automatically transforms the output (which is all strings) into scalar (ie integer/float) data. Basically, through the use of a function callback, it's possible to inline additional processing into the parser.

Once you have the CSV data assigned to a string variable the transform to JavaScript data is very simple.

var csv = "" // this is the string containing the CSV data
var data = $.csv.toArray(csv, {
  onParseValue: $.csv.hooks.castToScalar
});

That's all for the CSV parsing step.

---

Now, what Flot expects is an array of 2D arrays where each 2D array contains an independent dataset.

To build your data create an empty array first:

var flotData = [];

Then for each data set you generate using CSV you simply add the dataset to the collection.

var flotData.push(data);

File handling via HTML5 is a tricky topic because it uses asynchronous callbacks to load files; that means no return statements. To keep things simple, I'll just make the flot plot a global object.

First initialize the plot during the $(document).ready():

var data = [];
flot = $.flot($('#plotdiv'), data, options);

Note: A dummy data object is added so the graph can be initialized.

Then, add a file handler:

// handles csv files
function handleFileSelect(evt) {
  var files = evt.target.files; // FileList object

  // reset the flot dataset
  flot.setData([]);
  for(var i=0, len=files.length; i<len; i++) {
    flotFileData(files[i], i);
  }
}

Assume that this can load one-or-more CSV data files. This gets called after you choose your files from the file dialog. The data gets reset to empty (ie []) because we want to start fresh after every time the file dialog gets executed; otherwise you'll be appending to a previous dataset.

This will cycle through the files and call flotFileData() for each file that was selected in the file dialog..

Here's the code for handling the file open callback:

function flotFileData(file, i) {
  var reader = new FileReader();
  reader.readAsText(file);
  reader.onload = function(event){
    var csv = event.target.result;
    var newData = $.csv.toArrays(csv, {
      onParseValue:$.csv.hooks.castToScalar
    });
    var data = flot.getData();
    data[i] = newData;
    flot.setData(data);
    flot.setupGrid();
    flot.draw();
  };
  reader.onerror = function(){ alert('Unable to read ' + file.fileName); };
}

This reads the file as plain-text and makes the contents available via event.target.result. The CSV parser transforms the CSV to scalar data in 2-dimensional array form. To stack multiple datasets we need to append to the data that's already in the plot, so we need to store the existing data first via flot.getData(). Append the new data, set it via flot.setData() and finally re-draw the plot.

Note: If the ranges of the graph don't need to be re-calculated you can skip the flot.setupGrid() call.

Ideally, the redraw should only happen once per file loading phase but this demo hasn't been optimized yet. That means, the graph will be re-drawn for every file that's read (definitely not ideal).

If you want to see it in action, take a look at the 'Flot Demonstration' provided by the jquery-csv project. I suggest you try loading both the analytics1.csv and analytics2.csv datasets so you can see how both are overlaid on the graph.

If you decide to go the server-side route to load the CSV files, there's also a more basic example that demonstrates loading the CSV from a textarea.

Disclaimer: I am the author of jquery-csv.

Update:

Due to the shuttering of Google Code, jquery-csv has been moved to GitHub

Answer 2

Use the jQuery CSV plugin to get an array. Build / sort the array however you need for the chart.

jQuery CSV Plugin

It just occured to me you may be thinking of reading a flat CSV file with jQuery. That's not possible. Giving javascript access to the filesystem sounds like a terrible idea. You can always use $.get() to load a file on a server, though.