Converting csv files to an array of JavaScript objects and vice versa. Can group input data.
npm i csv-to-js-parser
Suppose we have a table with customers and the goods they ordered in the store (Table 1).
Table 1
customer_id | customer_name | customer_status | product_id | product | price | closed |
---|---|---|---|---|---|---|
1 | Bob | 0 | 1 | computer | 550 | true |
1 | Bob | 0 | 2 | monitor | 400 | false |
1 | Bob | 0 | 3 | mobile phone | 970 | true |
1 | Bob | 0 | 4 | mouse | 7 | true |
2 | lice | 1 | 5 | laptop | 1200 | true |
2 | Alice | 1 | 4 | mouse | 7 | false |
3 | Eve | 1 | 6 | microphone | 20 | true |
3 | Eve | 1 | 7 | router | 105 | false |
3 | Eve | 1 | 5 | laptop | 1200 | false |
Here, each customer has a unique identifier: "customer_id".
Table 1 has columns where repeated values occur in rows. For example, customer_id will be the same in the first four lines, as these lines describe the purchases of the same customer. In these situations, you usually don't need to convert each row to a separate JavaScript object, but it is necessary that there is one object per customer.
Using this module, you can convert the above table into an array, where each element of this array is a JavaScript object that describes the parameters of a particular customer and his purchase.
[
{
customer_id: 1,
customer_name: 'Bob',
customer_status: 0,
product: ['computer', 'monitor', 'mobile phone', 'mouse'],
product_id: [1, 2, 3, 4],
price: [550, 400, 970, 7],
closed: [true, false, true, true]
},
{
customer_id: 2,
customer_name: 'Alice',
customer_status: 1,
product: ['laptop', 'mouse'],
product_id: [5, 4],
price: [1200, 7],
closed: [true, false]
},
{
customer_id: 3,
customer_name: 'Eve',
customer_status: 1,
product: ['microphone', 'router', 'laptop'],
product_id: [6, 7, 5],
price: [20, 105, 1200],
closed: [true, false, false]
}
]
Let's look at the source code for getting such result. Let the input table be stored in the "data.csv" file, where the column separators are the ";".
const fs = require('fs');
const csvToObj = require('csv-to-js-parser').csvToObj;
const data = fs.readFileSync('data.csv').toString();
const description =
{
customer_id: {type: 'number', group: 1},
product: {type: 'string'},
product_id: {type: 'number'},
customer_name: {type: 'string', group: 2},
price: {type: 'number'},
closed: {type: 'boolean'},
customer_status: {type: 'number', group: 2}
};
let obj = csvToObj(data, ';', description);
The csvToObj function accepts the following parameters:
-
data: csv table as a string.
-
delimeter [optional]: column separator in the input table. If not specified, then the default is comma ",".
-
description [optional]: description of the input table.
The description object describes the input table:
- type: describes the type of column values. Three options are supported: "number", "string" and "boolean".
- group: the order of grouping. Have to be set only for the columns that need to be grouped. There must be at least one such column. The values of this property indicate the order in which the columns should be grouped. For example columns with "group: 1" are grouped first, then columns with "group: 2" are grouped inside the first grouping and so on. You can perceive the group property in the same way as a "Group By" construct in SQL.
If the description parameter is not specified, then a string type is assigned to all columns of the input table and grouping is performed by all columns. This means that each row of the input table will be converted to a separate JavaScript object.
The delimeter and description parameters can be set in a different order: first description, then delimeter.
Consider another example where you can better see how grouping works. Let the input data be table 2.
Table 2
sex | age | person_id |
---|---|---|
male | 30 | 1 |
male | 30 | 2 |
male | 30 | 3 |
male | 31 | 4 |
male | 31 | 4 |
male | 31 | 5 |
male | 31 | 5 |
male | 31 | 6 |
male | 32 | 7 |
male | 32 | 7 |
male | 32 | 8 |
female | 30 | 9 |
female | 30 | 9 |
female | 30 | 10 |
female | 30 | 10 |
female | 31 | 11 |
female | 31 | 12 |
female | 33 | 13 |
female | 33 | 14 |
female | 33 | 14 |
We need to get an array of JavaScript objects in which the data from table 2 will be grouped first by age and then by sex. In this case, the description object will be as follows:
const description =
{
age: {type: 'number', group: 1},
sex: {type: 'string', group: 2},
person_id: {type: 'number'}
};
The output from csvToObj founction will be as follows:
[
{
sex: 'female',
age: 30,
person_id: [9, 9, 10, 10]
},
{
sex: 'male',
age: 30,
person_id: [1, 2, 3]
},
{
sex: 'female',
age: 31,
person_id: [11, 12]
},
{
sex: 'male',
age: 31,
person_id: [4, 4, 5, 5, 6]
},
{
sex: 'male',
age: 32,
person_id: [7, 7, 8]
},
{
sex: 'female',
age: 33,
person_id: [13, 14, 14]
}
]
You can notice that in the objects that the csvToObj function returns, the properties that are responsible for non-grouped columns contain arrays of the same length. Using the combineArrays function, you can convert these arrays to a single array of objects, which in some cases may be a more natural representation of data in JavaScript.
The code for working with this function for the example from table 1:
const combineArrays = require('csv-to-js-parser').combineArrays;
obj = combineArrays(obj, 'products', ['product_id', 'product', 'price', 'closed'], ['product_id', 'name', 'price', 'closed']);
The combineArrays function takes the following parameters:
- obj: input object (usually from csvToObj).
- newKey: the name of the property in which the arrays will be combined.
- arrayKeys: the names of the properties in the input object that are arrays.
- newArrayKeys [optional]: if specified: the names of properties that override properties from arrayKeys, i.e. the output object will have newArrayKeys properties instead of arrayKeys properties.
The result of the combineArrays function for example from table 1 is shown below:
[
{
customer_id: 1,
customer_name: 'Bob',
products:
[
{
product_id: 1,
name: 'computer',
price: 550,
closed: true
},
{
product_id: 2,
name: 'monitor',
price: 400,
closed: false
},
{
product_id: 3,
name: 'mobile phone',
price: 970,
closed: true
},
{
product_id: 4,
name: 'mouse',
price: 7,
closed: true
}
],
customer_status: 0
},
{
customer_id: 2,
customer_name: 'Alice',
products:
[
{
product_id: 5,
name: 'laptop',
price: 1200,
closed: true
},
{
product_id: 4,
name: 'mouse',
price: 7,
closed: false
},
],
customer_status: 1
},
{
customer_id: 3,
customer_name: 'Eve',
products:
[
{
product_id: 6,
name: 'microphone',
price: 20,
closed: true
},
{
product_id: 7,
name: 'router',
price: 105,
closed: false
},
{
product_id: 5,
name: 'laptop',
price: 1200,
closed: false
},
],
customer_status: 1
}
]
To reverse conversion to an object with separate arrays, you can use the separateArrays function.
const separateArrays = require('csv-to-js-parser').separateArrays;
obj = separateArrays(obj, 'products', ['product_id', 'name', 'price', 'closed'], ['product_id', 'product', 'price', 'closed']);
In this function, parameters are similar to those used in combineArrays:
- obj: input object (usually from combineArrays).
- objArrayKey: name of the property where the arrays are combined.
- arrayKeys: names of properties in the input object to convert it to separate arrays .
- newArrayKeys [optional]: if specified, the names of properties that override properties from arrayKeys, i.e. the output object will have newArrayKeys properties instead of arrayKeys properties.
The result of this function will be the initial array of objects obtained from csvToObj.
To save objects from csvToObj or combineArrays functions to a file, you can use the built-in node.js function JSON.stringify().
const json = JSON.stringify(obj, null, ' ');
fs.writeFileSync('data.json', json);
You can use the objToCsv function to reverse convert an array of objects from csvToObj to CSV text format.
const objToCsv = require('csv-to-js-parser').objToCsv;
const csv = objToCsv(obj, ';');
fs.writeFileSync('newData.csv', csv);
The objToCsv function accepts the following parameters:
- obj: input array of objects (the format must match the one returned by csvToObj).
- delimeter [optional]: column delimiter in the output table. If not specified, then the default is comma ",".
- rowDelimeter [optional]: rows separator. If not specified, then the default is "LF" (\n). For windows, it is reasonable to specify the "CRLF" delimiter (\r\n).
https://github.com/Mendeo/csv-to-js-parser/blob/master/LICENSE