Replace string for a specific column (csv file) in Nodejs-CodePudding

I want to replace string of a particular column from a .csv file in Nodejs

here is my .csv data:

ID,Name,openingBalance,closingBalance
"27G","HARRIS TODD",23.22,465.22
"28G","ANGELO RALPH",124.31,555.20
"28N","GRODKO STEVEN",45.22,
"29A","FOWLER ROBERT",65.25,666.00
"29G","PROVOST BRIAN",,253.11
"300","BECKMAN JUDITH",114.21,878.21

in the closingBalance column there is a blank which I need to be replace as 0.00

I am able to replace the whole data, but not for the specific column,

Can anyone please help?

I used this for replace string :

var fs = require('fs')
fs.readFile(someFile, 'utf8', function (err,data) {
  if (err) {
    return console.log(err);
  }
  var result = data.replace(/string to be replaced/g, 'replacement');

  fs.writeFile(someFile, result, 'utf8', function (err) {
     if (err) return console.log(err);
  });
});

CodePudding user response：

You can use regular expression to replace blank cells, but that's easy only for first/last columns: data.replace(/,$/gm, ',0.00'); See Regex101 for further details and playground

Other way is to parse CSV to AoA:

const csv = `ID,Name,openingBalance,closingBalance
"27G","HARRIS TODD",23.22,465.22
"28G","ANGELO RALPH",124.31,555.20
"28N","GRODKO STEVEN",45.22,
"29A","FOWLER ROBERT",65.25,666.00
"29G","PROVOST BRIAN",,253.11
"300","BECKMAN JUDITH",114.21,878.21`;

const aoa = csv
  .split(/\r?\n/g)
  .map((row) => {
    let [ID, Name, openingBalance, closingBalance] = row.split(',');
    // Fix empty values for "openingBalance" column
    if (!openingBalance.trim()) {
      openingBalance = '0.00';
    }
    // Fix empty values for "closingBalance" column
    if (!closingBalance.trim()) {
      closingBalance = '0.00';
    }
    return [ID, Name, openingBalance, closingBalance]
  });

// now you have AoA with fixed values

console.log(aoa.map((row) => row.join(',')).join('\n'))

With this way, you can pre-moderate any column with any code.

CodePudding user response：

You can use regex to find out blank cells for closingBalance column.

The cells for this particular column is the last one in each record which can be easily found by \n and $ in regex.

So to do this:

const result = data.replace(/(,)(?=(\n|$))/g, '$10.00');

of if you want to find out any blank cells, you can use the following regex:

/(,)(?=(,|\n|$))/g