Microsoft Excel mangles Diacritics in csv files

Question

I am programmatically exporting data  using PHP 5 2  into a  csv test file  Example data    Num  ro 1  note the accented e   The data is utf-8  no prepended BOM    When I open this file in MS Excel is displays as Num    ro 1   I am able to open this in a text editor  UltraEdit  which displays it correctly   UE reports the character is decimal 233   How can I export text data in a  csv file so that MS Excel will correctly render it  preferably without forcing the use of the import wizard  or non-default wizard settings

User · Answer

Echo UTF-8 BOM before outputing CSV data. This fixes all character issues in Windows but doesnt work for Mac.

echo "\xEF\xBB\xBF";

It works for me because I need to generate a file which will be used on Windows PCs only.

User · Answer

Excel 2007 properly reads UTF-8 with BOM  EF BB BF  encoded csv   Excel 2003  and maybe earlier  reads UTF-16LE with BOM  FF FE   but with TABs instead of commas or semicolons

User · Answer

Another solution I found was just to encode the result as Windows Code Page 1252  Windows-1252 or CP1252   This would be done  for example by setting Content-Type appropriately to something like text csv  charset Windows-1252 and setting the character encoding of the response stream similarly

User · Answer

Below is the PHP code I use in my project when sending Microsoft Excel to user              Export an array as downladable Excel CSV       param array    header       param array    data       param string   filename         function toCSV  header   data   filename         sep      t        eol      n        csv     count  header         implode      sep       header       eol           foreach  data as  line           csv         implode      sep       line       eol             encoded csv   mb convert encoding  csv   UTF-16LE    UTF-8        header  Content-Description  File Transfer        header  Content-Type  application vnd ms-excel        header  Content-Disposition  attachment  filename     filename   csv         header  Content-Transfer-Encoding  binary        header  Expires  0        header  Cache-Control  must-revalidate  post-check 0  pre-check 0        header  Pragma  public        header  Content-Length     strlen  encoded csv        echo chr 255    chr 254     encoded csv      exit        UPDATED  Filename improvement and BUG fix correct length calculation  Thanks to TRiG and  ivanhoe011

User · Answer

select UTF-8 enconding when importing  if you use Office 2007 this is where you chose it   right after you open the file

User · Answer

The answer for all combinations of Excel versions  2003   2007  and file types  Most other answers here concern their Excel version only and will not necessarily help you  because their answer just might not be true for your version of Excel   For example  adding the BOM character introduces problems with automatic column separator recognition  but not with every Excel version   There are 3 variables that determines if it works in most Excel versions    Encoding BOM character presence Cell separator   Somebody stoic at SAP tried every combination and reported the outcome  End result  Use UTF16le with BOM and tab character as separator to have it work in most Excel versions   You don t believe me  I wouldn t either  but read here and weep  http   wiki sdn sap com wiki display ABAP CSV tests of encoding and column separator

User · Answer

Prepending a BOM   uFEFF  worked for me  Excel 2007   in that Excel recognised the file as UTF-8  Otherwise  saving it and using the import wizard works  but is less ideal

User · Answer

Writing a BOM to the output CSV file actually did work for me in Django   def handlePersoonListExport request         Retrieve a query set              template   loader get template  export csv       context   Context            data   query set              response   HttpResponse       response  Content-Disposition      attachment  filename export csv      response  Content-Type      text csv  charset utf-8      response write   xEF xBB xBF       response write template render context        return response   For more info http   crashcoursing blogspot com 2011 05 exporting-csv-with-special-characters html Thanks guys

User · Answer

This is just of a question of character encodings   It looks like you re exporting your data as UTF-8     in UTF-8 is the two-byte sequence 0xC3 0xA9  which when interpreted in Windows-1252 is        When you import your data into Excel  make sure to tell it that the character encoding you re using is UTF-8

User · Answer

UTF-8 doesn t work for me in office 2007 without any service pack  with or without BOM  U ffef or 0xEF 0xBB 0xBF   neither works  installing sp3 makes UTF-8 work when 0xEF 0xBB 0xBF BOM is prepended   UTF-16 works when encoding in python using  utf-16-le  with a 0xff 0xef BOM prepended  and using tab as seperator  I had to manually write out the BOM  and then use  utf-16-le  rather then  utf-16   otherwise each encode   prepended the BOM to every row written out which appeared as garbage on the first column of the second line and after   can t tell whether UTF-16 would work without any sp installed  since  I can t go back now  sigh  This is on windows  dunno about office for MAC   for both working cases  the import works when launching a download directly from the browser and the text import wizard doesn t intervence  it works like you would expect

User · Answer

You can save an html file with the extension  xls  and accents will work  pre 2007 at least    Example  save this  using Save As utf8 in Notepad  as test xls     lt html gt   lt meta http-equiv  Content-Type  content  text html  charset  utf-8    gt   lt table gt   lt tr gt     lt th gt id lt  th gt     lt th gt name lt  th gt   lt  tr gt   lt tr gt    lt td gt 4 lt  td gt    lt td gt H  l  ne lt  td gt   lt  tr gt   lt  table gt   lt  html gt

User · Answer

I ve also noticed that the question was  answered  some time ago but I don t understand the stories that say you can t open a utf8-encoded csv file successfully in Excel without using the text wizard   My reproducible experience  Type Old MacDonald had a farm            into Notepad  hit Enter  then Save As  using the UTF-8 option    Using Python to show what s actually in there     gt  gt  gt  open  oldmac csv    rb   read     xef xbb xbfOld MacDonald had a farm  xc3 x88 xc3 x8c xc3 x89 xc3 x8d xc3 x98 r n   gt  gt  gt   Z   Good  Notepad has put a BOM at the front   Now go into Windows Explorer  double click on the file name  or right click and use  Open with       and up pops Excel  2003  with display as expected

