Page 1 of 1

UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 1:24 am
by tsrivi
Hello all.
I've been trying for several hours to solve this problem:
I have a mysql table with utf-8 data in it. When i try to export it to a csv file using php (fwrite...) i can't see the correct characters.
I've tried using iconv, mb_convert_encoding, adding a UTF-16-LE BOM, UTF-8 BOM, nothing helped.

Could anyone help me getting the correct characters displayed when exporting to excel csv?

P.S
If i save and import the file manually via import data (Using excel 2007) i then get a manual option of choosing the encoding, which is windows-1255 - then i get the correct characters. But this is not a good solution as I don't want each time to manually import the data.

Any help/suggestion would be much appreciated.

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 3:32 am
by Eran
From my experience, excel doesn't read php generated UTF encoded files very well. I usually use iconv to convert one of the windows-12?? encodings (such as windows-1255). I pass all the text through something like:

Code: Select all

 
iconv("UTF-8","WINDOWS-1255",html_entity_decode( $string ,ENT_COMPAT,'utf-8'));
 

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 5:51 am
by tsrivi
Thanks for the fast response. Besides that line you don't add anything else? maybe a special character?
I've tried that and haven't had success. Other ideas?

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 6:13 am
by VladSun
In general, CSV is not to be used with UTF-8.
http://www.creativyst.com/Doc/Articles/ ... dEncodings

I would follow pytrin's suggestion - i.e. encode it in ASCII format.

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 6:13 am
by Eran
Can you post the code that you use to create the CSV file?

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 6:21 am
by tsrivi
This was my original code:

Code: Select all

 
<?php
$ourFileHandle = fopen($resFileName, 'w') or die("can't open file");    
$stringData = "?????" // hebrew text
fwrite($ourFileHandle, $stringData);
fclose($ourFileHandle); 
?>
 
Of course i've tried everything I wrote...

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 7:05 am
by Eran
Try to open the handle in binary mode

Code: Select all

 
$ourFileHandle = fopen($resFileName, 'wb');
 
Also, I don't think it's relevant, but I use fputcsv - http://www.php.net/fputcsv

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 7:13 am
by tsrivi
Nope, hasn't helped, still gibberish... other ideas? maybe someone could post a working code that works with non-english characters exporting to csv/text file?

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 8:27 am
by Eran
I use the code that I written previously, with Hebrew, and it works flawlessly. Maybe you didn't implement the character conversion as I suggested? the following is an extract from an export class I use in one of my projects (with hebrew characters):

Code: Select all

 
//$export is an array of arrays of values
$fp = fopen($filename,'w+b');if($fp !== false) {
    foreach($export as $line) {
        foreach ($line as $key => $val) {
                $line[$key] = iconv("UTF-8","WINDOWS-1255",html_entity_decode($val,ENT_COMPAT,'utf-8'));
        }
        fputcsv($fp,$line);
    }
} 
 
You should note that this works since the data arrives from a database in UTF-8 format. If you put the data manually (as you've written before), and save the file in a non-utf encoding, this will not work (you have to set the proper encoding in the iconv function).

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 10:41 am
by tsrivi
Does it matter whether it's encoded in utf8_unicode or utf8_general?
I tried the folowing just for a test:

Code: Select all

 
$ourFileHandle = fopen($resFileName, 'w+b') or die("can't open file");
$utf = utf8_encode("????");
$val=iconv("UTF-8","WINDOWS-1255",html_entity_decode($utf,ENT_COMPAT,'utf-8'));
fputcsv($ourFileHandle,$val);
fclose($ourFileHandle); 
 
Only to get an empty, blank file...
Could you tell me what I'm doing wrong?

I appreciate your help very much, thanks in advance.

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Sun Nov 02, 2008 6:38 pm
by Eran
fputcsv accepts an array not a string

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Mon Nov 03, 2008 6:26 am
by tsrivi
Sorry, tried an array and still an empty file...
Do you have any IM we can use and I'll show you?

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Mon Nov 03, 2008 6:51 am
by Eran
Sorry mate, I post here on occasion but I'm not technical support
hope you take it the right way

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Mon Apr 27, 2009 4:58 am
by daemon1981
Hello guys,

I've been trying to write on a csv file and get problem on special characters as well. After searching and trying some answers in this forum, I figured out the easiest thing for at least latin special characters (like french characters) it is to use utf8_decode().
It cleans the encoding and give a clean csv file you can open directly with MS excel and other none MS software tablesheet.

;)

Re: UTF-8 MySQL Database Exporting to CSV

Posted: Mon Apr 27, 2009 5:02 am
by daemon1981
may be the best it's to give my code :

Code: Select all

<?php
 
error_reporting(E_ALL);
ini_set('display_errors', 'yes');
 
// Connecting, selecting database
$link = mysql_connect('localhost', 'root', 'root')
    or die('Could not connect: ' . mysql_error());
 
mysql_select_db('test') or die('Could not select database');
 
// Performing SQL query
 
$fp = fopen('file.csv', 'w+b');
 
if (!$fp) {
    echo "Cannot open file ($filename)";
    exit;
} else {
    // $query = 'SELECT * FROM header';
    // $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    // while ($header = mysql_fetch_array($result, MYSQL_NUM)) {
    //  $header = array_slice($header, 1);
    //  // foreach ($header as $key => $value) {
    //  //  $header[$key] = "\"".$value."\"";
    //  // }
    //  fputcsv($fp, $header, ";");
    //  // fwrite($fp, implode(";", $header)."\n");
    //  // var_dump($line);
    // }
    
    $header = array(
                     utf8_decode("voie de départ"), 
                     utf8_decode("code postal de départ"), 
                     utf8_decode("ville de départ"), 
                     decode("pays de départ"),
                     utf8_decode("voie d'arrivée"), 
                     utf8_decode("code postal d'arrivée"), 
                     utf8_decode("ville d'arrivée"), 
                     utf8_decode("pays d'arrivée")
    );
    // fwrite($fp, implode(";", $header)."\n");
    fputcsv($fp, $header, ";");
    
    $query = 'SELECT * FROM routes';
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
        $line = array_slice($line, 1);
        // foreach ($line as $key => $value) {
        //  $line[$key] = "\"".$value."\"";
        // }
        fputcsv($fp, $line, ";");
        // fwrite($fp, implode(";", $line)."\n");
        // var_dump($line);
    }
}
 
fclose($fp);
 
// Free resultset
mysql_free_result($result);
 
// Closing connection
mysql_close($link);
 
echo "<a href=\"http://encoding.localhost/download.php?f=file.csv\">File</a>";
you can try. If you create a string or an array of string and don't use utf8_decode it will give weird things. And if you try utf8_encode it will give even more weird things.

decode is a good solution for at least latin characters ;)