zemirco/json2csv

Convert json to csv with column titles

⚠️ WARNING: THIS PACKAGE IS ABANDONED

The code has moved to a new home.

This repository stays as a the historic home of json2csv up until v5. From v6, the library has been broken into smaller libraries that are now published to NPM independently:

• Plainjs: Includes the Parser API and a new StreamParser API which doesn't the conversion in a streaming fashion in pure js.
• Node: Includes the Node Transform and Node Async Parser APIs for Node users.
• WHATWG: Includes the WHATWG Transform Stream and WHATWG Async Parser APIs for users of WHATWG streams (browser, Node or Deno).
• CLI: Includes the CLI interface.
• Transforms: Includes the built-in transforms for json2csv.
• Formatters: Includes the built-in formatters for json2csv. Formatters are the new way to format data before adding it to the resulting CSV.

Up-to-date documentation of the library can be found at https://juanjodiaz.github.io/json2csv

json2csv's Issues

Select delimiter

Hi,
It's possible to use ";" instead of ","?

Remove quotes

Is there a way to remove the quotes mark of the output?

e.g.:

Instead of

car, color
"Audi", "blue"
"BMW", "black"
"Porsche", "green"

Do

car, color
Audi, blue
BMW, black
Porsche, green

Map reduce function for custom fields

We are getting information from database(MongoDB) and passes to json2csv. Some field in our database uses number(1, 2, 3....) to represent states like ( Active, Inactive, closed). It could be very nice if we have some map reduce function on certain fields.

Instead of writing number to CSV file, you have to write a value returned by that function.

Default values

It would be nice if also "null" or "[]" values could be set up to default value. Not only "undefined" values.

Looking for maintainers!

Hey everybody,

you might have guessed it from the time that has passed since my latest update on this repo. At the moment I don't have the time (or other things on my list that have higher priorities) to maintain json2csv.

I started this project during my master thesis. Since then I never needed the module again but tried to keep it up-to-date. That didn't really work out in the last months and I'm therefore looking for people who would like to take over the project.

I will give you access to the repository here at GitHub and rights to publish directly to npm. So you work completely on your own and rewrite stuff or implement new stuff whenever you want. I won't be Big Brother watching your work and deciding whether it will be added or not.

Just leave a comment below to indicate your interest!

Best regards,
Mirco

Add quotes to cli

Changing the default quote value from " to something else is supported, but not currently in the CLI.

Handle Nested Objects

[
  {
    "expand":"renderedFields,names,schema,transitions,operations,editmeta,changelog",
    "id":"241317",
    "self":"http://jira.xxx.com/rest/api/2/issue/241317",
    "key":"SERV-12730",
    "fields":{"summary":"Edge indicator sometimes not showing green on some clients."}
  }
]

Ideally you'd want to be able to support the following usage of json2csv:

json2csv -i stuff.json -f id,key,fields.summary

i.e. be able to request values from nested objects

Accept absolute paths

json2csv --input /tmp/shortenTokenEmails.json 
module.js:338
    throw err;
          ^
Error: Cannot find module '/home/starfall/workspace/foreach/tmp/shortenTokenEmails.json'

Would be great if absolute paths were supported. Good job otherwise!

CLI: "json2csv: command not found"

I created a folder: /json_csv

In this folder I ran the npm command and got the right output with no errors.

I now place my json file (called file.json) into the /json_csv folder and run the command:

json2csv -i file.json -p

The output I get is:

json2csv: command not found

Anybody know why it isn't working? Am I testing the CLI in the wrong folder?

I've tried going a folder or 2 deeper into the package, but it still doesn't work.

Help will be appreciated.

System is: Ubuntu 12.04 LTS

Second test fails

Is there any way to recover json with fields from csv ?

Is it possible to create a CSV file that contains all the attributes?

Like, instead of listing the fields, by putting an asterisk or something like that.
For example:

json2csv({data: someJSONData, fields: [*]}, function(err, csv) {
  if (err) console.log(err);
  console.log(csv);
});

