Export Json To Csv Or Excel With Utf-8 (e.g. Greek) Encoding Using Javascript

I am trying to export and download a JSON object to CSV file and I have problem with Greek characters. My code works; it is not perfect, but it works. The problem is that Greek ch

Solution 1:

Export to CSV

Exporting to CSV with non-ASCII characters requires prepending the file with the Byte Order Mark aka BOM. In your code change

var str = "";

to:

var str = "\uFEFF";

You need a modern version of Excel to recognize the BOM. As mentioned in this helpful StackOverflow article, Excel 2003 and earlier will not honor the BOM correctly. I only have access to Excel 2003 on Windows, so I cannot test this at the moment, but it's fairly well documented.

Sadly, Excel 2011 for the Macintosh is NOT a "modern Excel" in this sense. Happily, Google Sheets do the right thing.

Export directly to Excel

Here's a jsFiddle implementation of the code below. It generates a SpreadsheetXml document. The upside to this method is you could get VERY tricky ... adding in formulas and doing a lot more things specific to Excel.

// Test script to generate a file from JavaScript such// that MS Excel will honor non-ASCII characters.

testJson = [
    {
        "name": "Tony Peña",
        "city": "New York",
        "country": "United States",
        "birthdate": "1978-03-15",
        "amount": 42

    },
    {
        "name": "Ζαλώνης Thessaloniki",
        "city": "Athens",
        "country": "Greece",
        "birthdate": "1987-11-23",
        "amount": 42
    }
];

// Simple type mapping; dates can be hard// and I would prefer to simply use `datevalue`// ... you could even add the formula in here.
testTypes = {
    "name": "String",
    "city": "String",
    "country": "String",
    "birthdate": "String",
    "amount": "Number"
};

emitXmlHeader = function () {
    var headerRow =  '<ss:Row>\n';
    for (var colName in testTypes) {
        headerRow += '  <ss:Cell>\n';
        headerRow += '    <ss:Data ss:Type="String">';
        headerRow += colName + '</ss:Data>\n';
        headerRow += '  </ss:Cell>\n';        
    }
    headerRow += '</ss:Row>\n';    
    return'<?xml version="1.0"?>\n' +
           '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">\n' +
           '<ss:Worksheet ss:Name="Sheet1">\n' +
           '<ss:Table>\n\n' + headerRow;
};

emitXmlFooter = function() {
    return'\n</ss:Table>\n' +
           '</ss:Worksheet>\n' +
           '</ss:Workbook>\n';
};

jsonToSsXml = function (jsonObject) {
    var row;
    var col;
    var xml;
    var data = typeof jsonObject != "object" 
             ? JSON.parse(jsonObject) 
             : jsonObject;

    xml = emitXmlHeader();

    for (row = 0; row < data.length; row++) {
        xml += '<ss:Row>\n';

        for (col in data[row]) {
            xml += '  <ss:Cell>\n';
            xml += '    <ss:Data ss:Type="' + testTypes[col]  + '">';
            xml += data[row][col] + '</ss:Data>\n';
            xml += '  </ss:Cell>\n';
        }

        xml += '</ss:Row>\n';
    }

    xml += emitXmlFooter();
    return xml;  
};

console.log(jsonToSsXml(testJson));

This generates the XML document below. If this XML is saved in a file named test.xls, Excel should recognize this and open it with the proper encoding.

<?xml version="1.0"?><ss:Workbookxmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"><ss:Worksheetss:Name="Sheet1"><ss:Table><ss:Row><ss:Cell><ss:Datass:Type="String">name</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">city</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">country</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">birthdate</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">amount</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell><ss:Datass:Type="String">Tony Peña</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">New York</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">United States</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">1978-03-15</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="Number">42</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell><ss:Datass:Type="String">Ζαλώνης Thessaloniki</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">Athens</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">Greece</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="String">1987-11-23</ss:Data></ss:Cell><ss:Cell><ss:Datass:Type="Number">42</ss:Data></ss:Cell></ss:Row></ss:Table></ss:Worksheet></ss:Workbook>

I must admit, however, my strong inclination would be to do this server-side if possible. I've used the Python library openpyxl to do this in the past and it is fairly simple. Most server-side languages have a library that generates Excel files and they should provide much better constructs than string concatenation.

Anyway, see this MSDN blog for the basics. And this StackOverflow article for some pros/cons of various other options.

Solution 2:

To make excel read a Unicode CSV file you have to add a Byte Order Mark as the very first string in the csv. This can be accomplished via JavaScript through adding the following line in your code:

line="\ufeff"+line