Export JSON to CSV or Excel with UTF-8 (e.g. Greek) encoding using JavaScript

Question

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 characters looks like junk.

Here is my existing code:

function downloadJsonToCsv(jsonObject) {
    var array = typeof jsonObject != "object" ? JSON.parse(jsonObject) : jsonObject;

    if (array == null) {
        return; // No data found on the jsonObject
    }

    var str = "";

    for (var i = 0; i < array.length; i++) {
        var line = "";

        for (var index in array[i]) {
            line += array[i][index] + ";"; // Set delimiter
        }

        // Here is an example where you would wrap the values in double quotes
        // for (var index in array[i]) {
        //    line += '"' + array[i][index] + '",';
        // }

        line.slice(0,line.Length-1); 

        str += line + "\r\n";
    }

    window.open("data:text/csv;charset=utf-8," + encodeURI(str));
}

I have two questions.

How can export this CSV file with correct Greek chars?
How can I export this data in Excel format and not in CSV format?

Try encodeURIComponent() instead of encodeURI(). Csv can be processed by excel, so I don't think you have to change something — Alkis Kalogeris, Dec 23 '13 at 11:33
If the above doesn't work, please provide some sample of your data so I can check it on my own. — Alkis Kalogeris, Dec 23 '13 at 11:37
If you're trying to load the CSV file into Excel, please note that Excel is not very good at loading CSV files with non-Ascii character-sets. Make sure you're saving it with UTF-8 encoding, and make sure you're adding a UTF-8 BOM character to the start of the file. This should help. See also loads of other questions around here asking about [CSV+utf8+Excel](http://stackoverflow.com/search?q=utf8+csv+excel). It's a common issue. — Spudley, Dec 23 '13 at 12:14
When I open my downloaded csv with notepad it looks ok. And if save it like utf-8 csv file then looks ok in excel also. But when open excel without saving from notepad then it is not ok. Can I export in utf-8 via javascript? — A. Zalonis, Dec 23 '13 at 15:24
A line from csv file: Υπ. Ζαλώνης;7778;16918;20.00;Thessaloniki - Night, 02/12/2013, (transition);Ζαλώνης;Thessaloniki - Θεσσαλονίκη;Κανονικό;No; — A. Zalonis, Dec 23 '13 at 15:30
See: http://stackoverflow.com/questions/155097/ for an excellent description of the BOM. Note that if you are stuck if you are using Mac Excel 2011 (or earlier), it disregards BOM as best as I can tell. — stvsmth, Jan 04 '14 at 00:34

score 43 · Accepted Answer · edited May 23 '17 at 11:52

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:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Worksheet ss:Name="Sheet1">
<ss:Table>

<ss:Row>
  <ss:Cell>
    <ss:Data ss:Type="String">name</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">city</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">country</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">birthdate</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">amount</ss:Data>
  </ss:Cell>
</ss:Row>

<ss:Row>
  <ss:Cell>
    <ss:Data ss:Type="String">Tony Peña</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">New York</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">United States</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">1978-03-15</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="Number">42</ss:Data>
  </ss:Cell>
</ss:Row>
<ss:Row>
  <ss:Cell>
    <ss:Data ss:Type="String">Ζαλώνης Thessaloniki</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">Athens</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">Greece</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">1987-11-23</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss: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.

For IE 11 you need to do window.navigator.msSaveOrOpenBlob(blob,filename); for the file to download. Updated JSFiddle here http://jsfiddle.net/kmqz9/223/ — Vishnoo Rath, Jan 05 '15 at 13:16
It's saved my time and how to write column names for this generated excel sheet — Learner, Feb 10 '15 at 04:01
How to add the Header row for each column here(name, city, country, date) — user3263194, Mar 12 '15 at 09:19
@user3263194 : Adding the header row is pretty trivial ... you should try it yourself; but I have updated the answer. — stvsmth, Mar 14 '15 at 16:49
But if I want to add my own custom headers as shown below, then how can I add it? headers = { "Customer Name": "String", "City": "String", "Country": "String", "Date of Birth": "String", "Amount": "Number" }; — user3263194, Mar 20 '15 at 10:10
@stv I tryed your fiddle and when the downloaded file was opened in Excel, I got this warning: 'The file format and extension of 'test(4).xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?' — ivayloc, Oct 14 '15 at 09:43
Thank you so much, It is really the solution I've been looking for years. — Yourim Yi, May 27 '17 at 08:01

score 6 · Answer 2 · answered Jan 04 '14 at 13:14

6

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

answered Jan 04 '14 at 13:14

Diaa

869
3
7
19

Export JSON to CSV or Excel with UTF-8 (e.g. Greek) encoding using JavaScript

2 Answers2

Linked