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?

• rfcoder89: the first line states utf-8, this is ASCII. maybe if you changed it to 16 this would use unicode maybe?
• Boltosaurus: I changed it. same thing(
• Benoit Blanchon: @Boltosaurus, I created a demo here: jsfiddle.net/8qPur. It looks OK to me : the downloaded file has the special characters encoded correctly.
• Boltosaurus: That's strange because I got your download like this: ֳ©, ֳ¨, ֳ¹
• GregW: Very detailed answer on CSV encoding here: stackoverflow.com/a/22107376/17783

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

• jwl: can you provide a full working example in jsFiddle or something?
• Gergő Nagy: @jlarson here you are: link I used Benoit Blanchon's demo.
• jwl: On the Mac I end up with this :( dropbox.com/s/a36t99fvo43xhfe/…
• Sam Barnum: This worked for me, using FileSaver.js. Instead of URL-encoding, I did this: var blob = new Blob(['\ufeff' + csvString], {type: 'text/csv;charset=utf-8'})
• Sarthak Saxena: This will fail and not print in Csv if it gets special symbols for.eg. "!@#$%^&1234567890.pdf". I am not able to find the solution for this corner case.

Answer 2

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

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

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

• KyleFarris: THIS! Took forever to find something that would work. Thanks!
• skrile: This worked for me when converting my stuff to a blob and then using the anchor tag click hack to trigger download: var downloadLink = document.createElement("a"); downloadLink.download = fileNameToSaveAs; downloadLink.href = window.URL.createObjectURL(textFileAsBlob); downloadLink.onclick = function (e) { document.body.removeChild(e.target); }; downloadLink.style.display = "none"; document.body.appendChild(downloadLink); downloadLink.click();
• Karthik: Excel is now showing Unicode characters like ář properly. Thanks
• Thread Pitt: Excellent, this worked for me with plain JavaScript!
• Staafsak: This did the trick for me: csv = 'data:text/csv;charset=utf-8,\uFEFF' + csvContent

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');

• Greg Venech: Amazing, thanks! Went through reams of SO pages and docs looking for something to solve an issue with an excel destroying CSVs after opening and saving them back out on OSX. This was the only thing that worked.
• tsh: Notice that TextEncoder no longer accept any encoding other than utf-8 now.
• Shift 'n Tab: It wont work in the current version that i had to download the version from this link. Thanks for saving it
• James Hill: I ran into a similar issue - InDesign's DataMerge obstinately refused to show my special characters, regardless of whether I attempted UTF-8, UTF-16, UTF-16LE, tabs, commas, anything. Using the files in b4stien's repo above, and adapting his example, it worked perfectly! Worth noting that in my case, I only needed to target Chrome on Windows.
• Roshan: Thank you, @b4stien I am looking to find out an encoding for the uploaded CSV file. The users might upload in different languages. How can I find out that? I tried many solutions but nothing seems to work for me. Any help please?

Answer 4

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

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 5

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");

• Boltosaurus: Hey, thanks for your response. Can you please give a full example of option 2? What exactly is .header() method? What exactly is res object?
• Moshe Simantov: I'm using express.js framework. On plane node.js you can use setHeader.
• Boltosaurus: I'm not talking about node. I'm using pure client-side javascript)
• Habibul Hasan: es.write(new Buffer('EFBBBF', 'hex')); is no longer in use.
• langeleppel: Thanks @MosheSimantov almost 9 years later, you saved my day. The ,77u/ after the base64 solved my Python Databricks streaming export. This was even not mentioned on the BOM WikiPedia pages!

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

• Emmanuel Touzery: unfortunately this won't work anymore => "Note: Prior to Firefox 48 and Chrome 53, an encoding type label was accepted as a paramer to the TextEncoder object, since then both browers have removed support for any encoder type other than utf-8, to match the spec. Any type label passed into the TextEncoder constructor will now be ignored and a utf-8 TextEncoder will be created." developer.mozilla.org/en-US/docs/Web/API/TextEncoder
• Emmanuel Touzery: I've add success with const blob = new Blob([new Uint8Array(iconv_lite.encode(csvContent, "utf16-le", {addBOM: true}))]); and then saveAs (from file-saver)

Answer 7

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

• Xyz: Please, fell free to expand on your answer. Specifically, try to explain why it solves the question (better than the already massively up-voted answers above).

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

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();