How to deal with Commas in CSV using Javascript

Question

I am having some data which is facing issue while exporting to csv if the data contains "," with in it.

Hereis the code:

  CSVData.push('"' + item.OrgId+ '","' + item.Name + '","' + item.OrgDescriptionString + '","' + itam.OrgDetailsString + '","' + item.Active+ '"');

if orgDetaisl string contains "," with in it, the data is getting overlapped and moving to next column.

How to escape "," .I tried using double quotes """ but not sure how can i achieve.

CSVData.push('"' + item.OrgId+ '","' + item.Name + '","\""' + item.OrgDescriptionString + '"\"","\""' + item.OrgDetailsString + ' \"","' + item.Active+ '"');

Please correct the string and help me to escape "," with in the data.

Far

When you create the CSV use a different column separator – Sasaki

@Gerard! Then it is not a CSV – Galipot

Answer 1

The proper way to escape separators inside fields is to enclose the fields in double quotes:

Id,Description
123,"Monitor, Samsung"

But then quotes inside the field have to be escaped with another quote:

Id,Description
123,"Monitor 24"", Samsung"

Of course, a different delimiter like ; could be used, but then you'd still have to check if those appear in the data.

Accelerometer

What you suggested didn't work. Please provide fiddler for it – Wobbling

Answer 2

I tried to with the below code and it worked for me

item.OrgDescriptionString.replace(/\"/g, "\"\"");

I tried to escape single quotes in the string and it worked.

Far

Answer 3

This is how I have achieved the reported functionality.

In brief: Requirement was to fetch the comma-separated string as it is in the CSV Export.

Assuming that we have a comma-separated string which needs to be exported in the CSV as it is.

For example String value which comes from the data source as:

'StackOverflow', 'Awesome! work by StackOverflow team, cheers to them.'

Solution:

['stackoverflow', 'Awesome! work by stackoverflow team, cheers to them.'].map(string => string === null ? '' : `\"${string}\"`);

Explanation: So if we quote the comma-separated values and try exporting than the CSV will not consider the comma(,) as a field separator and due to which in CSV export it won't split into multiple columns with respect to numbers of available commas. And the content will appear in the same column.

Ref wiki link: Comma-separated_values

Hope this helps. Cheers!!

Jamila

if you have a value with quotes already this solution wont work. It works fine for other scenarios. – Philia

Answer 4

Use double-quotes enclosed string and escape double-quotes in string:

csvData.push( "\\"" + strData.replace( /\\"/g , "\\"\\"" ) + "\\"" )

Demagogy

Answer 5

One of the probable solution could be to read the file and then replace the read data with ""

Something like this:

readData = readData.replace(/,/g, "");

Jasminjasmina

Answer 6

I had a similar situation, here is the string that needs to be printed in the csv file:

'"P1", "Graded Reader": "The Three", Bears 15 Jun 2023'

then i solved it by use Template literals (Template strings):

const exampleString = '"P1", "Graded Reader": "The Three", Bears';
const time = '15 Jun 2023';

const finalString = `"${exampleString?.replace(/"/g, '""')}" ${time}`;

Insociable

Answer 7

It is very simple, suppose an address variable containing the commas,, then you should enclosed it in double quotes.

data.push('"'+address+'"');

or

in my case it is working fine!

Tanganyika