Best method of getting Javascript data from a simple 3-row CSV

Question

I have a simple CSV (imported using JQuery's $.ajax from the /data/league.csv on the same site) with three lines in this exact format:

"Kimberlin Library","Queen's Building","Innovation Centre","etc etc"
8,2,0,-2
1,0,-1,0

which I'd like to get into this format (to use building and percent as data for the x-y axes in Highcharts, and also to populate a list with all three):

var leaguetable = {
    building: ["Kimberlin Library","Queen's Building","Innovation Centre","etc etc"],
    percent: [8,2,0,-2],
    change: [1,0,-1,0]
};

embarrassingly trivial, but I keep drawing a blank, despite trying other people's methods (including split(/\r\n|\n|r/), searching for /^(.*)$/m), and this question), so prepared to start from scratch. I need something as simple as possible, either JQuery or pure Javascript. For a similar problem I ended up converting the file to JSON, but I'd like to avoid that if possible.

I'm just curious; why do you want it in that format? When you're actually doing something with the data, I'd imagine it would be nicer to work with an array of objects containing the data rather than an object containing arrays containing data. — icktoofay, Sep 16 '11 at 01:15
Can you post a sample of the CSV? There's a lot of different varieties of CSV, and posting a sample may help. — icktoofay, Sep 16 '11 at 01:17
Why not use something like this? http://code.google.com/p/js-tables/wiki/CSV — Hemlock, Sep 16 '11 at 01:45
@Wes,Jonathan M: see above; icktoofay: I use the arrays directly in Highcharts for the axes; Jonathan M: I usually only ask if I'm really stuck but end up answering some questions myself; Hemlock: thanks for the link — Dave Everitt, Sep 16 '11 at 09:09

Jonathan M · Accepted Answer · 2011-09-16T03:29:29.873

Try this. It will handle simple CSV, and single- or double-quoted CSV, all via the regex pattern in the code below. You'll have to adjust the end of processCSV() to do what you want, since I'm just returning the object into thin air.

$(document).ready(function() {
    $.ajax({
        type: "GET",
        url: "my_csv.txt",
        dataType: "text",
        success: function(data) {processCSV(data);}
     });
 });

function processCSV(allLines) {
    var allLinesArray = allLines.split(/\r\n|\n/);
    var leaguetable = { 'building': [], 'percent': [], 'change': [] };
    var pattern = /([^,'"]*"[^"]*"[^,'"]*)|([^,'"]*'[^']*'[^,'"]*)|([^,"']*)/ig;
    var fieldValues;

    for (var i=0; i<allLinesArray.length; i++) {
        fieldValues = allLinesArray[i].match(pattern);
        if (fieldValues) {
            for (var j=0; j<fieldValues.length; j++) {
                // if begins with single- or double-quote, strip specified quotes
                if (fieldValues[j].charAt(0) === '"' || fieldValues[j].charAt(0) === "'") {
                    fieldValues[j] = fieldValues[j].replace(fieldValues[j].substr(0,1), "");
                }
            }
            // I'll trust your CSV to have the right number of fields, but...
            // you may want to build some validation in before doing the next 3 lines
            leaguetable.building.push(fieldValues[1]);
            leaguetable.percent.push(fieldValues[2]);
            leaguetable.change.push(fieldValues[3]);
        }
    }
    return leaguetable;
}

Because the CSV supplied (it's for a week-long public display) needed so much editing (no quotes, titles as values, surplus fields, etc.), I ended up manually putting the values into two arrays. When we get around to updating the project, this will be useful. FYI here's the project: http://gogreenweek.dmu.ac.uk — Dave Everitt, Sep 21 '11 at 11:32
@Jonathan M will this parser work for this CSV file http://dl.dropbox.com/u/19509627/SampleDataClean.csv — bouncingHippo, Jan 23 '13 at 15:53
@bouncingHippo, you'll need to modify the code, since the variable `leaguetable` was designed specifically for this question. — Jonathan M, Jan 24 '13 at 15:45
@JonathanM do you mind taking a look at my CSV parsing question please http://stackoverflow.com/questions/14442398/uploading-and-parsing-a-csv-file-using-javascript — bouncingHippo, Jan 24 '13 at 17:12

score 0 · Answer 2 · answered Sep 16 '11 at 01:21

0

Assuming your CSV is simple, you'll probably want to do something like this:

Split the data into lines.
Iterate over the lines:
1. Split the line on a comma.
2. Append the first part to the building array.
3. Append the second part to the percent array, after parsing it with parseInt.
4. Do the same for the third part and the change array.

answered Sep 16 '11 at 01:21

icktoofay

126,289
21
250
231

This won't work if it's a quoted CSV, containing data such as "I, Claudius" as a book title, for example. But that's to your point in your previous request for a sample of the CSV. – Jonathan M Sep 16 '11 at 01:24
@Jonathan: And that's why I qualified my answer with "assuming the CSV is simple". – icktoofay Sep 16 '11 at 01:24

Best method of getting Javascript data from a simple 3-row CSV

2 Answers2