json-2-csv
This node module will convert an array of JSON documents to a CSV string. Column headings will be automatically generated based on the keys of the JSON documents. Nested documents will have a ‘.’ appended between the keys.
It is also capable of converting CSV of the same form back into the original array of JSON documents. The columns headings will be used as the JSON document keys. All lines must have the same exact number of CSV values.
Installation
Upgrading?
Upgrading to v3 from v2? Check out the upgrade guide.
Usage
Looking for examples? Check out the Wiki: json-2-csv Wiki
converter.json2csv(array, callback, options)
- array — An array of JSON documents to be converted to CSV.
- callback — A function of the form function (err, csv) ;
- This function will receive any errors and/or the string of CSV generated.
- checkSchemaDifferences — Boolean — Should all documents have the same schema?
- Default: false
- Note: An error will be thrown if some documents have differing schemas when this is set to true .
- field — String — Field Delimiter.
- Default: ,
- Default: «
- Default: \n
- Default: none
- Default: []
- Note: When used with unwindArrays , arrays present at excluded key paths will not be unwound.
- [‘specifications.features’, ‘specifications.mileage’]
- [‘specifications’]
- Default: These will be auto-detected from your data by default.
- Keys can either be specified as a String representing the key path that should be converted, or as an Object with the field property specifying the path. When specifying keys as an Object, you can also optionally specify a title which will be used for that column in the header. The list specified can contain a combination of Objects and Strings.
- [ ‘key1’, ‘key2’, . ]
- [ < field: 'key1', title: 'Key 1' >, < field: 'key2' >, ‘key3’, . ]
- Default: A built-in method is used to parse out a variety of different value types to well-known formats.
- Note: Using this option may override other options, including useDateIso8601Format and useLocaleFormat .
- Default: true
- Default: false
- Default: false
- Default: false
- Default: false
- Note: If selected, values will be converted using toISOString() rather than toString() or toLocaleString() depending on the other options provided.
- Default: false
- Note: If selected, values will be converted using toLocaleString() rather than toString()
- Default: false
- Default: false
Promisified Version: converter.json2csvAsync(array, options)
Available in version 2.2.0 , this functionality makes use of promises from the bluebird module.
converter.csv2json(csv, callback, options)
- csv — A string of CSV
- callback — A function of the form function (err, array) ; This function will receive any errors and/or the array of JSON documents generated.
- options — (Optional) A JSON document specifying any of the following key value pairs:
- delimiter — Document — Specifies the different types of delimiters
- field — String — Field Delimiter.
- Default: ,
- Default: «
- Default: \n
- Default: false
- Default: null
- If you have a nested object (ie.
> ), then set this to [‘info.name’] - If you want all keys to be converted, then specify null or don’t specify the option to utilize the default.
- Default: JSON.parse — An attempt is made to convert the String back to its original value using JSON.parse .
- Default: false
- Default: false
Promisified Version: csv2jsonAsync(csv, options)
Available in version 2.2.0 , this functionality makes use of promises from the bluebird module.
Note: As of 3.5.8 , the command line interface functionality has been pulled out to a separate package. Please be sure to install the @mrodrig/json-2-csv-cli NPM package if you wish to use the CLI functionality shown below:
JSON to CSV Converter
Subscribe to PRO for just $10 / month and convert up to 50 MB (and unlock some useful features).
Do something clever with this data
We are eager to produce amazing data solutions for you. If you have any ideas at all, let’s discuss them. A taste of what we can do for you:
- transform JSON / XML / scraped HTML into a report / HTML dashboard / chart / Google Spreadsheet (using powerful underlying SQL queries and easily modified templates)
- schedule data to be read from / written to a URL / Dropbox / Box / MySQL / SQL Server / Zapier periodically
- merge data from multiple sources together
- integrate multiple actions inside a powerful workflow
- what is your idea?
Just send through your requirements and we will assist you with a solution.
Send to Google Spreadsheet
Careful. This will send data outside of data.page
Send my data to a new Sheet in the Google Drive Account of: email change. save
Email address above must be a valid Google Account
Contact us if you would like the Sheet to be auto-updated daily / weekly.
How to convert JSON to CSV in Node.JS
This article is a continuation of How to convert CSV to JSON in Node.JS.
We will keep up with the same pattern used there — introducing two solutions to our problem.
The first solution will use the standard readFile method, i.e. reading the whole JSON file, when everything is read — transform the data, and write it in a new CSV file. This solution is not a good idea to be used for large files, because of the memory limits.
The second one is a little bit more advanced and is using streams. The advantage of this solution is that we are reading the file part-by-part, parse the JSON, transform the chunk to CSV and push the transformed data in writable stream. This way we can read a gigabytes of data without even bothering about memory limits.
We will be using the csvjson npm package to help us with the transformation from JSON to CSV and JSONStream for the streams solution as the csvjson does not still supports json to csv transformation using streams(csv to json is supported).
The read/write operations, will be handled by us using the native fs module.
Project preparation
Before continue with the implementation of both methods, we will do a quick preparation by create a new project using npm.
Navigate to the folder where you want to create your project, open the terminal and execute the following commands:
The init command is initializing the project and the -y argument is telling npm to use the default settings. It’s always better to configure it according your needs, but for our testing purposes this is not the case.
And the other two commands are installing the dependencies we will be using — csvjson and jsonstream.
When the execution is finished, our main project structure should look like this:
Last thing before start writing some code is to create a JSON file that will be used to test the solutions.
Create a file test-data.json and fill it with the following data:
This is a pretty simple, not nested example. However, the library for transformation we will be using is smart enough to handle and nested data. Read their documentation for more info if that is your case.
Convert JSON to CSV using readFile
Create a file with name read-file.js in your main project directory.
Include the following code to the file:
This is the final, working version. We will explore it line by line.
Importing the required dependencies in the beginning of your file/code is a common practice in Node.JS. In this example, we are importing the external library we will be using and readFile/writeFile methods, provided by the built-in fs module.
Then, we are reading the JSON file, using the readFile. The first argument provided to it is the path to our test file, the second is the encoding(important one, without specify the encoding, we will receive Buffer instead of string) and the third one — callback function. There, we expect to receive two parameters(error — if any and the actual file content).
I’m sure that you are aware how important is to catch the errors and handle them properly. This is not the aim of this article, but keep it in mind.
When there is no error and the content is now available, csvjson will help us with converting it to CSV by using the method toCSV.
Passing the json data as first argument and options object as second.
The option we are using is only one — headers: ‘key’. It’s telling the library to use the object keys as headers.
Without — headers: ‘key’:
With — headers: ‘key’:
The second variant looks a little bit better to me ��
And now, we can write the transformed data to a CSV file using the writeFile method. As like the readFile, the first argument is the file path(the file will be created automatically), the second is your data and the last one callback.
However, the callback is expecting only one parameter — error. If it’s null, then the operation is successful.
Execute this command in the terminal:
Check your directory. A new file test-data.csv will be created.
Open it using your IDE or your favorite CSV viewer. The whole data from your JSON file should be there ��
Any questions ? Feel free to ask in comments.
We will continue with the solution using streams.
Convert JSON to CSV using Streams
For this solution, we will use the same test file created in the previous step — test-data.json. You can always insert more data to it to taste the real power of streams.
Create a file read-stream.js and include the following code:
This is the complete solution for this example. As you can see it’s a little bit more complex than the previous one. One of the reasons for this is the fact that csvjson is supporting a complete streaming solution for CSV to JSON, but not for JSON to CSV.
Basically we are implement our own solution with the help of JSONStream package. You may be wondering why we do this. Because when a readable stream for a JSON file is created, the stream is sending the chunks without caring if it’s possible to parse the data, is it a complete object and so on.
It’s absolutely normal to receive a chunk like this
The csvjson method toCsv is doing a JSON.parse() on the received data behind the scenes and of course is throwing an error as this is not a valid JSON. Fixing this is done by the JSONStream.parse(‘*’). This method takes care of the chunk received by the read stream and provide the streaming data in a correct format by passing the objects to the transform method one by one.
One more thing that I didn’t cover and you are seeing in the code is a small logic for the line number. Passing headers option for all the lines except the first one as ‘none’ and ‘key’ for the first. This will include the headers only on the first line of our CSV, rather than before each line.
I have tested this solution with JSON array containing over 50 000 objects and I would says it’s working as expected. However, JSON streaming is a pretty new thing to me also and a more performant and optimized method could exist.
If you just want to use a full solution from a single library, without parsing and transforming it by yourself, you can check the streaming solution by — json2csv.
Please let me know in the comments section in case of questions or suggestions for improvement.
JSON CSV or TSV
Embed all the functionality of csvjson in any web application with Flatfile. Auto-match columns, validate data fields, and provide an intuitive CSV import experience.
More Details
- This function is available as a npm package.
- JSON to CSV will convert an array of objects into a table. By default, nested arrays or objects will simply be stringified and copied as is in each cell. Alternatively, you can flatten nested arrays of objects as requested by Rogerio Marques in GitHub issue #3.
- CSV stands for Comma Separated Values. Often used as an interchange data format to represent table records, one per line. CSV is plain text.
- TSV or Tab Separated Values is used to store table data in the Clipboard. You can then copy (Ctrl+C) and paste (Ctrl+V) it into Excel.
- In French, Excel will expect a semi-colons ; instead of a comma , . Make sure to pick that option if you are going to import the CSV file in Excel.
- CSV values are plain text strings. Dror Harari proposed a variant called CSVJSON (csvjson.org). The variant proposes that every CSV value be a valid JSON value. More specifically, objects and arrays would not be wrapped in double quotes but output as is. Toggle the switch Output CSVJSON variant to output that format.
- CSVJSON format variant is not valid CSV however every value is valid JSON. Parsing CSVJSON is done by processing one line at a time. Wrap a line with square brackets [] and use JSON.parse() to convert to a JSON array.
- To convert from CSVJSON back to JSON, use the companion tool CSVJSON to JSON.
- Dror Harari: «The reason why I came up with CSVJSON was not to allow embedding of JSON objects in a CSV line, that’s a nice benefit but my main reason was to have the very well defined encoding semantics of JSON (as per json.org) be used to describe CSV lines (just taking out the [ and ]).»
Jul 15, 2019
Fixed bug where BOM was missing causing the lost of accented characters in Excel. GitHub issue #78.
June 6, 2019
Fixed bug where uploading a file went to the result box instead of the json box. GitHub issue #75.
- field — String — Field Delimiter.
- delimiter — Document — Specifies the different types of delimiters