javascriptnode.jscsvnode-csv-parse

How to populate new date column to existing CSV file


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.


Solution

  • 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);
          });