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?

Purehearted

the first line states utf-8, this is ASCII. maybe if you changed it to 16 this would use unicode maybe? – Koressa

I changed it. same thing( – Purehearted

@Boltosaurus, I created a demo here: jsfiddle.net/8qPur. It looks OK to me : the downloaded file has the special characters encoded correctly. – Manhole

That's strange because I got your download like this: ֳ©, ֳ¨, ֳ¹ – Purehearted

Very detailed answer on CSV encoding here: https://mcmap.net/q/57519/-encoding-issues-for-utf8-csv-file-when-opening-excel-and-textedit – Panek

@BenoitBlanchon it doesn't work. try Hebrew letters such as: אבג – Adenaadenauer

How can we convert it to ANSI encoding? – Jink

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

Rhianna

can you provide a full working example in jsFiddle or something? – Sturrock

@jlarson here you are: link I used Benoit Blanchon's demo. – Hanny

On the Mac I end up with this :( dropbox.com/s/a36t99fvo43xhfe/… – Sturrock

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'}); – Parasitize

Very thank you.more than 1 year i had this problem but can't find correct answer. – Esophagitis

This is the best solution i found in the web since one week! thanks ! – Stenger

is there a way to set the width of each column? – Planetoid

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. – Eby

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"

Beta

THIS! Took forever to find something that would work. Thanks! – Mortarboard

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

Excel is now showing Unicode characters like ář properly. Thanks – Fanatical

Excellent, this worked for me with plain JavaScript! – Mentholated

This did the trick for me: csv = 'data:text/csv;charset=utf-8,\uFEFF' + csvContent; – Schutz

It's creating for me an empty blank line at the beginning, isn't it?, well I'm using the same approach of injecting those boom characters plus the csv content but using Ruby instead – Taking

This worked for me too. Thank you! – Weird

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

Evolute

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. – Heterogamy

Notice that TextEncoder no longer accept any encoding other than utf-8 now. – Sextans

It wont work in the current version that i had to download the version from this link. Thanks for saving it – Jink

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. – Semple

Thank you, @Evolute 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? – Sketchy

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

Vizcacha

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

Lichi

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

I'm using express.js framework. On plane node.js you can use setHeader. – Lichi

I'm not talking about node. I'm using pure client-side javascript) – Purehearted

I added a new option for you - option 3. – Lichi

btoa is not available in older IE. And doesn't work even in Chrome or FF on a mac: jsbin.com/vazazaru/1 – Sturrock

Thanks for the option 2, it works fine. Even if the OP didn't ask for an express solution, it still useful for people coming from google! :) – Gies

es.write(new Buffer('EFBBBF', 'hex')); is no longer in use. – Ewold

Option 3 worked for my problem which is a bit different than OP, but Google still brought me here. I have a javascript created csv that was breaking the output from hashtag characters in a cell. Base64 encoding fixed this for me. – Unbearable

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! – Tema

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

Premed

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 – Watercourse

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) – Watercourse

Answer 7

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

Counterattack

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). – Pharynx

Answer 8

I've been able to solve my issue with the help of https://mcmap.net/q/55249/-adding-utf-8-bom-to-string-blob 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)
}

Grondin

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

Wellspoken

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

Chavaree