Remove comma in excel while converting to json

Question

I am using xlsx to import excel and display as a table. The functionality is working fine. But there seem to be a small problem. If the cell value contains ',' then it creates a problem since xlsx javascript uses ',' as the delimiter.

Please find below the code snippet!

function filePicked(oEvent) {
// Get The File From The Input
var oFile = oEvent.target.files[0];
var sFilename = oFile.name;
// Create A File Reader HTML5
var reader = new FileReader();
// Ready The Event For When A File Gets Selected
reader.onload = function(e) {
    var data = e.target.result;
    var workbook = XLSX.read(data, {type : 'binary'});
    workbook.SheetNames.forEach(function(sheetName){
        // Here is your object
        var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
        var data = XLSX.utils.make_json(workbook.Sheets[sheetName], {header:1}); 
        var json_object = JSON.stringify(XL_row_object);
        alert(json_object);
        $.each(data, function( indexR, valueR ) {
            var sRow = "<tr>";
            $.each(data[indexR], function( indexC, valueC ) {
                sRow = sRow + "<td>" + valueC + "</td>";
            });
            sRow = sRow + "</tr>";
            $("#my_file_output").append(sRow);
        });
        //alert(json_object);

    })
};

// Tell JS To Start Reading The File.. You could delay this if desired
reader.readAsBinaryString(oFile);

}

Can anyone please guide how to overcome this.

Thanks in advance

Have added, any idea of how it can be done inside xlsx file? — rafavinu, Jun 05 '16 at 17:53
There is no error, but the cells having ',' in it is getting truncated. Ex. if the cell has 'hello,world' only hello is parsed and world is truncated! — rafavinu, Jun 06 '16 at 04:56

score -1 · Answer 1 · edited May 23 '17 at 12:22

-1

Just wrap it in double quotes

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

it's similar to dealing with commas in csv

edited May 23 '17 at 12:22

Community

1
1

answered Jun 05 '16 at 15:55

Joshua Duxbury

4,430
4
29
49

This should be done before importing and saving as json? – rafavinu Jun 05 '16 at 16:43
That should happen in the parser i believe! Since before it is jsonified, ',' are missing! – rafavinu Jun 05 '16 at 17:07

Remove comma in excel while converting to json

1 Answers1