Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Javascript CSV export comma issue

Tags:

javascript

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:

enter image description here

Desired:

enter image description here

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

like image 752
SBB Avatar asked Dec 03 '18 15:12

SBB


People also ask

Why do commas mess with CSV files?

This comma breaks the CSV format, since it's interpreted as a new column. I've read up and the most common prescription seems to be replacing that character, or replacing the delimiter, with a new value (e.g. this|that|the, other ).

How do you handle commas in data when exporting a CSV file?

Re: Handling 'comma' in the data while writing to a CSV. So for data fields that contain a comma, you should just be able to wrap them in a double quote. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

Can CSV values have commas?

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.


1 Answers

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.

like image 63
Eponyme Web Avatar answered Sep 21 '22 15:09

Eponyme Web