39

I need to export javascript array to CSV file and download it. I did it but 'ı,ü,ö,ğ,ş' this characters looks like 'ı ü ö ÄŸ ÅŸ' in the CSV file. I have tried many solutions recommended on this site but didn't work for me.

I added my code snippet, Can anyone solve this problem?

var csvString = 'ı,ü,ö,ğ,ş';

var a = window.document.createElement('a');
a.setAttribute('href', 'data:text/csv; charset=utf-8,' + encodeURIComponent(csvString));
a.setAttribute('download', 'example.csv');
a.click();
Can Bozdemir
  • 423
  • 1
  • 5
  • 8
  • You probably know that this solution will not work in `Internet Explorer` since this browser does not support using `data`-URIs this way. But at least you should it make working using `Firefox`. `Firefox` will not accept `click` events for elements which are not appended to the DOM. So you need appending your `A` element to the DOM and not only creating it. See example in my answer. But also read my conclusion. Using `CSV` this way is not a solution but part of many problems. – Axel Richter Feb 26 '17 at 10:14

1 Answers1

109

This depends on what program is opening the example.csv file. Using a text editor, the encoding will be UTF-8 and the characters will not be malformed. But using Excel the default encoding for CSV is ANSI and not UTF-8. So without forcing Excel using not ANSI but UTF-8 as the encoding, the characters will be malformed.

Excel can be forced using UTF-8 for CSV with putting a BOM (Byte Order Mark) as first characters in the file. The default BOM for UTF-8 is the byte sequence 0xEF,0xBB,0xBF. So one could think simply putting "\xEF\xBB\xBF" as first bytes to the string will be the solution. But surely that would be too simple, wouldn't it? ;-) The problem with this is how to force JavaScript to not taking those bytes as characters. The "solution" is using a "universal BOM" "\uFEFF" as mentioned in Special Characters (JavaScript).

Example:

var csvString = 'ı,ü,ü,ğ,ş,#Hashtag,ä,ö';
var universalBOM = "\uFEFF";
var a = window.document.createElement('a');
a.setAttribute('href', 'data:text/csv; charset=utf-8,' + encodeURIComponent(universalBOM+csvString));
a.setAttribute('download', 'example.csv');
window.document.body.appendChild(a);
a.click();

See also Adding UTF-8 BOM to string/Blob.

Using this, the encoding will be correct. But nevertheless, this only works properly if comma is the default list separator in your Windows locale settings. If not, if for example semicolon is the default list separator in your Windows locale settings, then all content will be in first column without splitting it by comma. Then you have to use semicolon as delimiter in the CSV also. But this is another problem and leads to the conclusion not using CSV at all but using libraries which can directly creating Excel files (*.xls or *.xlsx).

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • I using **WPS Office Spreadsheet** to open CSV file and this program still don't showing characters properly. I tried to open CSV file with **Microsoft Office Excel** and worked properly. So i understood my problem. Thanks for help. – Can Bozdemir Feb 26 '17 at 22:31
  • This works well for getting Excel to open the file with the UTF-8 encoding but when I hit save (Ctrl+S) it saves it as a TSV text file and not as a CSV utf-8 file. Have you encountered this issue at all? – Dylan Jan 25 '18 at 00:43
  • 2
    @Dylan: Yes, but this is another question and it is a Microsoft fault within the Excel application that it is not able saving CSV UTF-8 encoded. See also the last sentence in my answer. – Axel Richter Jan 25 '18 at 06:38
  • 1
    @AxelRichter - I've used your solution on my own project, but the data I'm exporting is of Instagram captions, which include hashtags (#) - the UTF-8 BOM solution you provided is now cutting off the strings once it hits the first # character - any suggestions for getting the BOM to ignore the #? Thanks! – tristanojbacon Feb 18 '20 at 13:09
  • 1
    @tristanojbacon: Sorry, cannot reproducing. Works for me even if `#` character is in data. – Axel Richter Feb 18 '20 at 13:25
  • The best answer is https://stackoverflow.com/questions/14964035/how-to-export-javascript-array-info-to-csv-on-client-side#52414433 – bujals Feb 26 '20 at 11:10