how to parse csv from github?

Question

jQuery.ajax({
    url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
    type: 'get',
    dataType: 'json',
    success: function(data) {
        console.log(data);
    },
    error: function(jqXHR, textStatus, errorThrow){
        alert("Error: " + jqXHR['responseText']);
    }
});

I need the output as formatted json but It's going to error, basically trying to parse this

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv

UPDATE

They've changed the link

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

Look at the source code from : https://lucidar.me/en/covid-19/global-prediction/ — Fifi, Mar 25 '20 at 10:30
@Fifi thanks but ended with my own cor using php in order not to ping git everytime — rob.m, Mar 26 '20 at 09:06
OK, great. I realized suddenly you expected JS, not Python ! @skomisa The link should work now. — Fifi, Mar 26 '20 at 09:46
So, Have a look at this link, it may be helpful for Ajax queries : https://lucidar.me/en/javascript-modules/ajax/ — Fifi, Mar 26 '20 at 09:46
@Fifi thank you. I did resolve it and working. Only thing is that I am getting wrong numbers compared to that git repo, I tried a lot of things, there is a mix match of null and empty strings which makes it not perfect on their csvs — rob.m, Mar 26 '20 at 09:50
Yes, you have to check the type of the content. Something like `province = '-' if (type(row[0]) != str) else row[0]` in Python. — Fifi, Mar 26 '20 at 09:53
@Fifi geez, they keep change their format, now had to ask this question, mind answering if you could? https://stackoverflow.com/questions/60865083/how-to-fix-dates-to-be-similar-format — rob.m, Mar 26 '20 at 10:24

blex · Answer 1 · 2020-09-17T13:53:29.257

Parsing CSV is not always as simple as doing "...".split(','). And the file here is a perfect example of that. Some fields contain a ,, and thus are wrapped in quotes.

I suggest using Papa Parse which will handle that for you. I've used it many times before, it saved me a lot of headaches!

$.ajax({
    url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/6eae5b65a32b679efacf95a2867648330f83a871/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
    success: function(csv) {
        const output = Papa.parse(csv, {
          header: true, // Convert rows to Objects using headers as properties
        });
        if (output.data) {
          console.log(output.data);
        } else {
          console.log(output.errors);
        }
    },
    error: function(jqXHR, textStatus, errorThrow){
        console.log(textStatus);
    }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

thanks a lot mate! This tho takes me to this question as that json is not perfectly formatted https://stackoverflow.com/questions/60583604/how-to-split-and-create-single-objects-in-json — rob.m, Mar 08 '20 at 00:56

score 4 · Accepted Answer · answered Mar 08 '20 at 00:16

4

You have to use dataType as text and then run split with double for loop to get the data as an array of JS objects:

jQuery.ajax({
    url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
    type: 'get',
    dataType: 'text',
    success: function(data) {
        let lines = data.split('\n');
        let fields = lines[0].split(',');
        
        let output = [];
        
        for(let i = 1; i < lines.length; i++){
           let current = lines[i].split(',');
           let doc = {};
           for(let j = 0; j < fields.length; j++){
               doc[fields[j]] = current[j];
           }
           output.push(doc);
        }       
        
        console.log(output);
    },
    error: function(jqXHR, textStatus, errorThrow){
        console.log(textStatus);
    }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

answered Mar 08 '20 at 00:16

mickl

48,568
9
60
89

thanks a lot! I was just working on datatype text and was trying to figure out how to convert it to json. Thanks a lot. Just to ask, I need to grab these 3 csv from here but I thought I could just grab the raw for each of them, is this the best way? https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_time_series – rob.m Mar 08 '20 at 00:18
@rob.m I think so, maybe there's some jQuery extension which automatically does the conversion but since it's just a few lines of code I would suggest getting the data as raw – mickl Mar 08 '20 at 00:21
1

Note: parsing CSV is not always as simple as doing `"...".split(',')`. And the file here is a [perfect example of that](https://imgur.com/a/TgPoi8h). Some fields contain a `,`, and thus are wrapped in quotes. I suggest using [Papaparse](https://www.papaparse.com/) which will handle that for you. I've used it many times before, it saved me a lot of headaches! – blex Mar 08 '20 at 00:23
1

@blex would you be able to put up an answer using Papaparse for me and any future users? Your point is correct so an example would be helpful. Maybe using the github one as per the question thanks – rob.m Mar 08 '20 at 00:46
@mickl would you be able to create a json where the dates have a label like dat: 1/2/202 and the number next to it too like people: 1? Just to better organize the json as each of them is a value obj actually – rob.m Mar 08 '20 at 00:48

how to parse csv from github?

2 Answers2

Linked