Search code examples
node.jsexcelamazon-s3xlsxsheetjs

Special character issue in Excel for CSV file, Sheetjs prepend BOM for utf8 CSV files

I am using https://www.npmjs.com/package/xlsx library to create CSV.

This is the final code to write CSV code

let workbook: WorkBook = this.workBookCreator.getWorkbook(workBookName);

return this.xlsxModule.write(workbook, {
     bookType: format,
     type: "buffer"
});

And that will be uploaded to S3 bucket.

To download file

window.location.href = fileLocation;

Where fileLocation is the S3 pre-signed URL with a GET request.

When the downloaded file opens in Microsoft Excel, Special characters are messed up due to UTF-8 charset are not being recognized by EXCEL.

Can anyone please help me, how can I set charset UTF-8 while creating CSV to support all special characters to Excel?

I have tried the below solutions so far:

  1. Tried to change the buffer type of XLSX
  2. Added IconV & IconV-lite for buffer conversion
  3. Changed the Content-type of the S3 bucket file.
  4. Tried to change buffer type to string

I know we can prepend BOM to CSV file while writing. But couldn't find from where I can prepend.

Solution

  • So, Finally, I found the solution (Tried too many google search)

    I used iconv-lite npm package

    And here is code:

    const txt = '\uFEFF'+ iconV.decode(CSVString, 'utf8');
CSVString = iconV.encode(txt, 'utf8');

    Hope, I can save someone's day!