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

by

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 seperator. For instance in danish, the default seperator is ;. If you output tab or comma seperated text then it does not detect the seperator and in other locales if you seperate with semi-colon it doesn’t load. You can test this by changing the locae 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 UTF16 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 seperators 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 work around that tells excel to take both the sep and the encoding.

More Related Contents:

  1. Is it possible to force Excel recognize UTF-8 CSV files automatically?
  2. Stop Excel from automatically converting certain text values to dates
  3. Microsoft Excel mangles Diacritics in .csv files?
  4. Excel to CSV with UTF8 encoding [closed]
  5. Excel CSV – Number cell format
  6. Generating CSV file for Excel, how to have a newline inside a value
  7. Export hashtable to CSV with the key as the column heading
  8. Save each sheet in a workbook to separate CSV files
  9. Export each sheet to a separate csv file
  10. How to correctly display .csv files within Excel 2013?
  11. Convert xlsx file to csv using batch
  12. Importing CSV with line breaks in Excel 2007
  13. How do I use awk under cygwin to print fields from an excel spreadsheet?
  14. converting an Excel (xls) file to a comma separated (csv) file without the GUI
  15. How to save excel columns with long numbers into csv?
  16. Can Excel interpret the URLs in my CSV as hyperlinks?
  17. How to get MS Excel to list files in a folder with their metadata (in particular, the tags created for the files)?
  18. Excel – opening file stuck on 100%, until VBA code finishes
  19. VLOOKUP with multiple criteria returning values in one cell
  20. VBA check if file exists
  21. Trying to use variables as values vba to sql
  22. Extracting the top 5 maximum values in excel
  23. Count unique values in a column in Excel
  24. Refresh Excel VBA Function Results
  25. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  26. Concatenate column headers if value in rows below is non-blank
  27. Update excel sheet based on outlook mail [closed]
  28. Using the value in a cell as a cell reference in a formula?
  29. Excel, save and close after run
  30. Excel: How to gather unique values in one column that are associated with duplicates in another column?

Leave a Comment