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

Question

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

it makes my eyes hurt, but you can try using this `str.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");` from here https://stackoverflow.com/a/18893443/294949 — danh, Aug 08 '22 at 14:41
Also, possible dup of the containing question: https://stackoverflow.com/questions/18893390/splitting-on-comma-outside-quotes — danh, Aug 08 '22 at 14:42
Does this answer your question? [Splitting on comma outside quotes](https://stackoverflow.com/questions/18893390/splitting-on-comma-outside-quotes) — derpirscher, Aug 08 '22 at 15:58

score -1 · Accepted Answer · edited Apr 19 '23 at 04:03

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

For completeness, in case a beginner comes across this question and thinks they might need to implement CSV parsing manually: I highly recommend using a library. `csv`, `csv-parse`, `papaparse`, and other modules available on npm are likely much better-tested than one-off CSV parsing implementations. — Zac Anger, Apr 19 '23 at 04:08

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

1 Answers1