CSVTOJSON

All you need nodejs csv to json converter. * Large CSV data * Command Line Tool and Node.JS Lib * Complex/nested JSON * Easy Customised Parser * Stream based * multi CPU core support * Easy Usage * more!

Demo

Here is a free online csv to json service ultilising latest csvtojson module.

Menu

• Installation
• Usage
• Library
  • Convert from a file
  • Convert from a web resource / Readable stream
  • Convert from CSV string
• Parameters
• Result Transform
• Synchronouse Transformer
• Asynchronouse Transformer
• Convert to other data type
• Hooks
• Events
• Flags
• Big CSV File Streaming
• Process Big CSV File in CLI
• Parse String
• Empowered JSON Parser
• Field Type
• Multi-Core / Fork Process
• Header Configuration
• Error Handling
• Customised Parser
• Stream Options
• Change Log

GitHub: https://github.com/Keyang/node-csvtojson

Installation

npm install -g csvtojson

npm install csvtojson --save

Usage

library

From File

You can use File stream

//Converter Class
var Converter = require("csvtojson").Converter;
var converter = new Converter({});

//end_parsed will be emitted once parsing finished
converter.on("end_parsed", function (jsonArray) {
   console.log(jsonArray); //here is your result jsonarray
});

//read from file
require("fs").createReadStream("./file.csv").pipe(converter);

Or use fromFile convenient function

//Converter Class
var Converter = require("csvtojson").Converter;
var converter = new Converter({});
converter.fromFile("./file.csv",function(err,result){

});

From Web

To convert any CSV data from readable stream just simply pipe in the data.

//Converter Class
var Converter = require("csvtojson").Converter;
var converter = new Converter({constructResult:false}); //for big csv data

//record_parsed will be emitted each csv row being processed
converter.on("record_parsed", function (jsonObj) {
   console.log(jsonObj); //here is your result json object
});

require("request").get("http://csvwebserver").pipe(converter);

From String

var Converter = require("csvtojson").Converter;
var converter = new Converter({});
converter.fromString(csvString, function(err,result){
  //your code here
});

Command Line Tools

csvtojson

Example

csvtojson ./myCSVFile

Or use pipe:

cat myCSVFile | csvtojson

Check current version:

csvtojson version

Advanced usage with parameters support, check help:

csvtojson --help

Params

The constructor of csv Converter allows parameters:

var converter=new require("csvtojson").Converter({
  constructResult:false,
  workerNum:4,
  noheader:true
});

Following parameters are supported:

