Javascript CSV export comma issue

Question

I have a javascript function I am using to convert some JSON data to an excel export. For the most part, everything is working just fine.

I noticed however that one of my column names now has a comma in it (intentional) LastName, FirstName.

With my existing code, its causing the header column to be separated and moved over into its own column when I expect it to be a single column header.

/**
 * Convert the JSON to a CSV File
 * @param {*} JSONData 
 * @param {*} ReportTitle 
 * @param {*} ShowLabel 
 */
function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {

    //If JSONData is not an object then JSON.parse will parse the JSON string in an Object
    var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
    var CSV = '';
    //This condition will generate the Label/Header
    if (ShowLabel) {
        var row = "";

        //This loop will extract the label from 1st index of on array
        for (var index in arrData[0]) {
            //Now convert each value to string and comma-seprated
            row += index + ',';
        }
        row = row.slice(0, -1);
        //append Label row with line break
        CSV += row + '\r\n';
    }

    //1st loop is to extract each row
    for (var i = 0; i < arrData.length; i++) {
        var row = "";
        //2nd loop will extract each column and convert it in string comma-seprated
        for (var index in arrData[i]) {
            row += '"' + arrData[i][index] + '",';
        }
        row.slice(0, row.length - 1);
        //add a line break after each row
        CSV += row + '\r\n';
    }

    if (CSV == '') {
        alert("Invalid data");
        return;
    }

    var csv = CSV;
    blob = new Blob([csv], {
        type: 'text/csv'
    });


    if (window.navigator && window.navigator.msSaveOrOpenBlob) {
        window.navigator.msSaveOrOpenBlob(blob, ReportTitle);
    } else {
        var objectUrl = URL.createObjectURL(blob);
        window.open(objectUrl);
    }


}

I believe my error is in the if (ShowLabel) { statement.

Is there a way I can ignore the commas in the header row so that my columns will remain aligned?

Error:

Desired:

Any thoughts on how to ignore the commas in the header row?

I mean, not splitting on a delimiter in CSV format is a bit odd. You could set it up to split the string on every other comma if the column is "Last Name, First Name", but you would have to be sure that each cell had the exact same format. A.e. no missing names. And of course, this would be very specific so changing the column name at any time would require a change in code. — zfrisch
– zfrisch, Commented Dec 3, 2018 at 15:24

Oram · Accepted Answer · 2018-12-03 15:54:03Z

16

I believe you should add quotes around your labels so the comma inside (the quotes) is not treated as a separator

for (var index in arrData[0]) {
    //Now convert each value to string and comma-seprated
    row += '\"' + index + '\",';
}

In your screenshot, Bob, Jones is correctly assigned to one cell although there's a comma. If you open the CSV file in Notepad for instance you should see Bob, Jones has quotes.

edited Dec 3, 2018 at 15:54

Oram

1,7012 gold badges19 silver badges24 bronze badges

answered Dec 3, 2018 at 15:28

Eponyme Web

9765 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SBB Over a year ago

That looks like it worked - I should have noticed that from the code lower down that had the same thing :/

Kirataka Over a year ago

How to do this for its below data not the header.

Jagdish Chopde Over a year ago

Thanks a lot! I have commas in my fields and those were dividing the single field into multiple columns. I have been looking at how to change the comma delimiter but not getting any solution. Whereas this worked perfectly. small thing eat my lots of time. Thanks again.

Collectives™ on Stack Overflow

Javascript CSV export comma issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related