How to make Microsoft Excel load Unicode characters correctly when open double clicking CSV file

Question

Note: This issue is specific to Microsoft Excel. It works fine on LibreOffice without any problems.

I am developing a React application where I need to let the user download some table data upon a button click. On the download, I generate a UTF-16 encoded CSV file.

export const stringToUTF16Blob = (content: string): Blob => {
  // Initialize array with BOM for UTF-16-LE
  const charCodes = [0xff, 0xfe];

  // Convert string to UTF-16-LE in one step
  for (let i = 0; i < content.length; i++) {
    const charCode = content.charCodeAt(i);
    // Push the low byte first, then high byte
    charCodes.push(charCode % 256, Math.floor(charCode / 256) % 256);
  }

  return new Blob([new Uint8Array(charCodes)], { type: 'text/csv;charset=utf-16le;' });
};

When I generate this file. It can render the Unicode characters when we do a double click. But it doesn't split into lines correctly.

I can add the separator file string to the file:

const separatorString ='"sep=,"';

(The content value that I'm passing to the stringToUTF16Blob function in this code consists of separatorString + headers + content.)

Once I add it, it can split by comma correctly. But it doesn't consider Unicode characters. I think it just renders its ASCII representation.

Here is how my CSV file looks like when opened with text editor (you can see the encoding as UTF-16LE):

I tried with utf-8 with no luck, found out that Excel support is better when we use utf-16. indeed it made the Unicode charactors to work without the separator string.

Answer 1

This isn't specific to Excel, and not about Unicode either. CSV isn't a well defined format. Delimited files appeared in the 1970s but the first time there was any kind of "standard", was RFC 4180 in 2005. And even that says nothing about number or date formats. The words don't even exist in the RFC.

Why you can't make assumptions

Different countries use different locales, with most not using . as the decimal separator, while Canada uses both . and ,. Never mind date formats. If you look at Wikipedia's world map in the Decimal separator page you'll realize that most countries don't use . while half of the people do (India and China use .. Also the US but that's only 350M).

When you double-click on a text file with the .csv extension you're actually importing a text file. The application has to decide what settings to use for separators, numbers and dates. Different applications will use different heuristics.

Excel was built to "just work" everywhere, with whatever files users had, and those files are most likely to use the country's formats. Users paid for this and want it to just work. Those formats are taken from the current user's Locale settings, specifically the Decimal, List separator and Date formats.

OpenOffice/LibreOffice on the other hand, didn't even handle Unicode at first (I had some "interesting" experiences in 2001) and decided to use commas. No idea what it does with dates.

Possible Solution: Create an Excel file directly

You can create real XLSX files in JavaScript (and many other languages) without having to install Excel or LibreOffice. An XLSX is a ZIP package containing well-defined XML files. There are many npm packages that can generate XLSX files from data, create pivot tables etc. For example, exceljs has 2M weekly downloads (!).

Based on the docs, you could save a list of rows to an Excel file with something like :

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('My Sheet');

const rows = [
  [5,'Bob',new Date()], // row by array
  {id:6, name: 'Barbara', dob: new Date()}
];

const newRows = sheet.addRows(rows);

And save it either as Excel :

// write to a new buffer
const buffer = await workbook.xlsx.writeBuffer();

or CSV with settings :

// write to a file with European Date-Times
const workbook = new Excel.Workbook();
const options = {
  dateFormat: 'DD/MM/YYYY HH:mm:ss',
  dateUTC: true, // use utc when rendering dates
};
await workbook.csv.writeFile(filename, options);