I am working with CSV file (~20MB). It has three columns as shown below and the file name presents the start time i.e. timestamp of the first row (in this case file name is 20200325131010000.csv
).
x;y;z
3;-132;976
3;-131;978
3;-130;975
4;-132;975
5;-131;976
3;-132;975
The difference between the timestamp of the consecutive row is 20 ms. How can I efficiently populate the new date column to the existing file? The final CSV file should look like this:
timestamp;x;y;z
20200325131010000;3;-132;976
20200325131010020;3;-131;978
20200325131010040;3;-130;975
20200325131010060;4;-132;975
20200325131010080;5;-131;976
20200325131010100;3;-132;975
So far I have tried the following code:
const csv = require('csv-parser');
const fs = require('fs');
var json2csv = require('json2csv').parse;
var dataArray = [];
fs.createReadStream('C:/Users/Downloads/20200325131010000.csv')
.pipe(csv())
.on('data', (row) => {
row.timestamp= "20200325131010000";
dataArray.push(row);
})
.on('end', () => {
var result = json2csv({ data: dataArray, fields: Object.keys(dataArray[0]) });
fs.writeFileSync("test.csv", result);
});
The above code generates the following output (all timestamp are the same which is not desirable):
timestamp;x;y;z
20200325131010000;3;-132;976
20200325131010000;3;-131;978
20200325131010000;3;-130;975
20200325131010000;4;-132;975
20200325131010000;5;-131;976
20200325131010000;3;-132;975
The problem with this code is that it adds the same timestamp (i.e. 20200325131010000
) to all the rows. How can I correctly populate the timestamp column?
Kindly suggest me an efficient way to achieve this.
I think this code should work and solve your problem.
const csv = require('csv-parser');
const fs = require('fs');
var json2csv = require('json2csv').parse;
const filePath = 'C:/Users/Downloads/20200325131010000.csv';
const fileName = (/^.+\/([^/]+)\.csv$/gi).exec(filePath)[1]; // extract file name from path
const intialTimestamp = parseInt(fileName); // convert string to number
let i = 0;
var dataArray = [];
fs.createReadStream(filePath)
.pipe(csv())
.on('data', (row) => {
row.timestamp= (intialTimestamp + (i * 20)).toString(); // increase the number by 20 everytime
dataArray.push(row);
i++;
})
.on('end', () => {
var result = json2csv({ data: dataArray, fields: Object.keys(dataArray[0]) });
fs.writeFileSync("test.csv", result);
});