Databases
StackOverflow2013
Postsdbo
POPHP to MySql to CSV to Excel UTF-8
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP to MySql to CSV to Excel UTF-8
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload