Nested JSON to CSV format Node.js

Question

I am new to programming and am working on a project that received a nested JSON response from an API using node.js. I am hoping to find a simple method to convert this JSON into a CSV for export. I have searched around a bit have tried jsonexport and nestedcsv2json packages, but I have not been able to get my format quite right and am unable to iterate though my JSON response.

Basically the JSON response is as follows:

{ '2016-01-31': { chats: 0, missed_chats: 5 },
'2016-02-01': { chats: 60, missed_chats: 7 },
'2016-02-02': { chats: 56, missed_chats: 1 },
'2016-02-03': { chats: 46, missed_chats: 0 },
'2016-02-04': { chats: 63, missed_chats: 2 },
'2016-02-05': { chats: 59, missed_chats: 4 },
'2016-02-06': { chats: 0, missed_chats: 1 } }

The issue I am having is that the size of this can vary depending on the date range that the user enters. So I will need to iterate through the returned dates and then extract the nested JSON. It could be one date or 100 dates.

I am looking to make a CSV format with headers that I can export:

   Date         Chats    Missed Chats
2016-02-02        60            7

This should be quick and easy, but I am struggling to get this quite right. Any tips or help is greatly appreciated. Thanks!

BeNdErR · Accepted Answer · 2016-02-06 19:04:08Z

1

This outputs a semicolon separated csv-string. Edit the separator (for example replace it with tab \t) to suit your needs:

var json = { '2016-01-31': { chats: 0, missed_chats: 5 },
'2016-02-01': { chats: 60, missed_chats: 7 },
'2016-02-02': { chats: 56, missed_chats: 1 },
'2016-02-03': { chats: 46, missed_chats: 0 },
'2016-02-04': { chats: 63, missed_chats: 2 },
'2016-02-05': { chats: 59, missed_chats: 4 },
'2016-02-06': { chats: 0, missed_chats: 1 } };

var headers = 'Date;Chats;Missed chats';

var csv = headers + '\r\n';
for(key in json){
    csv += key + ';' + json[key].chats + ';' + json[key].missed_chats + '\r\n'
}
console.log(csv)

The output is:

Date;Chats;Missed chats
2016-01-31;0;5
2016-02-01;60;7
2016-02-02;56;1
2016-02-03;46;0
2016-02-04;63;2
2016-02-05;59;4
2016-02-06;0;1

You can find a working example here: https://jsfiddle.net/owd8zsa0/

answered Feb 6, 2016 at 19:04

BeNdErR

18k21 gold badges79 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

richardson_tm Over a year ago

Thank you! That is perfect for my needs. Simple and effective.

Aminadav Glickshtein · Accepted Answer · 2016-02-06 19:07:33Z

This answer, will automatically get the headers (first line), and add it to the CSV.

var input={ '2016-01-31': { chats: 0, missed_chats: 5 },
'2016-02-01': { chats: 60, missed_chats: 7 },
'2016-02-02': { chats: 56, missed_chats: 1 },
'2016-02-03': { chats: 46, missed_chats: 0 },
'2016-02-04': { chats: 63, missed_chats: 2 },
'2016-02-05': { chats: 59, missed_chats: 4 },
'2016-02-06': { chats: 0, missed_chats: 1 } }

function json2csv(input){
    var result=''
    var firstLine=true
    for (var i in input){
        if(firstLine){
            firstLine=false
            result='id\t'
            for (var j in input[i]){
                result+=j +';'
            }
            result+='\n'
        }
        else{
            result+=i+'\t'
            for (var j in input[i]){
                result+=input[i][j] +';'
            }
            result+='\n'
        }

    }
    return result
}

console.log(json2csv(input));

It will Result:

id;chats;missed_chats;
2016-02-01;60;7;
2016-02-02;56;1;
2016-02-03;46;0;
2016-02-04;63;2;
2016-02-05;59;4;
2016-02-06;0;1;

Shanoor · Accepted Answer · 2016-02-06 19:50:01Z

Using lodash and the csv package:

var csv = require('csv');
var _ = require('lodash');

var obj = {
    '2016-01-31': { chats: 0, missed_chats: 5 },
    '2016-02-01': { chats: 60, missed_chats: 7 },
    '2016-02-02': { chats: 56, missed_chats: 1 },
    '2016-02-03': { chats: 46, missed_chats: 0 },
    '2016-02-04': { chats: 63, missed_chats: 2 },
    '2016-02-05': { chats: 59, missed_chats: 4 },
    '2016-02-06': { chats: 0, missed_chats: 1 }
};

// flatten obj to have an array of object like
// { date: '2016-01-31', chats: 0, missed_chats: 5 }
obj = _.map(obj, function (value, key) {
    return _.merge({ date: key }, value);
})

csv.stringify(obj, { header: true }, function(err, result) {
    console.log(result);
});

/* output:
date,chats,missed_chats
2016-01-31,0,5
2016-02-01,60,7
2016-02-02,56,1
2016-02-03,46,0
2016-02-04,63,2
2016-02-05,59,4
2016-02-06,0,1
*/

peak · Accepted Answer · 2016-02-07 05:01:17Z

The "JSON response" shown in the question is not technically valid JSON, so since this answer depends on a JSON-aware tool (jq), the first task is to convert the "JSON response" to valid JSON. This can be done (for example) using json5 or any-json.

Assuming the "JSON response" is in a file named response.txt, we can proceed as follows, where the initial $ signifies a bash-like shell:

$ any-json -format json5 < response.txt | jq -r '. as $in
  | keys[]
  | [.] + ($in[.] | [.chats, .missed_chats ]) | @csv'

This produces the following output:

"2016-01-31",0,5
"2016-02-01",60,7
"2016-02-02",56,1
"2016-02-03",46,0
"2016-02-04",63,2
"2016-02-05",59,4
"2016-02-06",0,1

This output is valid CSV (with comma as the separator character), but if you want TSV (with tab as separator) or if you don't want the double-quotes, simply replace @csv by @tsv to produce the following output:

2016-01-31  0   5
2016-02-01  60  7
2016-02-02  56  1
2016-02-03  46  0
2016-02-04  63  2
2016-02-05  59  4
2016-02-06  0   1

It's trivial in either case to add a row of headers (either using jq or otherwise), so you can choose how to handle that.

Note: If you use json5, you would proceed in two steps.

Collectives™ on Stack Overflow

Nested JSON to CSV format Node.js

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related