Autodetect [{Object}] vs {Object}

The output of some REST APIs is of the form {Object}. json2csv expects [{Object}].

It'd be an added convenience if json2csv automatically handled both forms.

Regression 2.8.1

Latest version turning numeric value 0 to nothing.
doesn't recreate at version 2.4.1

fields

Is it possible to just use all fields present? Or at least all fields present in the first object entry?

CLI Tests

Currently there are no tests for the CLI part. Also need to make the loading of the fixtures a little less repetitive. Maybe also moved to own module.

Referencing .json files

This question undoubtedly has a simple answer, but do you have to include the actual JSON array or is it possible to reference a separate file including the array?

is it possible to insert sum of one of the column in the last line?

for example, I have fields like name address amount of various users. And at the end of the file I want it show the total amount

Add promise support

Might be worthwhile, but users can always do

var json2csv = Bluebird.promisify(require('json2csv'))

each line is a json string , does it support ?

I have a big file , each line is a json string , and I want to convert it to csv file , can this util support ?

mark todo done

todo

and add link to my newest project json2csv-stream

feature request: consolidate fields+fieldName options into a transform Object[]

For example:

{
  fields: [
    // Support label -> simple path
    {
      label: 'some label',
      value: 'path.to.something'
    },
    // Support label -> derived value
    {
      label: 'some label', // Supporting duplicate labels
      value: function(row) {
        return row.path1 + row.path2;
      }
    },
    // Support pathname -> pathvalue (label optional, keeps backwards compatibility)
    'simplepath'
    'path.to.value'
  ]
}

Backslashes not handled properly

There are two bugs related to the handling of backslashes:

1. When given strings with backslashes in them, json2csv produces 4x the number of backslashes as is appropriate. Some background and examples:

   $ node
   > s = 'hello\\world'
   'hello\\world'
   > console.log(s)
   hello\world
   > JSON.stringify(s)
   '"hello\\\\world"'
   > console.log(JSON.stringify(s))
   "hello\\world"
   
   $ json2csv
    [{"msg":"hello\\\\world"}]
    "msg"
    "hello\\\\world"
   

2. When a string ends with a backslash, the resulting csv cannot be parsed.

   $ json2csv
    [{"msg":"hello\\\\"}]
    "msg"
    "hello\\\""
   

The expected results would be:

"msg"
"hello\world"

and

"msg"
"hello\"

Doesn't handle quotation marks ("") correctly

The CSV format (as implemented by Excel and Google Docs) uses double quotation marks to escape single quotation marks, i.e. a "special" string becomes "a ""special"" string". This module is using JSON.stringify which escapes quotation marks with \", which breaks lines with quotation marks.
The fix is simple, I'll have a pull request in a moment.

Allow custom double quotes

It would be nice if you could define what will be used to replace escaped double quotes.