User · Answer

Note that including the UTF-8 BOM is not necessarily a good idea - Mac versions of Excel ignore it and will actually display the BOM as ASCII    three nasty characters at the start of the first field in your spreadsheet

User · Answer

Check the encoding in which you are generating the file  to make excel display the file correctly you must use the system default codepage   Wich language are you using  if it s  Net you only need to use Encoding Default while generating the file

User · Answer

I ve found a way to solve the problem  This is a nasty hack but it works  open the doc with Open Office  then save it into any excel format  the resulting  xls or  xlsx will display the accentuated characters

User · Answer

If you have legacy code in vb net like I have  the following code worked for me       Response Clear       Response ClearHeaders       Response ContentType    text csv      Response Expires   0     Response AddHeader  Content-Disposition    attachment  filename export csv        Using sw As StreamWriter   New StreamWriter Context Response OutputStream  System Text Encoding Unicode          sw Write csv          sw Close       End Using     Response End

User · Answer

Prepending a BOM   uFEFF  worked for me  Excel 2007   in that Excel recognised the file as UTF-8  Otherwise  saving it and using the import wizard works  but is less ideal

User · Answer

The CSV format is implemented as ASCII  not unicode  in Excel  thus mangling the diacritics  We experienced the same issue which is how I tracked down that the official CSV standard was defined as being ASCII-based in Excel

User · Answer

open the file csv with notepad   clic on Encode  select convert to UTF-8  not convert to UTF-8 without BOM   Save open by double clic with excel Hope that help Christophe GRISON

User · Answer

With Ruby 1 8 7 I encode every field to UTF-16 and discard BOM  maybe    The following code is extracted from active scaffold export    lt                                                                                                                                                                                                                                                                                                                                    require  fastercsv                                                                                                                                                                                                                                                                                                                fcsv options                                                                                                                                                                                                                                                                                                                         row sep   gt    n                                                                                                                                                                                                                                                                                                                    col sep   gt  params  delimiter                                                                                                                                                                                                                                                                                                      force quotes   gt   export config force quotes                                                                                                                                                                                                                                                                                       headers   gt   export columns collect    column  format export column header name column                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             data   FasterCSV generate fcsv options  do  csv                                                                                                                                                                                                                                                                                     csv  lt  lt  fcsv options  headers  unless params  skip header      true                                                                                                                                                                                                                                                                 records each do  record                                                                                                                                                                                                                                                                                                            csv  lt  lt   export columns collect    column                                                                                                                                                                                                                                                                                              Convert to UTF-16 discarding the BOM  required for Excel   gt  2003                                                                                                                                                                                                                                                     Iconv conv  UTF-16    UTF-8   get export column value record  column   2  -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    end                                                                                                                                                                                                                                                                                                                             end                                                                                                                                                                                                                                                                                                                             -  gt  lt    data -  gt    The important line is   Iconv conv  UTF-16    UTF-8   get export column value record  column   2  -1

User · Answer

As Fregal said  uFEFF is the way to go    lt   LANGUAGE  JAVASCRIPT  CODEPAGE  65001   gt   lt   Response Clear    Response ContentType    text csv   Response Charset    utf-8   Response AddHeader  Content-Disposition    attachment  filename excelTest csv    Response Write   uFEFF       csv text here   gt

User · Answer

A correctly formatted UTF8 file can have a Byte Order Mark as its first three octets   These are the hex values 0xEF  0xBB  0xBF   These octets serve to mark the file as UTF8  since they are not relevant as  byte order  information  1  If this BOM does not exist  the consumer reader is left to infer the encoding type of the text   Readers that are not UTF8 capable will read the bytes as some other encoding such as Windows-1252 and display the characters        at the start of the file   There is a known bug where Excel  upon opening UTF8 CSV files via file association  assumes that they are in a single-byte encoding  disregarding the presence of the UTF8 BOM  This can not be fixed by any system default codepage or language setting   The BOM will not clue in Excel - it just won t work   A minority report claims that the BOM sometimes triggers the  Import Text  wizard   This bug appears to exist in Excel 2003 and earlier   Most reports  amidst the answers here  say that this is fixed in Excel 2007 and newer   Note that you can always  correctly open UTF8 CSV files in Excel using the  Import Text  wizard  which allows you to specify the encoding of the file you re opening   Of course this is much less convenient   Readers of this answer are most likely in a situation where they don t particularly support Excel  lt  2007  but are sending raw UTF8 text to Excel  which is misinterpreting it and sprinkling your text with    and other similar Windows-1252 characters   Adding the UTF8 BOM is probably your best and quickest fix   If you are stuck with users on older Excels  and Excel is the only consumer of your CSVs  you can work around this by exporting UTF16 instead of UTF8   Excel 2000 and 2003 will double-click-open these correctly    Some other text editors can have issues with UTF16  so you may have to weigh your options carefully        Except when you can t   at least  Excel 2011 for Mac s Import Wizard does not actually always work with all encodings  regardless of what you tell it   lt  anecdotal-evidence gt

User · Answer

I can only get CSV to parse properly in Excel 2007 as tab-separated little-endian UTF-16 starting with the proper byte order mark

[excel] Microsoft Excel mangles Diacritics in .csv files?

Examples related to excel

Examples related to encoding

Examples related to csv

Examples related to diacritics