CODEWITHSUNDEEP
javascriptcsvexport-to-csv
Boltosaurus
Boltosaurus

Reputation: 2216

Javascript to csv export encoding issue

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?

Upvotes: 88

Views: 163229

Answers (10)

Marcelo Lujan
Marcelo Lujan

Reputation: 2232

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"

Upvotes: 125

btx
btx

Reputation: 2487

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

Upvotes: 14

Ali Nowrouzi
Ali Nowrouzi

Reputation: 79

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)
}

Upvotes: 2

Sarah Tammam
Sarah Tammam

Reputation: 132

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

Upvotes: 0

Gergő Nagy
Gergő Nagy

Reputation: 1620

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

Upvotes: 141

Mukesh Kumar
Mukesh Kumar

Reputation: 115

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

Upvotes: 2

Moshe Simantov
Moshe Simantov

Reputation: 4493

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

Upvotes: 8

Didier68
Didier68

Reputation: 1325

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

Upvotes: 0

gjchen
gjchen

Reputation: 21

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

Upvotes: 2

b4stien
b4stien

Reputation: 1850

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

Upvotes: 18

Related Questions