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
