Generate CSV for Excel via JavaScript with Unicode Characters

Question

I am trying to generate a CSV file on the client side using javascript. I've followed the answer on this stackoverflow question. I have unicode characters in the content (Hebrew characters in my case).

The file generation succeeds, however when I open the file in Excel - all the unicode characters are shown as funny characters. ASCII characters (English and numbers) are presented well.

The weird thing is that if I open the file in notepad, the unicode characters show well. So I guess this has something to do with Excel and the way I'm saving the file.

Any ideas?

Answer 1

Following Jack Cole's comment and this question, what fixed my problem was adding a BOM prefix (\uFEFF) to the beginning of the file.

This is the working code:

var csvContent = "...csv content...";
var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", "data:text/csv;charset=utf-8,\uFEFF" + encodedUri);
link.setAttribute("download","report.csv");
link.click();

Answer 2

The suggested solution didn't work with all browsers, but I found a way to get it working in all browsers (Chrome, Firefox, IE11 and even Edge, ... don't know about IE9-10 since I don't have access to them anymore).

I have to use an external library to encode encoding.js and it works amazingly well with unicode (I can see my unicorn emoji in my CSV export in Excel).

So here's the code

data = new TextEncoder('utf-16be').encode(csvContent);

// create a Blob object for the download
const blob = new Blob(['\uFEFF', data], {
  type: 'text/csv;charset=utf-8';
});

// when using IE/Edge, then use different download call
if (typeof navigator.msSaveOrOpenBlob === 'function') {
  navigator.msSaveOrOpenBlob(blob, filename);
} else {
  // this trick will generate a temp <a /> tag that you can trigger a hidden click for it to start downloading
  const link = document.createElement('a');
  const csvUrl = URL.createObjectURL(blob);

  link.textContent = 'download';
  link.href = csvUrl;
  link.setAttribute('download', filename);

  // set the visibility hidden so there is no effect on your web-layout
  link.style.visibility = 'hidden';

  // this part will append the anchor tag and remove it after automatic click
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
}

and that's it, it works in IE / Edge / Chrome / Firefox