• constructResult: true/false. Whether to construct final json object in memory which will be populated in "end_parsed" event. Set to false if deal with huge csv data. default: true.
• delimiter: delimiter used for seperating columns. Use "auto" if delimiter is unknown in advance, in this case, delimiter will be auto-detected (by best attempt). Use an array to give a list of potential delimiters e.g. [",","|","$"]. default: ","
• quote: If a column contains delimiter, it is able to use quote character to surround the column content. e.g. "hello, world" wont be split into two columns while parsing. Set to "off" will ignore all quotes. default: " (double quote)
• trim: Indicate if parser trim off spaces surrounding column content. e.g. " content " will be trimmed to "content". Default: true
• checkType: This parameter turns on and off weather check field type. default is true. See Field type
• toArrayString: Stringify the stream output to JSON array. This is useful when pipe output to a file which expects stringified JSON array. default is false and only stringified JSON (without []) will be pushed to downstream.
• ignoreEmpty: Ignore the empty value in CSV columns. If a column value is not giving, set this to true to skip them. Defalut: false.
• workerNum: Number of worker processes. The worker process will use multi-cores to help process CSV data. Set to number of Core to improve the performance of processing large csv file. Keep 1 for small csv files. Default 1.
• fork(Deprecated, same as workerNum=2): Use another CPU core to process the CSV stream.
• noheader:Indicating csv data has no header row and first row is data row. Default is false. See header configuration
• headers: An array to specify the headers of CSV data. If --noheader is false, this value will override CSV header row. Default: null. Example: ["my field","name"]. See header configuration
• flatKeys: Don't interpret dots (.) and square brackets in header fields as nested object or array identifiers at all (treat them like regular characters for JSON field identifiers). Default: false.
• maxRowLength: the max character a csv row could have. 0 means infinite. If max number exceeded, parser will emit "error" of "row_exceed". if a possibly corrupted csv data provided, give it a number like 65535 so the parser wont consume memory. default: 0
• checkColumn: whether check column number of a row is the same as headers. If column number mismatched headers number, an error of "mismatched_column" will be emitted.. default: false
• eol: End of line character. If omitted, parser will attempt retrieve it from first chunk of CSV data. If no valid eol found, then operation system eol will be used.
• escape: escape character used in quoted column. Default is double quote (") according to RFC4108. Change to back slash () or other chars for your own case.

All parameters can be used in Command Line tool. see

csvtojson --help

Result Transform

To transform JSON result, (e.g. change value of one column), just simply add 'transform handler'.

Synchronouse transformer

var Converter=require("csvtojson").Converter;
var csvConverter=new Converter({});
csvConverter.transform=function(json,row,index){
    json["rowIndex"]=index;
    /* some other examples:
    delete json["myfield"]; //remove a field
    json["dateOfBirth"]=new Date(json["dateOfBirth"]); // convert a field type
    */
};
csvConverter.fromString(csvString,function(err,result){
  //all result rows will add a field 'rowIndex' indicating the row number of the csv data:
  /*
  [{
    field1:value1,
    rowIndex: 0
 }]
  */
});

As shown in example above, it is able to apply any changes to the result json which will be pushed to down stream and "record_parsed" event.

Asynchronouse Transformer

Asynchronouse transformation can be achieve either through "record_parsed" event or creating a Writable stream.

Use record_parsed

To transform data asynchronously, it is suggested to use csvtojson with Async Queue.

This mainly is used when transformation of each csv row needs be mashed with data retrieved from external such as database / server / file system.

However this approach will not change the json result pushed to downstream.

Here is an example:

var Conv=require("csvtojson").Converter;
var async=require("async");
var rs=require("fs").createReadStream("path/to/csv"); // or any readable stream to csv data.
var q=async.queue(function(json,callback){
  //process the json asynchronously.
  require("request").get("http://myserver/user/"+json.userId,function(err,user){
    //do the data mash here
    json.user=user;
    callback();
  });
},10);//10 concurrent worker same time
q.saturated=function(){
  rs.pause(); //if queue is full, it is suggested to pause the readstream so csvtojson will suspend populating json data. It is ok to not to do so if CSV data is not very large.
}
q.empty=function(){
  rs.resume();//Resume the paused readable stream. you may need check if the readable stream isPaused() (this is since node 0.12) or finished.
}
var conv=new Conv({construct:false});
conv.transform=function(json){
  q.push(json);
};
conv.on("end_parsed",function(){
  q.drain=function(){
    //code when Queue process finished.
  }
})
rs.pipe(conv);

In example above, the transformation will happen if one csv rown being processed. The related user info will be pulled from a web server and mashed into json result.

There will be at most 10 data transformation woker working concurrently with the help of Async Queue.

Use Stream

It is able to create a Writable stream (or Transform) which process data asynchronously. See Here for more details.

Convert to other data type

Below is an example of result tranformation which converts csv data to a column array rather than a JSON.

var Converter=require("csvtojson").Converter;
var columArrData=__dirname+"/data/columnArray";
var rs=fs.createReadStream(columArrData);
var result = {}
var csvConverter=new Converter();
//end_parsed will be emitted once parsing finished
csvConverter.on("end_parsed", function(jsonObj) {
    console.log(result);
    console.log("Finished parsing");
    done();
});

//record_parsed will be emitted each time a row has been parsed.
csvConverter.on("record_parsed", function(resultRow, rawRow, rowIndex) {

    for (var key in resultRow) {
        if (!result[key] || !result[key] instanceof Array) {
            result[key] = [];
        }
        result[key][rowIndex] = resultRow[key];
    }

});
rs.pipe(csvConverter);

Here is an example:

    TIMESTAMP,UPDATE,UID,BYTES SENT,BYTES RCVED
    1395426422,n,10028,1213,5461
    1395426422,n,10013,9954,13560
    1395426422,n,10109,221391500,141836
    1395426422,n,10007,53448,308549
    1395426422,n,10022,15506,72125

It will be converted to:

{
  "TIMESTAMP": ["1395426422", "1395426422", "1395426422", "1395426422", "1395426422"],
  "UPDATE": ["n", "n", "n", "n", "n"],
  "UID": ["10028", "10013", "10109", "10007", "10022"],
  "BYTES SENT": ["1213", "9954", "221391500", "53448", "15506"],
  "BYTES RCVED": ["5461", "13560", "141836", "308549", "72125"]
}

Hooks

preProcessRaw

This hook is called when parser received any data from upper stream and allow developers to change it. e.g.

/*
CSV data:
a,b,c,d,e
12,e3,fb,w2,dd
*/

var conv=new Converter();
conv.preProcessRaw=function(data,cb){
    //change all 12 to 23
    cb(data.replace("12","23"));
}
conv.fromString(csv,function(err,json){
  //json:{a:23 ....}
})

By default, the preProcessRaw just returns the data from the source

Converter.prototype.preProcessRaw=function(data,cb){
  cb(data);
}

It is also very good to sanitise/prepare the CSV data stream.

var headWhiteSpaceRemoved=false;
conv.preProcessRaw=function(data,cb){
    if (!headWhiteSpaceRemoved){
      data=data.replace(/^\s+/,"");
      cb(data);
    }else{
      cb(data);
    }
}

preProcessLine

this hook is called when a file line is emitted. It is called with two parameters fileLineData,lineNumber. The lineNumber is starting from 1.

/*
CSV data:
a,b,c,d,e
12,e3,fb,w2,dd
*/

var conv=new Converter();
conv.preProcessLine=function(line,lineNumber){
    //only change 12 to 23 for line 2
    if (lineNumber === 2){
      line=line.replace("12","23");
    }
    return line;
}
conv.fromString(csv,function(err,json){
  //json:{a:23 ....}
})

Notice that preProcessLine does not support async changes not like preProcessRaw hook.

Events

Following events are used for Converter class:

• end_parsed: It is emitted when parsing finished. the callback function will contain the JSON object if constructResult is set to true.
• record_parsed: it is emitted each time a row has been parsed. The callback function has following parameters: result row JSON object reference, Original row array object reference, row index of current row in csv (header row does not count, first row content will start from 0)

To subscribe the event:

//Converter Class
var Converter=require("csvtojson").Converter;

//end_parsed will be emitted once parsing finished
csvConverter.on("end_parsed",function(jsonObj){
    console.log(jsonObj); //here is your result json object
});

//record_parsed will be emitted each time a row has been parsed.
csvConverter.on("record_parsed",function(resultRow,rawRow,rowIndex){
   console.log(resultRow); //here is your result json object
});

Flags

There are flags in the library:

*omit*: Omit a column. The values in the column will not be built into JSON result.

*flat*: Mark a head column as is the key of its JSON result.

Example:

*flat*user.name, user.age, *omit*user.gender
Joe , 40, Male

It will be converted to:

[{
  "user.name":"Joe",
  "user":{
    "age":40
  }
}]

Big CSV File

csvtojson library was designed to accept big csv file converting. To avoid memory consumption, it is recommending to use read stream and write stream.

```js var Converter=require("csvtojson").Converter; var csvConverter=new Converter({constructResult:false}); // The parameter false will turn off final result construction. It can avoid huge memory consumption while parsing. The trade off is final result will not be populated to end_parsed event.

var readStream=require("fs").createReadStream("inputData.csv");

var writeStream=