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

1
votes

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.

1
node.jsexcelamazon-s3xlsxsheetjs
What problems did you run into when you tried the various methods recommended in this forum for similar questions? Off the top of my head, two methods include writing a BOM to the file; importing into Excel, possibly using Power Query. – Ron Rosenfeld
@RonRosenfeld I have updated the Question with my attempts so far. Could you please guide me to the link for Power Query? Or can you please elaborate more? – Hardik Shah
PQ is available in Excel 2010+ (as a free MS add-in in 2010-2013, built-in in 2016+). When you import using PQ, there will be an option to set the File Origin. IIRC, 65001 is for UTF8. – Ron Rosenfeld
That is I don't want to force users to do manual steps. – Hardik Shah
Then you will either have to set it up as a refreshable query, and convince your users to import and not open the file; or you will have to figure out how to add the BOM to the file when you create it. To do the latter, I suggest you do an internet search. Sheetjs prepend bom for utf8 csv files returned about 10,000 results on Google. – Ron Rosenfeld

1 Answers

1
votes

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!