convert csv file to json object datatable

Question

Does anyone know how to get a csv url file and convert it to a json object so that I can use google charting tools in js?

Are you trying to parse the CSV in JS or another language and just have that output JSON for JS? — Angelo R., Aug 30 '10 at 18:48
I was able to parse the csv files into two arrays in C# on the backend. Now I would like to create JSON object (that I can use on the front end using those two arrays' data) to bind to a google js api chart. — locoboy, Aug 30 '10 at 19:39

score 19 · Answer 1 · answered Aug 09 '11 at 05:35

19

I realise this is an old question, but I came across it today needing to do the same thing and wrote a script to do it. You can check it out at my github repo.

The following code would accomplish what you're after (using jQuery):

$.ajax("http://my.domain.com/mycsvfile.csv", {
    success: function(data) {
        var jsonobject = csvjson.csv2json(data);
        // Now use jsonobject to do some charting...
    },
    error: function() {
        // Show some error message, couldn't get the CSV file
    }
});

Happy coding :)

answered Aug 09 '11 at 05:35

aaronsnoswell

6,051
5
47
69

1

I've just used your file to get a quick prototype of something up. ALl looked good until I found that \r characters were finding their way into a Mongo (I hadn't noticed before then). A few points in the code required myString.replace(/\r/gm,"") to clean it up. – joevallender Jun 13 '12 at 15:50
@joevallender Thanks for pointing that out. Any chance you can still remember what you had to do and can create an issue for it? https://github.com/aaronsnoswell/csvjson.js/issues/new. – aaronsnoswell Jun 14 '12 at 10:09
I'd have submitted a pull request if I wasn't doing on it on the fly, I'll drop some notes into the issue queue now – joevallender Jun 14 '12 at 13:57

Stéphane Laurent · Answer 2 · 2017-10-13T08:14:27.273

Papa Parse is nice for that.

<html>
<head>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
    <script src="libraries/PapaParse-4.1.2/papaparse.min.js"></script>

    <script>
    $(document).ready(function(){

        $("#submitbutton").click(function(){
            var myfile = $("#csvfile")[0].files[0];
            var json = Papa.parse(myfile, 
                {
                header: true, 
                skipEmptyLines: true,
                complete: function(results) {
                    console.log("Dataframe:", JSON.stringify(results.data));
                    console.log("Column names:", results.meta.fields);
                    console.log("Errors:", results.errors);
                }
            });

        })
    })
    </script>

</head>

<body>
    <form name="foo" method="post" enctype="multipart/form-data">
        <input id="csvfile" type="file" value="i">
    </form>

    <button id="submitbutton" type="button">Upload CSV file!</button>

</body>

</html>

Uploading this CSV:

+------+----------------+---------------+------------+
|  Id  |  Petal.Length  |  Petal.Width  |  Species   |
+======+================+===============+============+
|  1   |      1.4       |      0.2      |   setosa   |
+------+----------------+---------------+------------+
|  2   |      1.4       |      0.2      |   setosa   |
+------+----------------+---------------+------------+
|  3   |      1.3       |      0.2      |   setosa   |
+------+----------------+---------------+------------+
|  4   |      3.9       |      1.4      | versicolor |
+------+----------------+---------------+------------+
|  5   |      3.5       |       1       | versicolor |
+------+----------------+---------------+------------+
|  6   |      4.2       |      1.5      | versicolor |
+------+----------------+---------------+------------+
|  7   |      5.4       |      2.3      | virginica  |
+------+----------------+---------------+------------+
|  8   |      5.1       |      1.8      | virginica  |
+------+----------------+---------------+------------+

you'll get this output in the console:

Dataframe: [{"Id":"1","Petal.Length":"1.4","Petal.Width":"0.2","Species":"setosa"},{"Id":"2","Petal.Length":"1.4","Petal.Width":"0.2","Species":"setosa"},{"Id":"3","Petal.Length":"1.3","Petal.Width":"0.2","Species":"setosa"},{"Id":"4","Petal.Length":"3.9","Petal.Width":"1.4","Species":"versicolor"},{"Id":"5","Petal.Length":"3.5","Petal.Width":"1","Species":"versicolor"},{"Id":"6","Petal.Length":"4.2","Petal.Width":"1.5","Species":"versicolor"},{"Id":"7","Petal.Length":"5.4","Petal.Width":"2.3","Species":"virginica"},{"Id":"8","Petal.Length":"5.1","Petal.Width":"1.8","Species":"virginica"}]
Column names: ["Id", "Petal.Length", "Petal.Width", "Species"]
Errors: []

score 4 · Answer 3 · answered Oct 13 '15 at 11:32

use this code for guide to parse csv file to json ...

function processFiles(files) {
    var file = files[0];
    var reader = new FileReader();
    reader.onload = function (e) {
        var output = document.getElementById("fileOutput");
        var texto = e.target.result;
        csvJSON(texto);
    };
    reader.readAsText(file);
}
function csvJSON(csv) {
    var lines = csv.split("\n");
    var result = [];
    var headers;
    for (var i = 0; i < lines.length; i++) {
        headers = lines[i].split("\n");
    }
    var cont = 0;
    for (var i = 0; i < lines.length; i++) {

        var obj = {};
        var currentline = lines[i].split("\n");
        for (var j = 0; j < headers.length; j++) {
            obj[cont] = currentline[j];
        }
        cont++;
        result.push(obj);
    }

    return JSON.stringify(result); //JSON
}

score 3 · Accepted Answer · edited May 23 '17 at 10:33

As far as I know, for most scenarios, you can turn csv into a js Array of Arrays, a Matrix, or any javascript object that follows your charting tool convention.

You may need to:

Get the CSV file content
Parse it
Wrap the results from 2 into your charting tool json(?)
Call your charting library

For 1, if the CSV file is hosted in your domain, you can do a simple XMLHttpRequest, else try searching here "same origin policy".
The tricky part is the point 2. I've seen several failed attempts for parsing CSV files by hand (semicolons can be contained as part of the value, etc)... Check out the link.

score 1 · Answer 5 · answered Aug 30 '10 at 18:48

CSV and JSON are different formats. JSON is hierarchical while CSV represents a list of values. So I guess you will need to first parse the CSV (using a parser of course and not implementing yourself). This parser will give you an object which you could then serialize into JSON or probably convert into another object before serializing (once again using a parser) in order to get the desired format.

convert csv file to json object datatable

5 Answers5

Linked