Database table to csv character encoding

Question

So I found a script to get a csv file from the database table.

<?php 

header('Content-Type: text/csv; charset=utf-8 ');
header('Content-Disposition: attachment; filename=data.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('id','Name', 'Email', 'age','tekens'));

// fetch the data
mysql_connect('localhost', 'name', 'pass');
mysql_select_db('name');
$rows = mysql_query('SELECT * FROM info');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) 

fputcsv($output, $row);

?>

The code works but I have trouble with the characters. My database table looks like this : Database table

This is how I want it in my csv file. However my csv file looks like this : Csv file

In the csv file Хой shows like ???. Is there a way to fix this?

I am sorry if it's a duplicate. I tried so many different things.

http://stackoverflow.com/questions/279170/utf-8-all-the-way-through/279279 ... I've had this question/answer open in a tab for over a year now, and I'm still copy/pasting the URL into comments regularly \*sighs\* ... *hint: you're not setting the charset on the db connector itself* — CD001, Oct 14 '15 at 09:04
http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php ... and the *mysql_\** library is deprecated. — CD001, Oct 14 '15 at 09:06
did you try to set with `mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $conn);` after your connection is ok? — Julio Soares, Oct 14 '15 at 09:12
There's also the possibility that you're opening the .csv file in Excel ... which doesn't play nicely with utf-8 ... try something like OpenOffice Calc instead : https://www.openoffice.org/ — CD001, Oct 14 '15 at 09:24
what about `$output = fopen('php://output', 'w'); stream_encoding($output , "UTF-8");` ? — Julio Soares, Oct 14 '15 at 09:25
Nope it doesn't work in any program. Julio, when I put that in my php file. The site doesnt work anymore. — twoam, Oct 14 '15 at 09:33

score 0 · Answer 1 · answered Oct 14 '15 at 09:17

I do not think the issue with the first blank row of CSV (before even header is being set) is due to utf8 character
some how a \n is being inserted in your csv as a first character even before you set header using fputcsv($output, array('id','Name', 'Email', 'age','tekens')); you can veryfy that by opening your csv in a text editor like notpad.
You can just remove the while loop to avoid any confusion about utf8 characters and check from where does the \n is being written as the first character in data.csv.
Having said that I was not able to replicate the same issue on my side with the same code and same kind of table

score 0 · Accepted Answer · answered Oct 14 '15 at 10:11

Okay guys, I fixed it with the help from CD001. The thing i changed was :

// fetch the data
mysql_connect('localhost', 'name', 'pass');
mysql_select_db('name');
$rows = mysql_query('SELECT * FROM info');

to

// fetch the data
$link = mysql_connect('localhost', 'name', 'pass');
mysql_set_charset('utf8',$link);
mysql_select_db('name');
$rows = mysql_query('SELECT * FROM info');

Database table to csv character encoding

2 Answers2