Export to CSV in UTF-8 for Excel using php

Question

Below are my code used to generate csv file. 
My problem is UTF-8 characters  are not coming correctly.
even I tried iconv also, but  no result.

PHP CODE
-----------------

$con = mysql_connect('localhost', 'root', ''); $db_selected = mysql_select_db('test', $con); mysql_query("SET NAMES utf8"); $qry_res = mysql_query("SELECT * FROM table 2");
$filename = "test.csv";
$fp = fopen('php://output', 'w');
//$header = array('id','name');
$header = array("Id","Name");
header('Content-type: application/csv;charset=utf-8');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $header);

while($data = mysql_fetch_row($qry_res)){
  fputcsv($fp, $data);
}

 exit;

------------------------------------------
Table value:
-----------------------
Id  Name
2   traducción de idiomas
3   תרגום שפות
4   language translation
7   Tłumaczenie na język

Result:
Id  Name
2   traducciÃ³n de idiomas
3   ????? ????
4   language translation 
7   T?umaczenie na j?zyk

Thanks in advance.

Not sure. Use Content-Type "text/csv;charset=UTF-8" as application means binary data without encoding. It seems the output encoding is single-byte Latin-1. And you might want to write at the beginning of the content a BOM for Unicode identification under Windows: U+FEFF, in java "\ufeff" forgotten about PHP. — Joop Eggen
– Joop Eggen, Commented Apr 20, 2016 at 9:37
Excel is notoriously bad when it comes to UTF-8; your best bet is to probably just generate a UTF-8 CSV file (which you appear to be doing) and then in Excel do a data import. You can then specify the character encoding of the file when you import it... or use something like Open Office. — CD001
– CD001, Commented Apr 20, 2016 at 9:44

KiwiJuicer · Accepted Answer · 2016-04-20 09:34:25Z

14

Try to add following after your headers:

echo "\xEF\xBB\xBF";  // BOM header UTF-8

answered Apr 20, 2016 at 9:34

KiwiJuicer

1,9721 gold badge14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KiwiJuicer Over a year ago

Did you try it @Sakthi

Sakthi Over a year ago

It works fine.. Sorry for the late response Thank you.

KiwiJuicer Over a year ago

Thanks for the reply. You might want to accept this as the correct answer, cheers

DSalomon · Accepted Answer · 2019-05-18 06:29:24Z

0

If you origin data are UTF-8, the best way to export data to CSV / Excel readable, is to converter your data.

$chain1 = iconv("UTF-8", "ISO-8859-15", 'érase camión del niño'); 
$chain2 = iconv("UTF-8", "cp1252", 'érase camión del niño'); 
echo $chain1.$chain2;

That worked for me.

answered May 18, 2019 at 6:29

DSalomon

491 silver badge5 bronze badges

Comments

Benjamin · Accepted Answer · 2023-09-22 14:27:03Z

0

Assuming your data is UTF in your DB, then you need to modify how you produce the document.

In my code I replace a bare echo with a modified one using iconv:

// echo $csv;

echo iconv("UTF-8", "ISO-8859-2", $csv);

Excel assumes ISO. An explicit instruction to dump it for UTF is what's required. Works for me every time.

answered Sep 22, 2023 at 14:27

Benjamin

1961 gold badge3 silver badges18 bronze badges

Collectives™ on Stack Overflow

Export to CSV in UTF-8 for Excel using php

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related