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?

Schouten

I would be very interested in hearing more about your BOM solution, as I believe I've tried "EF BB BF" which did not work for me. – Gunning

The working solution chosen was to: * include a BOM ; utf-8 * use this header: 'Content-type: text/plain; charset=utf-8' This "worked" in excel 2003 and excel 2007 -- where worked = opened without an import wizard and rendered diacritics correctly. I did not verify that the BOM was required. – Schouten

The BOM is required, I just tested this now. Without it the special chars don't render ok. – Rockies

would love it if someone could say more about how to add a BOM (byte-order marker). If I just do something like Response.Write(EF BB BF") those characters just show up at the beginning of the file. – Juxtapose

sydneyos: As Fergal says below; Prepend \uFEFF to your string. – Biosphere

please check this out: https://mcmap.net/q/57264/-javascript-to-csv-export-encoding-issue It works for me too. – Flagler

Check out this: https://mcmap.net/q/57264/-javascript-to-csv-export-encoding-issue works great for me. – Flagler

check out this. works for me too. – Flagler

See this question+answer: Is it possible to force Excel recognize UTF-8 CSV files automatically?. – Castilian

Answer 1

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 < 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. </anecdotal-evidence> :)}

Gunning

Adding a BOM appears to encourage Excel to show the import wizard. Useful, but not sufficiently elegant. I'll try the utf-16 idea. – Schouten

Took me forever to find where to specify the encoding. Save Dialog > Tools Button > Web Options > Encoding Tab. They sure are good at hiding such important things. – Adlay

Wrong: adding a BOM to an UTF-8 file loads that file correctly without requiring the import wizard in Excel 2007. – Berard

Victor - which BOM? I see a couple answers here recommending the UTF16 BOM for what are actually UTF8 files? – Gunning

