Sajal
Sajal

Reputation: 1216

PHP export CSV when data having UTF8 charcters

In MySQL I have set my data field type to utf8_bin and i am storing data in Unicode. Texts are being properly displayed in web pages.

I want to generate excel file exporting data from my table to it. The output in .xls and .cvs is - '????'.

I checkout out other answers here, its been referred to use headers:

header("content-type:application/csv;charset=UTF-8");

similar question. But its not working. After using header, in csv Output is - सूरà¥à¤¯à¤¾.

Please help. Thanks.

Upvotes: 11

Views: 90579

Answers (4)

Sajal
Sajal

Reputation: 1216

This post solved my problem: https://stackoverflow.com/a/4762708/2349494

And this is what did the conversion:

print chr(255) . chr(254) . mb_convert_encoding($csv_output, 'UTF-16LE', 'UTF-8');

Earlier I was getting garbage characters now correct data is exported.

Upvotes: 21

ldaguise
ldaguise

Reputation: 81

I have a variant to Sajal response and found it on php.net at : http://www.php.net/manual/en/function.iconv.php#104287

Echo this at the begining of the content of the csv :

chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $data)

I prefer with iconv() but it seems it do the same as mb_convert_encoding(). Don't forget to replace ; by tabs (\t) and it worked fine for me like this !

Upvotes: 6

Poorya
Poorya

Reputation: 131

I had the same problem and I did it by combining two things:

First, you have to change table collation to UTF8 e.g: "utf8_general_ci" (or just make sure of that), then add this code after your MySQL query:

mysql_query("SET NAMES utf8");

Like

$result = mysql_query("SHOW COLUMNS FROM table WHERE Field NOT IN ('user_id', 'password')");
mysql_query("SET NAMES utf8");

And then, use this as header (adapt with your needs):

header('Content-Description: File Transfer');
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
header('Content-Transfer-Encoding: binary');
header('Pragma: public');
print "\xEF\xBB\xBF"; // UTF-8 BOM
print $csv_output;
exit;

Upvotes: 13

Ashwin Parmar
Ashwin Parmar

Reputation: 3045

Try following code;

ob_end_clean();
$_filename = date('Y_m_d_H_i').".csv";

header("Cache-Control: public"); 
header("Content-Type: application/octet-stream");
header("Content-Type: text/csv; charset=utf-8");
header("Content-Disposition: attachment; filename=$_filename");
// Read the file from disk
readfile($filename);
exit();

Upvotes: 0

Related Questions