title: PHP to MySql to CSV to Excel UTF-8
body: <p>I know this has been discussed several times but yet I'm getting crazy dealing with this problem. I have a form with a submit.php action. At first I didn't change anything about the charsets, I didn't use any utf8 header information.. The result was that I could read all the ä,ö,ü etc correctly inside the database. Now exporting them to <code>.csv</code> and importing them to Excel as <code>UTF-8 charset</code> (also tested all the others) results in an incorrect charset.</p>
  
  <p>Now what I tried: </p>
  
  <p>PHP: </p>
  
  <pre><code>header("Content-Type: text/html; charset=utf-8");
  $mysqli-&gt;set_charset("utf8");
  </code></pre>
  
  <p>MySQL:
  I dropped my database and created a new one:</p>
  
  <pre><code>create database db CHARACTER SET utf8 COLLATE utf8_general_ci;
  create table ...
  </code></pre>
  
  <p>I changed my my.cnf and restarted my sql server:</p>
  
  <pre><code>[mysqld]
  character-set-server=utf8
  collation-server=utf8_general_ci
  
  [mysql]
  default-character-set=utf8
  </code></pre>
  
  <p>If I connect to my db via bash I receive the following output:</p>
  
  <pre><code>| character_set_client     | utf8                             |
  | character_set_connection | utf8                             |
  | character_set_database   | utf8                             |
  | character_set_filesystem | binary                           |
  | character_set_results    | utf8                             |
  | character_set_server     | utf8                             |
  | character_set_system     | utf8                             |
  | character_sets_dir       | /usr/local/mysql/share/charsets/ |
  </code></pre>
  
  <p>A php test:</p>
  
  <pre><code>var_dump($mysqli-&gt;get_charset());
  </code></pre>
  
  <p>Giving me:</p>
  
  <pre><code>Current character set: utf8 object(stdClass)#3 (8) { ["charset"]=&gt; string(4) "utf8"     ["collation"]=&gt; string(15) "utf8_general_ci" ["dir"]=&gt; string(0) "" ["min_length"]=&gt; int(1) ["max_length"]=&gt; int(3) ["number"]=&gt; int(33) ["state"]=&gt; int(1) ["comment"]=&gt; string(13) "UTF-8 Unicode" }
  </code></pre>
  
  <p>Now I use:</p>
  
  <pre><code>mysql -uroot -ppw db &lt; require.sql &gt; /tmp/test.csv
  </code></pre>
  
  <p>require.sql is simply a</p>
  
  <pre><code>select * from table;
  </code></pre>
  
  <p>And again I'm unable to import it as a csv into Excel no matter if I choose UTF-8 or anything else. It's always giving me some crypto..</p>
  
  <p>Hopefully someone got a hint what might went wrong here..</p>
  
  <p>Cheers</p>
  
  <p>E: TextMate is giving me a correct output so it seems that the conversion actually worked and it's and Excel issue? Using Microsoft Office 2011.</p>
  
  <p>E2: Also tried the same stuff with latin1 - same issue, cannot import special characters into excel without breaking them. Any hint or workaround?</p>
  
  <p>E3: I found a workaround which is working with the Excel Import feature but not with double clicking the .csv.</p>
  
  <pre><code>    iconv -f utf8 -t ISO-8859-1 test.csv &gt; test_ISO.csv
  </code></pre>
  
  <p>Now I'm able to import the csv into excel using Windows(ANSI). Still annoying to have to use this feature instead of doubleclicking. Also I really don't get why UTF8 isn't working, not even with the import feature, BOM added and the complete database in UTF8.</p>
  
  <p>Comma separation turned out to be a mess as well. 
  1. Concat_WS works only partly because it's adding a stupid concat_ws(..) header to the .csv file. Also "file test.csv" doesn't give me a "comma separated". This means even tho everything is separated by commas Excel won't notice it using double click.
  2. sed/awk: Found some code snippets but all of them were separating the table very badly. E.g. colum street "streetname number" remained a 'streetname','number' which made 2 colums out of one and the table was screwed.</p>
  
  <p>So it seems to me that Excel can only open .csv with a double click which 
  a) Are encoded with ISO-8859-1 (and only under windows because standard mac charset is Macintosh)
  b) File having the attribute "comma separated". This means if I create a .csv through Excel itself the output of </p>
  
  <pre><code>file test1.csv
  </code></pre>
  
  <p>would be</p>
  
  <pre><code>test1.csv: ISO-8859 text, with CRLF line terminators
  </code></pre>
  
  <p>while a iconv changed charset with RegEx used for adding commas would look like:</p>
  
  <pre><code>test1.csv: ISO-8859 text
  </code></pre>
  
  <p>Pretty weird behaviour - maybe someone got a working solution.</p>