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 http://wiki.scn.sap.com/wiki/display/ABAP/CSV+tests+of+encoding+and+column+separator?original_fqdn=wiki.sdn.sap.com
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.