PHP export CSV when data having UTF8 charcters

Question

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.

@Mark Baker yes i have read about it in some posts but i am not sure how to use it. Can you help. — Sajal, Jul 11 '13 at 12:15
Before outputting any of your data to the file, write "\xEF\xBB\xBF" to the file...it may also help to write a line "sep=," after the BOM and before the data (or appropriate character if you're not using a comma) because MS Excel's default separator is locale-specific — Mark Baker, Jul 11 '13 at 12:21
The ‘similar question’ has example code. Putting a fake-BOM in a UTF-8 file is almost always the Wrong Thing, but it is necessary for getting Excel to read UTF-8 CSV unfortunately. — bobince, Jul 11 '13 at 12:32
Possible duplicate of [How can I output a UTF-8 CSV in PHP that Excel will read properly?](https://stackoverflow.com/questions/4348802/how-can-i-output-a-utf-8-csv-in-php-that-excel-will-read-properly) — wp78de, Apr 11 '18 at 20:54

score 21 · Accepted Answer · edited Apr 11 '18 at 22:37

21

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.

edited Apr 11 '18 at 22:37

wp78de

18,207
7
43
71

answered Jul 25 '13 at 07:45

Sajal

1,198
1
17
32

Note that the resulting csv will be twice the size of a UTF8 one. A small price to pay for a very easy solution to a hairy problem that has been plaguing many people for a long while ! – Ellert van Koperen Dec 05 '14 at 12:08
A small finding I would like to add here is, if you are doing tsv rather than csv then add \t using double quotes "" not single quotes '' – Qarib Haider Nov 23 '15 at 07:04
THANK YOU! This tip saved my day. – kalinma Dec 04 '17 at 17:53
Unfortunately for me roughly 75% of the resulting text is in Chinese characters, the remaining 25% is shown correctly. What could this mean? – Gabe Hiemstra Aug 03 '21 at 10:56

score 13 · Answer 2 · answered Jul 24 '13 at 20:24

13

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;

answered Jul 24 '13 at 20:24

Poorya

131
2

din't worked my friend. Showing garbage characters in elx/csv file. I have put all code here. Plz check - http://oxyjone.blogspot.in/2013/07/generating-excel-having-utf-8.html – Sajal Jul 25 '13 at 07:17
1

I think its my bad! mysql_query("SET NAMES utf8"); should be befor main query! :) – Poorya Aug 09 '13 at 10:30
tip: the "\xEF\xBB\xBF" BOM gives you an error while using nginx, you can't force download using it. hours wasted on that. – lsouza Oct 16 '14 at 19:51
This line solve the problem print "\xEF\xBB\xBF"; // UTF-8 BOM – Hussein mahyoub Mar 29 '18 at 23:23
@Husseinmahyoub same for me – Bhavin Thummar Feb 25 '19 at 12:38

score 6 · Answer 3 · answered Oct 15 '13 at 15:53

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 !

score 0 · Answer 4 · answered Jul 11 '13 at 12:30

0

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();

answered Jul 11 '13 at 12:30

Ashwin Parmar

3,025
3
26
42

Please refer this `http://stackoverflow.com/questions/4348802/how-can-i-output-a-utf-8-csv-in-php-that-excel-will-read-properly?rq=1` – Ashwin Parmar Jul 16 '13 at 09:25

PHP export CSV when data having UTF8 charcters

4 Answers4

Linked