I mean replace this line with something like this:
return portion.replace(/\\"/g, params.doubleQuotes);

Newlines are not created correctly

Instead of a newline in a field, I just get the string \n .

When I manually create the CSV string myself, it works great.

Possible to get an empty cell instead of the text "undefined"?

If I pass in the following data for header:

[ 'Survey Participant',
  'Date Taken',
  'Q1 - Multi Choice']

and the following for data:

[ { 'Survey Participant': '[email protected]',
    'Date Taken': Mon Aug 25 2014 13:39:41 GMT-0700 (PDT),
    'Q1 - Multi Choice': 'Choice 2'},
  { 'Survey Participant': '[email protected]',
    'Date Taken': Tue Aug 26 2014 08:25:54 GMT-0700 (PDT) }]

It generates a CSV like the following:

"Survey Participant","Date Taken","Q1 - Multi Choice"
"[email protected]","2014-08-25T20:39:41.845Z","Choice 2"
"[email protected]","2014-08-26T15:25:54.272Z",undefined

Notice the last cell of the last row contains undefined since there wasn't a value provided for that record. Is there any way to get the output to replace undefined with an blank cell using the built in functionality?

params.data = JSON.parse(JSON.stringify(params.data));

line 48 in json2csv.js

  params.data = JSON.parse(JSON.stringify(params.data));

Seems terribly inefficient to me, especially with large datasets.

provide the field list with a default value

by @selevanm

Is there a way to provide the field list with a default value if the field doesn't exist in all cases in the JSON? I wrote a flatten function to bubble nested objects up to the surface of my JSON, then add the index of the array of the object as part of the keyname. In some cases I will have more than one object in the array, so instead of having json2csv report and error if I provide a field name that doesnt exist, I would like to have it default to null or empty string in data is not provided to it.

So my field list may look like this:

'profile_children_0_name',
'profile_children_0_age',
'profile_children_0_gender',
'profile_children_0_accountExpDate',
'profile_children_0_subscription_startDate',
'profile_children_1_age',
'profile_children_1_gender',
'profile_children_1_accountExpDate',
'profile_children_1_subscription_startDate'

And my JSON for the profile object:

profile: {
  children:[
    {
      "name":"somename",
      "age": 4,
    },
    {
      "name":"somename2",
      "age":5,
    }
  ]
}

So sometimes I have 1 object in the child array, sometimes more. My field list above in this case works, but if I wanted to allow for more children, ie profile_children_2_ I will get an error on those JSON docs that do not contain three children. In that case I would like the profile_children_2_ to just be null or an empty string. Hope this makes sense.

Incorrect escaping

For following json:

[
{
"item_id": "55370784",
"resolved_id": "0",
"given_url": "http://www.michaeljohngrist.com/ruins-gallery/%5C",
"given_title": "Nothing found for Ruins-gallery ",
"favorite": "0",
"status": "1",
"time_added": "1289675792",
"time_updated": "1295238149",
"time_read": "1295238149",
"time_favorited": "0",
"sort_id": 1536
}
]

The output csv is messed up thanks to the "" in given_title. It should be escaped correctly.

Add bower support

Hi,

Please, could you add bower support ?
(http://bower.io/docs/creating-packages/#register)

Thank you :)

Where is the cli?

I'm sorry for this silly question. I'm a newbee in Node/npm world.

I just installed json2csv as follows:

$ npm install json2csv
npm http GET https://registry.npmjs.org/json2csv
npm http 304 https://registry.npmjs.org/json2csv
[email protected] ./node_modules/json2csv

But I can't fire json2csv on command line:

$ json2csv
-bash: json2csv: command not found

Where can I find this command and how can I enable this??

Version Code is malformated

You should declare the version in your package.json according to http://semver.org/ .
This would mean: 1.0.1 instead of *v*1.0.1

Invalid String Length Error

I have been using json2csv for big data records & apparently it seems it can't handle large datasets.

Here's the error I get:

error: uncaughtException: Invalid string length date=Tue Nov 24 2015 21:58:18 GMT+0000 (UTC), pid=818, uid=0, gid=0, cwd=/scripts/latest_activity_engagement_report, execPath=/root/.nvm/versions/node/v4.2.2/bin/node, version=v4.2.2, argv=[/root/.nvm/versions/node/v4.2.2/bin/node, /scripts/latest_activity_engagement_report/index.js], rss=1325998080, heapTotal=1287887104, heapUsed=1265062400, loadavg=[1.00341796875, 0.7333984375, 0.43896484375], uptime=20235596, trace=[column=11, file=/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js, function=null, line=189, method=null, native=false, column=null, file=null, function=Array.forEach, line=null, method=forEach, native=true, column=15, file=/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js, function=createColumnContent, line=144, method=null, native=false, column=15, file=/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js, function=null, line=28, method=null, native=false, column=3, file=/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js, function=checkParams, line=86, method=null, native=false, column=3, file=/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js, function=module.exports, line=22, method=exports, native=false, column=3, file=/scripts/latest_activity_engagement_report/index.js, function=generateCSV, line=307, method=null, native=false, column=13, file=/scripts/latest_activity_engagement_report/index.js, function=null, line=172, method=null, native=false, column=13, file=/scripts/latest_activity_engagement_report/dbHelper.js, function=null, line=51, method=null, native=false, column=12, file=/scripts/latest_activity_engagement_report/node_modules/mongodb/lib/utils.js, function=handleCallback, line=96, method=null, native=false], stack=[RangeError: Invalid string length,     at /scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js:189:11,     at Array.forEach (native),     at createColumnContent (/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js:144:15),     at /scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js:28:15,     at checkParams (/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js:86:3),     at module.exports (/scripts/latest_activity_engagement_report/node_modules/json2csv/lib/json2csv.js:22:3),     at generateCSV (/scripts/latest_activity_engagement_report/index.js:307:3),     at /scripts/latest_activity_engagement_report/index.js:172:13,     at /scripts/latest_activity_engagement_report/dbHelper.js:51:13,     at handleCallback (/scripts/latest_activity_engagement_report/node_modules/mongodb/lib/utils.js:96:12)]

I am using the latest version i.e. 3.0.1. Any idea what might be causing this?

Data without header information

Hi, How can I only print out the data without the header information.?

Thanks

comment lib file

put some comments into bin/json2csv

how write csv file with support utf-8 when open with excel

support for no value in the field

I like to generate the csv with some fields with no value like

   name, age, gender, nationality
   'John',,,'American'

In above CSV, i dont have values for age, and gender. With current json2csv, i can only put double quotes for empty value. But i dont want to put even the double quote. It should be plain empty.

return error in callback

use node style and pass error object as first parameter of callback function instead of throwing it.

json2csv({data: json, fields: ['car', 'color']}, function(err, csv) {
  if (err) console.log(err);
  console.log(csv);
});

New version is not on NPM

The new version with the new field properties doesn't seem to be the same one that's on NPM

-p fails if data contains ,

Example:

echo '[{"title":"a,b"}]' | json2csv -p -f title

Command line only accepts files with .json extension

Because of how you open the file (using require) the command line can't read the file unless it has a json extension. The command json2csv -i log -f sync,timebase -p will not work.

Line is the culprit
input = require(path.join(process.cwd(), program.input));

FYI I am using node v0.8.20. This behavior might be different in newer node versions.

is it possible to create file with only data, no fields?

I want to create a csv file with only the data and want to omit the headers. is it possible?

Coveralls

@zemirco could you add this repo project to https://coveralls.io/repos/new? Thanks!

Option for null to ""

The value 'null' seems to be irrelevant in CSV. It will be nicer if you can automatically convert to empty string "" in CSV file.

It could be even better and generic if it is customizable to any string or any number.

Escaping still doesn't work

json2csv({data: [
  {
    a:'asdf"asdf'
  }, 
  {
    a: 'asdf'
  }
]}, function(err, x) {
  console.log(x)
})

"a"
"asdf""asdf" // this should be "asdf"""asdf"
"asdf"

according to http://tools.ietf.org/html/rfc4180,

escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE

Support for nested values in json

var t = {
  one: {
    child: 'hello'
  }
}

Current implementation does not support nested fields:

['one.child']

Write absolute path tests

Write test for absolute path using path.resolve(..).

Error: Cannot find module 'os'

phantomjs://bootstrap.js:289
phantomjs://bootstrap.js:254 in require
/var/tmp/node_modules/json2csv/lib/json2csv.js:4
/var/tmp/node_modules/json2csv/lib/json2csv.js:203
TypeError: '[object Object]' is not a function (evaluating 'json2csv')

var json2csv = require('json2csv');
var fs = require('fs');

var jsonData = require('_User.json');

json2csv({ data: jsonData }, function(err, csv) {
if (err) throw err;
fs.write('_User.csv', csv, function(err) {
if(err) throw err;
console.log("_User.csv file has been saved.");
});
});

example 3 in readme.md wrong

add "price" to fields paramter