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.

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

Broder

Try encodeURIComponent() instead of encodeURI(). Csv can be processed by excel, so I don't think you have to change something – Goon

If the above doesn't work, please provide some sample of your data so I can check it on my own. – Goon

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. It's a common issue. – Hanforrd

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? – Broder

I am sorry @alkis but it didn't works – Broder

A line from csv file: Υπ. Ζαλώνης;7778;16918;20.00;Thessaloniki - Night, 02/12/2013, (transition);Ζαλώνης;Thessaloniki - Θεσσαλονίκη;Κανονικό;No; – Broder

See: 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. – Vivica

Answer 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: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.

Vivica

For IE 11 you need to do window.navigator.msSaveOrOpenBlob(blob,filename); for the file to download. Updated JSFiddle here jsfiddle.net/kmqz9/223 – Congratulate

It's saved my time and how to write column names for this generated excel sheet – Cohby

How to add the Header row for each column here(name, city, country, date) – Henshaw

@Henshaw : Adding the header row is pretty trivial ... you should try it yourself; but I have updated the answer. – Vivica

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" }; – Henshaw

@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?' – Nosebleed

Thank you so much, It is really the solution I've been looking for years. – Frendel

Answer 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

Decoration