ansaurus

Question

Microsoft Excel mangles Diacritics in .csv files?

Answer 1

A:

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.

Adam Rosenfield 2008-09-30 21:11:09

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

Freddo411 2008-09-30 21:17:23

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

AlbertEin 2008-09-30 21:19:25

A BOM might do the trick, yep.

2008-09-30 21:20:48

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

Adam Rosenfield 2008-09-30 21:21:15

Excel doesn't have the dropdown AFAIK

AlbertEin 2008-09-30 21:23:43

Answer 2

A:

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.

AlbertEin 2008-09-30 21:12:25

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

Freddo411 2008-09-30 21:18:02

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

AlbertEin 2008-09-30 21:22:28

Answer 3

A:

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.

Jeff Yates 2008-09-30 21:30:01

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

spoulson 2009-07-01 18:28:19

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.

Jeff Yates 2009-07-01 18:42:33

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

RichardOD 2009-10-13 13:35:58

Answer 4

+12 A:

This is a known bug with Excel and opening UTF8 csv files via file association: it assumes that they are ascii. This can not be fixed by any system default codepage or language setting. A BOM (EF BB BF) will not clue in Excel - it just won't work. (This statement is made for Excel 2000, 2003, and 2007.)

Note that you can 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.

So in once sense, you already are exporting text data so that Excel can read it. However, if you want "double-click" open-by-Excel-association to work, you'll have to export your text file as UTF16 instead of UTF8. Excel does handle UTF16 just fine, presumably by recognizing the Byte Order Marker.

James Baker 2008-09-30 21:30:17

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

Freddo411 2008-09-30 21:51:44

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.

Triynko 2010-05-17 20:31:05

Answer 5

+1 A:

daniels 2008-09-30 21:33:51

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

Freddo411 2008-09-30 21:45:33

Answer 6

A:

The option to use UTF-8 only applies to Excel 2008(Win).

2008-10-01 06:43:58

There is no such version of Excel.

Tony Meyer 2009-12-17 20:17:36

Answer 7

+6 A:

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.

2008-11-24 17:11:18

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.

haridsv 2010-04-30 22:57:11

Answer 8

+1 A:

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
%>

Kristof Neirynck 2009-02-27 17:29:40

Answer 9

A:

I see this question was 'answered' a long time ago and is probably lost in the depths of StackOverflow. But I wanted to add that 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>

Benjol 2009-06-05 20:06:12

Answer 10

A:

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.

John Machin 2009-06-12 03:01:12

Answer 11

+1 A:

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;
    }

    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($csv));
    echo chr(255) . chr(254) . mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
    exit;
  }

Marc Carlucci 2009-10-30 08:50:24

Answer 12

A:

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.

2009-11-05 09:56:03

Answer 13

A:

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.

Manfred Stienstra 2009-12-09 13:22:14

ansaurus

tags:

views:

answers:

Microsoft Excel mangles Diacritics in .csv files?

related questions