Search code examples
javascriptcsvblobexport-to-csv

How to ignore commas when writing to CSV file

So yes, there is a lot of SO questions on this already and the consenses is to just wrap the string with "double quotes" and it should ignore the commas, this is what it says everywhere. But I am literally doing that and its just not working.

    const handleDownload = useCallback(async (rows) => {
    const rowsInCsvFormat = csvMaker(rows);
    console.log("<---rowsInCsvFormat", rowsInCsvFormat);
    /* LOG OUT:
<---rowsInCsvFormat custkey,name,address,nationkey,phone,acctbal,mktsegment,comment
"XXXXXXXXXX", "XXXXXXXX", "xOGvMn1ADS F5i1ZfDdQn9S4ejdS", "6", "16-544-978-6155", "3419.38", "FURNITURE", "egular theodolites x-ray. pending, regular dugouts instead of the bold, silent fo"
"XXXXXXXXXX", "XXXXXXXX", "aol eH4UEC 9WaFawbOqNlDhReBITfQ3eeRX", "23", "33-977-877-7892", "3511.52", "BUILDING", "slyly ironic, ironic requests. slow foxes wake ironically ironic excuses. slyly even pinto beans solve "
"XXXXXXXXXX", "XXXXXXXX", "moMK cZHgYIWZhIJXIAdCHy5H", "9", "19-793-594-4079", "1142.26", "MACHINERY", "accounts cajole fluffily regular dependencies? busy p"
"XXXXXXXXXX", "XXXXXXXXXX", "BVBbHwD ossy6XTilWoKB4f5WM9", "21", "31-898-746-6930", "2482.02", "AUTOMOBILE", "t the fluffily ironic Tiresias are according to the carefully final deposits. carefully unusual deposits alon"
"XXXXXXXXXX", "XXXXXXXXXX", "vFK kwmVwY7ADWdgCAtC7Az0bz  ,Zh", "3", "13-265-243-2018", "1278.58", "MACHINERY", " furiously quick instructions. ironic, unusual accounts haggle carefully quickly pending foxes. quickly ironic accou"
"XXXXXXXXXX", "XXXXXXXXXX", "t1Cr 8JI ebeDyb5", "10", "20-835-407-9807", "3961.77", "BUILDING", "ideas affix slyly alongside of the finally even asymptotes. regular, regular pinto beans doubt carefully:"
"XXXXXXXXXX", "XXXXXXXXXX", "GmtYfFjGCkjuOt,cbnTGKfTdumVFIe,ZWvW", "6", "16-337-644-1340", "876.74", "FURNITURE", "cies. excuses are. even foxes doubt furiously ironic requests. express deposits cajole quickly. b"
"XXXXXXXXXX", "XXXXXXXXXX", "7t3gNMUeWlfpw6", "0", "10-740-810-3918", "4351.46", "FURNITURE", "uriously final deposits haggle carefully regular accounts. quickly final requests slee"
"XXXXXXXXXX", "XXXXXXXXXX", "IWeyeEYs,,GmYeL iaiD,SdcU8LB", "3", "13-954-433-9712", "5050.01", "MACHINERY", " accounts along the blithely regular instruction"
"XXXXXXXXXX", "XXXXXXXXXX", "vBSAdbHnHjPxf1EgHQk55yO", "3", "13-476-176-8659", "8560.33", "FURNITURE", "press accounts. furiously even packages haggle quickly along the carefully silent instructions. even accounts cajol"
    */
    const blob = new Blob([rowsInCsvFormat], { type: "text/csv" });
    const url = window.URL.createObjectURL(blob);
    const a = document.createElement("a");
    a.setAttribute("href", url);
    a.setAttribute("download", "result.csv");
    a.click();
  }, []);

This is the area of the code I'm doing the string manipulation (the code is complicated because the data coming back is weird) so I'll just post the string manipulation part:

`"${subRows["r" + index].toString().replace(/"/g, '""')}"`

enter image description here

I would like this in a single cell. What am I missing here?

EDIT: Updating question with more real data, to show the actual object passed in.

Solution

  • "What am I missing here?" Quotes.

    Quotes on fields that have commas. CSV is a deceptively simple format, that most people implement wrong.

    In order for a single cell to have the text you wrote, the CSV has to contain

    "egular theodolites x-ray. pending, regular dugouts instead of the bold, silent fo"

    But the quotes you have are the JavaScript string literal quotes. They just delimit the text for JavaScript to know where it starts and where it ends. The text is just

    egular theodolites x-ray. pending, regular dugouts instead of the bold, silent fo

    You have to write this literal to produce quotes inside the text:

    "\"egular theodolites x-ray. pending, regular dugouts instead of the bold, silent fo\""

    or

    '"egular theodolites x-ray. pending, regular dugouts instead of the bold, silent fo"'

    or

    `"egular theodolites x-ray. pending, regular dugouts instead of the bold, silent fo"`

    And things get more complicated when the string itself contains a double quote, because CSV rules says you should double it.

    For a more complex example, take this data:

    name age
    Jack "The Man" Smith 32
    John, Johnny and Johnnie Johnson 42
    Jane Doe 68

    To represent it in CSV, you need to have this content:

    name,age
"Jack ""The Man"" Smith",32
"John, Johnny and Johnnie Johnson",42
Jane Doe,68

    Which means your string literal should be

    "name,age\n\"Jack \"\"The Man\"\" Smith\",32\n\"John, Johnny and Johnnie Johnson\",42\nJane Doe,68"

    Notice all the extra quotes.

    EDIT: This answer reflected the toy data that the question originally contained. However, the actual data shows a different problem. CSV, by default, only takes a comma as a field separator. If there is an adjacent space, it is taken as a part of the field text. As such, the quote stops being the first character in the field, and is so recognised as part of the text, not as the delimiter. So,

    "foo", "bar, baz, quux"

    is recognised as four fields: foo, "bar, baz, and quux" (notice the leading spaces on all but the first field; none of them start with a quote!).

    The correct representation would be

    "foo","bar, baz, quux"