Javascript to csv export encoding issue
JavascriptCsvExport to-CsvJavascript Problem Overview
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?
Javascript Solutions
Solution 1 - Javascript
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.
Solution 2 - Javascript
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"
Solution 3 - Javascript
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');
Solution 4 - Javascript
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");
Solution 5 - Javascript
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();
Solution 6 - Javascript
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
Solution 7 - Javascript
data=`"red","मुकेश"`
var processdata = "data:text/csv;charset=utf-8,%EF%BB%BF" + encodeURIComponent(data);
Solution 8 - Javascript
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...
Solution 9 - Javascript
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:
- Separate the field with tab instead of comma (so that Excel on MacOS can properly display the generated CSV file)
- Encode the string / content with UTF-16 little endian (UTF16-LE) instead of UTF-8
- 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.
Solution 10 - Javascript
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();