CODEWITHSUNDEEP
javascriptnode.js
RTran25
RTran25

Reputation: 11

How to write a string containing commas to CSV?

I am trying to write data into a csv file. The data will always be a string, but it sometimes contains a comma. How do I include the comma when writing to the csv file without it separating them into different columns? I am using the fs module to create the csv file. I would like an explanation if you're using regex as I am not too familiar with it.

const fs = require('fs');

let testArr = ['test1','test,2','test3'];

async function main(){
    try{
        fs.writeFile(('./testCsv.csv'), testArr.join("\n"), 'utf8', function (err){
            if(err) throw err;
            console.log('Saved!');
        });
    }
}

I want the output to look like ( | is used to separate as columns) test1|test,2|test3 but it instead looks like test1|test|2|test3

One thing I have tried is

const fs = require('fs');

let testArr = ['test","5','test","6','test","7'];

async function main(){
    try{
        fs.writeFile(('./testCsv.csv'), testArr.join("\n"), 'utf8', function (err){
            if(err) throw err;
            console.log('Saved!');
        });
    }
}

but the output looks like

test"|5,test|6,test|7

with the 7 looking weird in the file (it was not centered and up-left from it)

Upvotes: 0

Views: 5175

Answers (3)

StriplingWarrior
StriplingWarrior

Reputation: 156459

The short answer is that you need to throw double-quotes around any values that contain commas (or double-quotes, or new lines).

Of course, that introduces a new question: what if the cell has double-quotes in it? And the answer to that is that those double-quotes need to be escaped with yet another double-quote.

['test","5','test","6','test","7']
---- should become ----
"test"",""5","test"",""6","test"",""7"

See the Basic Rules of CSV for more details. While there's no official standard for CSV, these are adhered to frequently enough that it'll probably work for your use case.

I don't have node running, but something along these lines might work:

const fs = require('fs');

let testArr = [ ['test","5','test","6','test","7'] ];

function escape(value)
{
     if(!['"','\r','\n',','].some(e => value.indexOf(e) !== -1)) {
         return value;
     }
     return '"' + value.replace(/"/g, '""') + '"';
}

async function main(){
    try{
        let lines = testArr
            .map(line => line.map(cell => escape(cell)).join(","));
        fs.writeFile(('./testCsv.csv'), lines.join("\n"), 'utf8', function (err){
            if(err) throw err;
            console.log('Saved!');
        });
    }
}

Regarding the regex, /"/g:

  • The forward slashes (/) tell JavaScript that you're making a regex. Everything between them is the regex pattern. The characters following them are flags.
  • " says you're trying to find double-quote characters
  • The g flag after the closing slash says you want to replace all occurrences of the pattern, not just the first one you encounter.

I haven't tested this, and even if it works I cannot guarantee it'll work in all cases. You may find it worthwhile to delegate the whole CSV-formatting process to a library which specializes in that sort of thing. For example, with json2csv you'd only need to map your inputs into json objects and then tell the parser which fields you want to output on those objects.

Upvotes: 1

Randy Casburn
Randy Casburn

Reputation: 14165

Given your requirement:

I want the output to look like ( | is used to separate as columns) test1|test,2|test3

AND assuming the rules pointed out in the comments will be considered separately, here is the simplest solution:

const testArr = [['test1','test,2','test3'],['test1','test,2','test3'],['test1','test,2','test3'],['test1','test,2','test3']];

const barSeparatedValues = testArr.map(arr=>arr.join("|")).join("\n");

console.log(barSeparatedValues);

Notice there are four "rows" of data being separated by newline characters while the "columns" are being separated by |.

Upvotes: 0

Deepstop
Deepstop

Reputation: 3807

Double quotes suggested by StriplingWarrior works for reading the file into MS-Excel, and escaping any double-quotes by using 2 double-quotes is necessary if they might appear in the string.

I just tested it in Excel, and multiple commas do not count as separators when simply opening the csv file with Excel. In both MS-Access and Excel, you can use the data import functions and get more control over how the syntax of the file you are importing is treated.

Mileage may vary with other applications, but double-quotes would be the first guess in any case.

Upvotes: 0

Related Questions