Javascript loading CSV file into an array

Question

I am developing a web page in Wordpress. The webpage needs to have a combobox with all counties. I have a dataset in csv format which has some 10k rows for all these counties. When the user selects a county in the dropdown, I want only the selected county data displayed in the web page. This is my requirement.

In wordpress, my web page I am adding the js file using

<script type="text/javascript" src="http://xxx/wp     content/uploads/2014/05/countyList1.js"></script>

and the code for webpage dropdown is

<select name="county" id="county" onload="setCounties();" onchange="getSelectedCountyData();"></select>

In countyList1.js file I have the setCounties() and getSelectedCountyData() functions.

So far I can see the dropdown with counties list. I don't know how to read the CSV file and apply the selected county filter to this list.

I tried the FileReader object and I can load the CSV contents on the web page but I don't want the user to select the CSV. I have the dataset already.

I am trying to use this jquery.csv-0.71 library from this SO post How to read data From *.CSV file using javascript? but I need help.

Here's the code which gets called when a county is selected

function getSelectedCountyData() {
        cntrySel = document.getElementById('county');
        //selCty = countyList[cntrySel.value];
        handleFiles();
}

function handleFiles() {

    $(document).ready(function () {
        $.ajax({
            type: "GET",
            url: "D:\Docs\Desktop\csvfile.csv",
            dataType: "csv",
            success: function (data) { processData(data); }
        });
    });
}

function processData(allText) {
    var allTextLines = allText.split(/\r\n|\n/);
    var headers = allTextLines[0].split(',');
    var lines = [];

    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);
        }
    }
    console.log(lines);
    drawOutput(lines);
}

function drawOutput(lines) {
    //Clear previous data
    document.getElementById("output").innerHTML = "";
    var table = document.createElement("table");
    for (var i = 0; i < lines.length; i++) {
        var row = table.insertRow(-1);
        for (var j = 0; j < lines[i].length; j++) {
            var firstNameCell = row.insertCell(-1);
            firstNameCell.appendChild(document.createTextNode(lines[i][j]));
        }
    }
    document.getElementById("output").appendChild(table);
}

Solution 1

I highly recommend looking into this plugin:

http://github.com/evanplaice/jquery-csv/

I used this for a project handling large CSV files and it handles parsing a CSV into an array quite well. You can use this to call a local file that you specify in your code, also, so you are not dependent on a file upload.

Once you include the plugin above, you can essentially parse the CSV using the following:

$.ajax({
    url: "pathto/filename.csv",
    async: false,
    success: function (csvd) {
        data = $.csv.toArrays(csvd);
    },
    dataType: "text",
    complete: function () {
        // call a function on complete 
    }
});

Everything will then live in the array data for you to manipulate as you need. I can provide further examples for handling the array data if you need.

There are a lot of great examples available on the plugin page to do a variety of things, too.

Solution 2

You can't use AJAX to fetch files from the user machine. This is absolutely the wrong way to go about it.

Use the FileReader API:

<input type="file" id="file input">

js:

console.log(document.getElementById("file input").files); // list of File objects

var file = document.getElementById("file input").files[0];
var reader = new FileReader();
content = reader.readAsText(file);
console.log(content);

Then parse content as CSV. Keep in mind that your parser currently does not deal with escaped values in CSV like: value1,value2,"value 3","value ""4"""

Solution 3

If your not overly worried about the size of the file then it may be easier for you to store the data as a JS object in another file and import it in your . Either synchronously or asynchronously using the syntax <script src="countries.js" async></script>. Saves on you needing to import the file and parse it.

However, i can see why you wouldnt want to rewrite 10000 entries so here's a basic object orientated csv parser i wrote.

