Javascript to csv export encoding issue

Question

I need to export javascript array to excel file and download it I'm doing it in this code. data is a javascript object array.

var csvContent = "data:text/csv;charset=utf-8,";
data.forEach(function(dataMember, index)
{
    dataString = dataMember.join(",");
    csvContent += index < data.length ? dataString+ "\n" : dataString;
}); 

var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download", "upload_data" + (new Date()).getTime() + ".csv");
link.click();

All this stuff works fine till I have string properties that have non-english characters, like spanish, arabic or hebrew. How can I make an export with all this non-ASCII values?

Answer 1

You should add the UTF-8 BOM at the start of the text, like:

var csvContent = "data:text/csv;charset=utf-8,%EF%BB%BF" + encodeURI(csvContent);

It worked for me with Excel 2013.

Demo Fiddle

Answer 2

You can add the BOM at first, use this code and try

var BOM = "\uFEFF"; 
var csvContent = BOM + csvContent;

and then crate the file headers with the data: "text/csv;charset=utf-8"

Answer 3

Excel is really bad at detecting encoding, especially Excel on OSX.

The best solution would be to encode your CSV in the default Excel encoding: windows-1252 (also called ANSI, which is basically a subset of ISO-8859-1).

I put a complete example of how to do that at: https://github.com/b4stien/js-csv-encoding.

The 2 main parts are stringencoding (to encode the content of your CSV in windows-1252) and FileSaver.js (to download the generated Blob).

It looks like:

var csvContent = 'éà; ça; 12\nà@€; çï; 13',
    textEncoder = new TextEncoder('windows-1252');


var csvContentEncoded = textEncoder.encode([csvContent]);
var blob = new Blob([csvContentEncoded], {type: 'text/csv;charset=windows-1252;'});
saveAs(blob, 'some-data.csv');

Answer 4

Option 1

use iconv-lite library and encode your output to ascii before send it back to the user. Example:

var iconv = require('iconv-lite');
buf = iconv.encode(str, 'win1255'); // return buffer with windows-1255 encoding

Option 2

Write on the head of the file the BOM header of UTF-8 encoding. Example:

res.header('Content-type', 'text/csv; charset=utf-8');
res.header('Content-disposition', 'attachment; filename=excel.csv'); 
res.write(Buffer.from('EFBBBF', 'hex')); // BOM header

// rest of your code

Option 3

Use base64 url format like data:text/csv;base64,77u/Zm9vLGJhcg0KYWFhLGJiYg==. This method will work on client-side also (IE10+, FF, Chrome, Opera, Safari).

For example:

window.location = "data:text/csv;base64,77u/" + btoa("foo,bar\r\naaa,bbb");

Answer 5

It is not necessary to use the encodeURIComponent method and glue the data string snippets. Just glue the BOM character in front of the string.

const data = 'öäüÖÄÜ';
const BOM = '\uFEFF';
const blob = new Blob([BOM + data], { type: 'text/csv;charset=utf-8' });

const url = window.URL.createObjectURL(blob);
const linkElem = document.createElement('a');
linkElem.href = url;
linkElem.click();

Answer 6

somehow found Tab-Separated-CSV with utf-16le encoding with BOM works on WIN/MAC Excel

followed b4stien's answer but make a little difference to archive:

var csvContent = 'éà; ça; 12\nà@€; çï; 13',
    textEncoder = new TextEncoder('utf-16le');
var csvContentEncoded = textEncoder.encode([csvContent]);
var bom = new Uint8Array([0xFF, 0xFE]);
var out = new Uint8Array( bom.byteLength + csvContentEncoded.byteLength );
out.set( bom , 0 );
out.set( csvContentEncoded, bom.byteLength );
var blob = new Blob([out]);
saveAs(blob, 'some-data.csv');

with Linux /usr/bin/file tests:

Little-endian UTF-16 Unicode text, with very long lines, with CRLF line terminators

Answer 7

 data=`"red","मुकेश"`
 var processdata = "data:text/csv;charset=utf-8,%EF%BB%BF" + encodeURIComponent(data);

Answer 8

I've been able to solve my issue with the help of https://stackoverflow.com/a/27975629/5228251 answer

const json2csv = require('json2csv');
const csvExport = (req, res) => {
    var csvContent = json2csv({ data, fields })

    res.setHeader('Content-Type', 'text/csv')
    // just prepend the '\ufeff' to your csv string value
    return res.status(200).send('\ufeff' + csvContent)
}

Answer 9

B4stien, thank you to you for your answer! After testing several solutions based on charset "utf8", encoding windows-1252 is the only solution that allowed me to keep my accent in Excel 365!

Manetsus, the b4stien's answer and his link were very usefull for my case: i have to export french and german data into csv file: no solution based on "utf8" has worked... Only his solution which use an "ANSI" (window-1252) encoder...

I give his code sample, and you can download the depending encoding-indexes.js, encoding.js and FileSaver.js from the link...

    <!doctype html>
    <html>

    <head>
        <meta charset="utf-8">
        <script type="text/javascript" src="encoding-indexes.js"></script>
        <script type="text/javascript" src="encoding.js"></script>
        <script type="text/javascript" src="FileSaver.js"></script>
    </head>

    <body>
        <a href="#" id="download-csv">Click me to download a valid CSV !</a>

        <script type="text/javascript">
            var csvContent = 'éà; ça; 12\nà@€; çï; 13',
                textEncoder = new CustomTextEncoder('windows-1252', {NONSTANDARD_allowLegacyEncoding: true}),
                fileName = 'some-data.csv';

            var a = document.getElementById('download-csv');
            a.addEventListener('click', function(e) {
                var csvContentEncoded = textEncoder.encode([csvContent]);
                var blob = new Blob([csvContentEncoded], {type: 'text/csv;charset=windows-1252;'});
                saveAs(blob, fileName);
                e.preventDefault();
            });
        </script>
    </body>

    </html>

Nevertheless, as Excel is relatively open in the support of languages and formats, I do not exclude that UTF8 is not supported in my development environment because of the way it is installed ...

Note: I test it with Firefox, Chrome and IE 11 on windows 7, with Excel 365...

Answer 10

To export CSV containing multibyte characters and make it readable on text editor and Excel in multiple OS platforms (Windows, Linux, MacOS), the following rules should be applied:

1. Separate the field with tab instead of comma (so that Excel on MacOS can properly display the generated CSV file)
2. Encode the string / content with UTF-16 little endian (UTF16-LE) instead of UTF-8
3. Add byte order mark (BOM) 0xFEFF as specified in RFC2781 section 3.2 at the beginning of the serialized stream to explicitly provide "signature" of content encoded with UTF16-LE

Further elaboration, use cases and sample code with NodeJS can be seen in this article.

Answer 11

This is the solution that worked for me

Reference: Export CSV file -encoded

var csv = 'السلام عليكم, Student name\n';
        
var hiddenElement = document.createElement('a');
        //workaround to support encoding
hiddenElement.href = **'data:text/csv;charset=utf-8,%EF%BB%BF'+encodeURIComponent(csv);**

hiddenElement.download = 'Course Students Progress - ' + new Date().toLocaleString() + '.csv';
hiddenElement.click();