UTF-8 MySQL Database Exporting to CSV
Moderator: General Moderators
UTF-8 MySQL Database Exporting to CSV
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.
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
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
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?
I've tried that and haven't had success. Other ideas?
Re: UTF-8 MySQL Database Exporting to CSV
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.
http://www.creativyst.com/Doc/Articles/ ... dEncodings
I would follow pytrin's suggestion - i.e. encode it in ASCII format.
There are 10 types of people in this world, those who understand binary and those who don't
Re: UTF-8 MySQL Database Exporting to CSV
Can you post the code that you use to create the CSV file?
Re: UTF-8 MySQL Database Exporting to CSV
This was my original code:
Of course i've tried everything I wrote...
Code: Select all
<?php
$ourFileHandle = fopen($resFileName, 'w') or die("can't open file");
$stringData = "?????" // hebrew text
fwrite($ourFileHandle, $stringData);
fclose($ourFileHandle);
?>
Re: UTF-8 MySQL Database Exporting to CSV
Try to open the handle in binary mode
Also, I don't think it's relevant, but I use fputcsv - http://www.php.net/fputcsv
Code: Select all
$ourFileHandle = fopen($resFileName, 'wb');
Re: UTF-8 MySQL Database Exporting to CSV
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
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):
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).
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);
}
}
Re: UTF-8 MySQL Database Exporting to CSV
Does it matter whether it's encoded in utf8_unicode or utf8_general?
I tried the folowing just for a test:
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.
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);
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
fputcsv accepts an array not a string
Re: UTF-8 MySQL Database Exporting to CSV
Sorry, tried an array and still an empty file...
Do you have any IM we can use and I'll show you?
Do you have any IM we can use and I'll show you?
Re: UTF-8 MySQL Database Exporting to CSV
Sorry mate, I post here on occasion but I'm not technical support
hope you take it the right way
hope you take it the right way
-
daemon1981
- Forum Newbie
- Posts: 2
- Joined: Mon Apr 27, 2009 4:52 am
Re: UTF-8 MySQL Database Exporting to CSV
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.

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.
-
daemon1981
- Forum Newbie
- Posts: 2
- Joined: Mon Apr 27, 2009 4:52 am
Re: UTF-8 MySQL Database Exporting to CSV
may be the best it's to give my code :
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
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>";decode is a good solution for at least latin characters
Last edited by Benjamin on Mon Apr 27, 2009 10:06 am, edited 1 time in total.
Reason: Changed code type from text to php.
Reason: Changed code type from text to php.