How do I parse a .csv file with quotes and commas within the quotes Node.js-CodePudding

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

CodePudding user response：

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###', ',');