function requestCSV(f,c){return new CSVAJAX(f,c);};
function CSVAJAX(filepath,callback)
{
    this.request = new XMLHttpRequest();
    this.request.timeout = 10000;
    this.request.open("GET", filepath, true);
    this.request.parent = this;
    this.callback = callback;
    this.request.onload = function() 
    {
        var d = this.response.split('\n'); /*1st separator*/
        var i = d.length;
        while(i--)
        {
            if(d[i] !== "")
                d[i] = d[i].split(','); /*2nd separator*/
            else
                d.splice(i,1);
        }
        this.parent.response = d;
        if(typeof this.parent.callback !== "undefined")
            this.parent.callback(d);
    };
    this.request.send();
};

Which can be used like this;

var foo = requestCSV("csvfile.csv",drawlines(lines)); 

The first parameter is the file, relative to the position of your html file in this case. The second parameter is an optional callback function the runs when the file has been completely loaded.

If your file has non-separating commmas then it wont get on with this, as it just creates 2d arrays by chopping at returns and commas. You might want to look into regexp if you need that functionality.

//THIS works 

"1234","ABCD" \n
"!@£$" \n

//Gives you 
[
 [
  1234,
  'ABCD'
 ],
 [
  '!@£$'
 ]
]

//This DOESN'T!

"12,34","AB,CD" \n
"!@,£$" \n

//Gives you

[
 [
  '"12',
  '34"',
  '"AB',
  'CD'
 ]
 [
  '"!@',
  '£$'
 ]
]

If your not used to the OO methods; they create a new object (like a number, string, array) with their own local functions and variables via a 'constructor' function. Very handy in certain situations. This function could be used to load 10 different files with different callbacks all at the same time(depending on your level of csv love! )

Solution 4

This is what I used to use a csv file into an array. Couldn't get the above answers to work, but this worked for me.

$(document).ready(function() {
   "use strict";
    $.ajax({
        type: "GET",
        url: "../files/icd10List.csv",
        dataType: "text",
        success: function(data) {processData(data);}
     });
});

function processData(icd10Codes) {
    "use strict";
    var input = $.csv.toArrays(icd10Codes);
    $("#test").append(input);
}

Used the jQuery-CSV Plug-in linked above.

View more solutions

Comments

[Aqua267]

But the user is not uploading the csv file. I have the CSV file in wordpress and the url is sthg like xxx/wp-content/uploads/2014/05/csvFile.csv. How I get pass this file to the FileReader?

[Aqua267]

Just to clarify I am looking to do this in Javascript only. I am not familiar with jquery/ ajax.

[Halcyon]

If the file is already uploaded, and you have the file contents then you don't need the FileReader anymore. The FileReader is the wrapper you get for reading files on the user machine.

[Aqua267]

I have the file but the problem is I don't know how to read its contents. I am a novice, apologize if this sounds dumb.

[Aqua267]

Thanks for the link. I am trying something like the one below however data is undefined. I have not used ajax or jquery before so your help is very much appreciated. I know I am missing something obvious but can't figure out: $.ajax({ url: "dl.dropboxusercontent.com/u/25575808/energy/nodes.csv", aync: false, success: function (csvd) { data = $.csv2Array(csvd); }, dataType: "text", complete: function () { // call a function on complete } });

[Aqua267]

Got it..it was a typo async. Thanks again.

[Adam P]

I am tryingthis solution but get "XMLHttpRequest cannot load file: ///Users/adampaciorek/Desktop/names%20Parser/CSV_Database_of‌​_First_Names.csv. Cross origin requests are only supported for protocol schemes: http, data, chrome, chrome-extension, https, chrome-extension-resource". Does anyone know a workaround for this?

[princessjackie]

Where does your original script live on your system that's calling this file? It seems like an issue trying to dive into another area of your computer to grab the file, which may violate cross-origin policies and if you're trying to access a local file from the web, that won't work. You will need to have your CSV file on the same domain. Here's some info from Google about cross-origin with a link about same-origin policy to give more background: developer.chrome.com/extensions/xhr

[NiallMitch14]

Is it possible to use jquery-csv for input elements with csv files?

[Jack]

This did the trick perfectly. Thank you for adding your success.