We found the same thing as Victor says today (using Excel 2010, it's all we had available). Adding a UTF-8 BOM/Signature (EF BB BF) seemed to fix the double-clicking using the system default encoding, and correctly uses UTF8 :) – Rotherham

In general, a UTF-8-encoded file should not have a Byte Order Mark prepended. UTF-8 does not have variable byte order, and putting it there sabotages UTF-8's ASCII compatibility. There are some specific file formats that either allow or encourage a UTF-8 faux-BOM, but otherwise it should be avoided. CSV is entirely encoding-ignorant so it's anyone's guess as to whether a given tool will interpret the byte sequence 0xEF 0xBB 0xBF as an indicator of UTF-8; an invisible control character in the first cell; the characters  in the first cell; or something else entirely. – Alforja

@Alforja The only downside to omitted a Byte Order Mark from UTF8 is that nobody knows it's UTF8. – Brick

@Ian: Nobody knows for sure it's UTF-8 with a BOM either - 0xEF 0xBB 0xBF is a valid sequence in most legacy encodings too (hence it often being misinterpreted as ISO-8859-1 or cp1252 and displayed as ). It only helps guessing algorithms, and for file formats that specifically make allowances for it (eg XML). The downside to including a faux-BOM in UTF-8 files is you break their ASCII-compatibility (a major selling point for UTF-8) Many encoding-ignorant text tools will break faced with an unexpected leading faux-BOM. – Alforja

BTW, it does not functions properly with Excel 2013. The .CSV (TAB or ; separator) file is read correctly but when saving it falls back to ANSI encoding and TAB separator (WTF!). – Dropping

@bobince: UTF-8's ASCII compatability refers to things like an ASCII find(mystring, "X") working when mystring is UTF-8. It does not refer to banning non-ASCII characters. Including the BOM does not change this behavior, and won't break most encoding-ignorant text tools. UTF7 and UTF16 can fail on those encoding-ignorant calls. – Ruyle

UTF-8+BOM is not ASCII compatible because there is no string that is encoded as the same byte sequence as ASCII even for basic 7-bit chars—"X" is 0xEF,0xBB,0xBF,0x58 instead of 0x58. Encoding-ignorant text tools don't like seeing three bytes of random unparseable crud at the start of their input and usually do fail; this has plagued shell commands and scripts in particular. It is true that UTF-7 and UTF-16 also fail in this situation, which is why UTF-16 is rarely used outside of memory storage and UTF-7 is not used at all. UTF-8 is supposed to be the portable alternative; BOM wrecks that. – Alforja

Modified the answer to clarify that BOM is 3 octets, not three characters (terminology is a total PITA in this field), and that the legacy encoding Excel isn't ASCII but something else (on western machines Windows-1252. ASCII is a 7 bit encoding. – Parcenary

My findings are that Excel will open a UTF-8 BOM csv correctly, but can easily garble the chosen delimiter. If on successful opening and displaying, it still managed to destroy the output on saving. The only way I could prevent that was using UTF-16LE and t as delimiter – Safir

UTF-16LE+it's BOM worked for me too and solved the encoding problem when opening csv in Excel by double-click. But for me tab as a delimiter (instead of comma) is unwanted behaviour. Is there any way to change this? – Huneycutt

Curiously I am using Excel 2013 but did not find that the BOM fix worked for me. – Amylaceous

Ah ha... it turns out that having sep=, at the beginning of the file actually breaks this trick. – Amylaceous

How to quickly add the BOM to an existing .csv file with a text editor, e.g. Sublime Text? – Castilian

@Triynko: That's good to know, but it's important to note that this encoding only applies when saving in Web Page format - not as CSV. – Hispanicism

@Castilian Open .csv in Sublime Text, hit File > Save with Encoding > UTF-8 with BOM. – Mikkel

Is there a solution for the "sep=," problem? – Core

Yep, even if you put the BOM, if you use sep= to specify the separator Excel will still not use the right encoding. I just tried using ; as the separator and now the special characters are correctly displayed. I'm using Excel 2013, btw. – Aldredge

Yes! I'm exporting a pandas dataframe with Portuguese characters to csv. While Linux seems to know what to do with utf-8, in order to open easiliy in Excel I had to use utf-16 encoding. Thanks! – Leannaleanne

But, as the OP asks, if you are "programmatically exporting data (using PHP 5.2)", how do you get 0xEF,0xBB,0xBF into the file in the first instance? What would this string look like in my source code, before I pass it to file_put_contents()? – Jamaaljamaica

With a few more minutes research, it looks like it should be: $bom = "\xEF\xBB\xBF"; Surprising to me that nobody actually said this anywhere already, since that was the OP's question. – Jamaaljamaica

check out this. works for me too. – Flagler

Answer 2

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.

Entebbe

It still opens the text import wizard, so the difference is that you can simply double click, so still not ideal but the only known solution anyway. – Erg

For me, no import wizard appears with Excel 2007. – Berard

No import wizard for me either - it works as expected if a UTF8 BOM/Signature (EF BB BF) is present. – Rotherham

Also, \ufeff is a UTF-16 (BE) BOM not a UTF-8 BOM – Sophistic

No, @AlastairMcCormack, it's either, depending on how it's encoded. "\ufeff" encoded as UTF-8 is exactly EF BB BF. (Encoded as UTF-16 it will be just two bytes.) – Kyl

Good point @DaveBurt. I was thinking hex literals not Unicode literals. – Sophistic

check out this. works for me too. – Flagler

For me, no, UTF8+BOM encoded CSV with the BOM EF BB BF (3 bytes) does not work when opened it Excel. UTF-16 solves the problem instead: #6002756 – Castilian

Answer 3

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

Aleta

I tried several other suggestions on this page, but this worked for me in Excel 2007. The most important changes were to use tabs instead of commas (even though it's a .csv file) and the line above that echos the two characters followed by the call to mb_convert_encoding(). I also had to recompile PHP with --enable-mbstring to get support for mb_convert_encoding(). Thanks! – Encourage

This worked well for me too, thanks. However, in Safari I get an error in my console 'Resource interpreted as document but transferred as...' I guess it's a WebKit quirk, judging #3899926, but perhaps it's not and/or someone has found a solution. Furthermore, in your example I would suggest a change: 'Content-Disposition: attachment; filename="'.$filename.'.csv"' because Firefox wants the double quotes, or else it will cut off your filename after a space. – Wende

Why are you outputting CSV (text/csv) but calling it Excel (application/vnd.ms-excel)? – Gadabout

This works great! I can confirm it's working on Mac as well (in Office 2011). – Dowzall

Shouldn't this be header('Content-Length: '. mb_strlen($encoded_csv, 'UTF-16LE')); ? – Acth

Answer 4

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

Cabotage

Why not just add sep=, or whatever you want to use? If you're already adding the BOM I assume you're not averse to adding stuff to the file. – Amylaceous

Well, actually, to answer my own question, you wouldn't add the field separator declaration because it causes this trick to stop working. So basically it's garbled encoding or your file not being interpreted properly as a CSV if your users have the wrong region settings. – Amylaceous

utf-16le + BOM(0xFF 0xFE) + tab is the best – Tedric

Ahh this recommendation explains why Pandas wouldn't open an "Excel" file I was supplied with until I manually worked out it was utf-16-le encoded and tab delimited. – Rhizome

Answer 5

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

Ischium

This is useful. I have modified the question to ask how to do this without resorting to the wizard – Schouten

Answer 6

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.

Interferometer

Not true for every type of column separator nor every Excel version. Read my answer below (below for now). – Cabotage

Where should I put this? :-) – Flute

It works on current Macs, too – Nicolanicolai

Answer 7

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.

Dextrocular

Works on Excel 2011 for Mac too. – Tooth

thank you for your post, use utf-16le is ok even when you didn't install office 2007 sp3, but the BOM should be 0xFF 0xFE – Tedric

This is the right answer. Thank you! – Aceous

Answer 8

As Fregal said \uFEFF is the way to go.

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%>
<%
Response.Clear();
Response.ContentType = "text/csv";
Response.Charset = "utf-8";
Response.AddHeader("Content-Disposition", "attachment; filename=excelTest.csv");
Response.Write("\uFEFF");
// csv text here
%>

Thane

Just watch and see how your tab separator is ignored in Excel 2007 when you use BOM. You have to come up with something more. – Cabotage

Answer 9

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:

<html>
<meta http-equiv="Content-Type" content="text/html" charset="utf-8" />
<table>
<tr>
  <th>id</th>
  <th>name</th>
</tr>
<tr>
 <td>4</td>
 <td>Hélène</td>
</tr>
</table>
</html>

Colet

interesting option. It opens the text right but for some reason all the page is completely white. Without the classic spreadsheet lines delimiting rows and columns (office for mac) – Pride

Yup, same thing in Office 2007 on Windows. It's always surprised me that it worked at all, to be honest. (Note, if you add border="1" to the table, you do get lines, but just around the 4 cells :) – Colet

Answer 10

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:

>>> open('oldmac.csv', 'rb').read()
'\xef\xbb\xbfOld MacDonald had a farm,\xc3\x88\xc3\x8c\xc3\x89\xc3\x8d\xc3\x98\r\n'
>>> ^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.

Anaxagoras

@Cocowalla: Well, I just tried this (again; I did test it before posting) and it worked with Excel 2007 (which is what I'm using now). Did you do open('oldmac.csv', 'rb').read() to verify your input? – Anaxagoras

I didn't try with Excel 2007 (I know Excel 2007 reads UTF-8 files with a BOM just fine), I tried with Excel 2003 – Aerometry

@Cocowalla: Well it worked for me with Excel 2003 when I had it. Are you sure you have the latest service pack for Excel 2003? Did you verify you input as I suggested? – Anaxagoras

I did verify that notepad had stuck a BOM at the start of the file, but I'm on Excel 2003 SP2 (SP3 is available) - so I guess this only works in SP3 – Aerometry

Answer 11

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.

Taking

I've confirmed that the data is UTF-8. What do I put into the file to let excel know that my data is utf-8 (BOM?) – Schouten

I think that you need to change the file encoding, excel uses the system default codepage to handle csv files – Eduardo

I'm not entirely sure, since I don't have Excel installed on the machine I'm currently using, but with OpenOffice, there's a dropdown box for character encoding when you import a CSV file. From there, choose Unicode (UTF-8). – Taking

Excel doesn't have the dropdown AFAIK – Eduardo

check out this. works for me too. – Flagler

Answer 12

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.

Sheedy

Actually, CSV is not bound to a specific encoding. It's Excel that's assuming ASCII. en.wikipedia.org/wiki/Comma-separated_values – Ripley

That's what I said. "implemented as ASCII in Excel", "CSV defined as ASCII-based in Excel". Not sure what point you're making as you appear to be agreeing with me. – Sheedy

Actually you say "The CSV format is implemented as ASCI", I think that is where the confusion stems from. – Quinary

Answer 13

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.

Archives

Answer 14

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.

Vladi

Answer 15

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!

Indihar

Yes, this worked for me with Excel 2010. In Java use printWriter.print('\ufeff'), see also How to add a UTF-8 BOM in java. – Controller

check out this. works for me too. – Flagler

Answer 16

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.

Hollah

Thanks for this one. Works on excel windows and mac. I'm using it. – Pride

This would only work if your non-ascii character range falls entirely within Windows-1252. So for example, no korean/chinese/japanese, no cyrillic, etc. But I guess you'll slide by with this for most western european languages. – Palanquin

Answer 17

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…

Lac

I know this comment is 6 years later, but FWIW: Using JavaScript to download a file like '\uFEFF' + myCsvString works as expected on Mac Excel 15.19.1 (2016). – Landgrave

Answer 18

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.

Eduardo

The export data is utf-8. I am writing the export file with php 5 – Schouten

Transcode the data to Windows-1252 codepage, i'm not sure how to acomplish it with php – Eduardo

Answer 19

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:

<%                                                                                                                                                                                                                                                                                                                           
      require 'fastercsv'                                                                                                                                                                                                                                                                                                        
      fcsv_options = {                                                                                                                                                                                                                                                                                                           
        :row_sep => "\n",                                                                                                                                                                                                                                                                                                        
        :col_sep => params[:delimiter],                                                                                                                                                                                                                                                                                          
        :force_quotes => @export_config.force_quotes,                                                                                                                                                                                                                                                                            
        :headers => @export_columns.collect { |column| format_export_column_header_name(column) }                                                                                                                                                                                                                                
      }                                                                                                                                                                                                                                                                                                                          

      data = FasterCSV.generate(fcsv_options) do |csv|                                                                                                                                                                                                                                                                           
        csv << fcsv_options[:headers] unless params[:skip_header] == 'true'                                                                                                                                                                                                                                                      
        @records.each do |record|                                                                                                                                                                                                                                                                                                
          csv << @export_columns.collect { |column|                                                                                                                                                                                                                                                                              
            # Convert to UTF-16 discarding the BOM, required for Excel (> 2003 ?)                                                                                                                                                                                                                                     
            Iconv.conv('UTF-16', 'UTF-8', get_export_column_value(record, column))[2..-1]                                                                                                                                                                                                                                        
          }                                                                                                                                                                                                                                                                                                                      
        end                                                                                                                                                                                                                                                                                                                      
      end                                                                                                                                                                                                                                                                                                                        
    -%><%= data -%>

The important line is:

Iconv.conv('UTF-16', 'UTF-8', get_export_column_value(record, column))[2..-1]

Gaskins

Answer 20

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.

Wolver

The OP says he's programmatically exporting, so he's not looking for a solution that needs manual intervention. – Cabotage

Answer 21

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()

Graaf

Answer 22

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

Merocrine

This doesn't answer the question as its supposed to be done programmatically and not require user intervention to manually re-save every file – Imitable