sep=”;” statement breaks utf8 BOM in CSV file which is generated by XSL

You are right, there is no way in Excel 2007 to get it load both the encoding and the seperator correctly across different locales when someone double clicks a CSV file.

It seems like when you specify sep= after the BOM it forgets the BOM has told it that it is UTF-8.

You have to specify the BOM because in certain locales Excel does not detect the separator. For instance in Danish, the default separator is ;. If you output tab or comma-separated text then it does not detect the separator and in other locales, if you separate with semi-colon it doesn’t load. You can test this by changing the locale format in Windows settings – Excel then picks this up.

From this question:
Is it possible to force Excel recognize UTF-8 CSV files automatically?

and the answers it seems the only way is to use UTF-16 LE encoding with BOM.

Note also that as per
it seems that if you use utf16-le with tab separators then it works.

I’ve wondered if Excel reads sep=; and then re-calls the method to get the CSV text and loses the BOM – I’ve tried giving incorrect text and I can’t find any workaround that tells Excel to take both the sep and the encoding.

Leave a Comment