I have a solution that parses a csv, however it does not take into account data that has a comma within the quoted field. (example "not, accounted","normal")
let filePath = Path.resolve(__dirname, `./MyData.csv`);
let data = fs.readFileSync(filePath, 'utf-8');
data = data.replace(/"/g, '');
data = data.split(/\r?\n/);
for (let i in data) {
data[i] = data[i].split(",");
}
data.forEach(async customerEmailToAdd => {
if (customerEmailToAdd[0] != 'id') {
const sql = `
UPDATE customers
SET contactEmail = '${customerEmailToAdd[4]}',
contactName = '${customerEmailToAdd[3]}'
WHERE Id = '${customerEmailToAdd[0]}';
`;;
await queryInterface.sequelize.query(sql);
};
});
You issue is that you are trying to use split and replace to parse a .csv and this 2 functions are not a really good idea for this (for a lot of specific cases, like a wild comma in a value). You should consider reading the file character by character using a state machine to know what you are reading. Because you can also find something like this: "not, \"accounted\""
But, if you want to keep with your current method, you can replace the comma that are between two quotes by a temporary placeholder. Something like ###COMMA###, just make sure that this placeholder will never appear in a real case.
You can use the following code for this : data = data.replace(/"(.*?)"/g, (str) => str.replaceAll(',', '###COMMA###'));
Then you use split and replace to parse the csv file, and you replace the placeholder by real commas : data = data.replaceAll('###COMA###', ',');