Exporting data from MySQL to Excel with UTF-8 encoding

Question

I have a mysql line with utf8_general_ci matching, when I export it to csv, instead of the correct utf-8 characters I get Ć…ā€¦I , etc., how to make excel understand UTF-8 encoding here is my code:

 $conn = mysql_connect('localhost', 'root', 'asdfggh') or die(mysql_error()); mysql_query("SET CHARACTER SET utf8"); mysql_query("SET NAMES utf8"); mysql_select_db('table_name', $conn) or die(mysql_error($conn)); $query = sprintf('SELECT * FROM sudraba_birzs'); $result = mysql_query($query, $conn) or die(mysql_error($conn)); header('Content-Encoding: UTF-8'); header('Content-type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename="'.date("dm-Y_H:i") . '.csv'.'"'); echo "\xef\xbb\xbf"; $row = mysql_fetch_assoc($result); if ($row) { echocsv(array_keys($row)); } while ($row) { echocsv($row); $row = mysql_fetch_assoc($result); } function echocsv($fields) { $separator = ''; foreach ($fields as $field) { if (preg_match('/\\r|\\n|,|"/', $field)) { $field = '"' . str_replace('"', '""', $field) . '"'; } echo $separator . $field; $separator = ','; } echo "\r\n"; } 

How to export it in order to correctly display all characters (so that Excel understands utf-8), and also to support table layout (with rows and columns)

Answer 1

You generate a CSV, which is basically a text file. It is not possible to specify encoding information in such files. Most text editors implement (better or worse) automatic encoding detection. Excel does not work. Excel will simply accept ANSI when you right-click the CSV file. (You need to use the Open menu to get a coding hint.)

Your only option (besides switching to a different output format) will convert the data to ANSI, either with mb_convert_encoding () or with iconv () . But now you have one more problem: ANSI is not a real encoding, it basically means "whatever encoding is installed on my computer running Windows." First you need to find out the typical encoding that most of your users use. It mainly depends on the country. For example, many countries in Western Europe use Win-1252.

Answer 2

I had the same problem (a common problem in databases with Spanish language). I wrote this and it worked:

This is a class that connects to the database, and the functions will do what you want using mysqli and PHP. In this case, calling this class (require or include), just use the downloadCsv () function.

As an example, this will be the file "class.php":

 <?php class DB{ private $con; //this constructor connects with the database public function __construct(){ $this->con = new mysqli("Your_Host","Your_User","Your_Pass","Your_DatabaseName"); if($this->con->connect_errno > 0){ die('There was a problem [' . $con->connect_error . ']'); } } //create the function that will download a csv file from a mysqli query public function downloadCsv(){ $count = 0; $header = ""; $data = ""; //query $result = $this->con->query("SELECT * FROM Your_TableName"); //count fields $count = $result->field_count; //columns names $names = $result->fetch_fields(); //put column names into header foreach($names as $value) { $header .= $value->name.";"; } } //put rows from your query while($row = $result->fetch_row()) { $line = ''; foreach($row as $value) { if(!isset($value) || $value == "") { $value = ";"; //in this case, ";" separates columns } else { $value = str_replace('"', '""', $value); $value = '"' . $value . '"' . ";"; //if you change the separator before, change this ";" too } $line .= $value; } //end foreach $data .= trim($line)."\n"; } //end while //avoiding problems with data that includes "\r" $data = str_replace("\r", "", $data); //if empty query if ($data == "") { $data = "\nno matching records found\n"; } $count = $result->field_count; //Download csv file header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=FILENAME.csv"); header("Pragma: no-cache"); header("Expires: 0"); echo $header."\n".$data."\n"; } ?> 

After creating the "class.php" file in this example, use this function in the "download.php" file:

 <?php //call the "class.php" file require_once 'class.php'; //instantiate DB class $export = new DB(); //call function $export->downloadCsv(); ?> 

After downloading, open the file using MS Excel.

Hope this helps you, I think I wrote it well, I didn’t like the text box and code box.

Answer 3

As an alternative and many sipmler solution you can try this, I have been using it for several years and have never had a problem with it.

db_connect.php

 <?php $mysqli = new mysqli($mysqli_host, $mysqli_user, $mysqli_pass, $mysqli_db); if ($mysqli->connect_errno) { $debug = $debug.'<br/>Failed to connect to MySQL: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error; } else { $debug = $debug.'<br/>Connected to '. $mysqli->host_info; } ?> 

Export function

 function export($query_exp,$name) { require '../lib/db_config.php'; require '../lib/db_connect.php'; $filename = $name.' - '.date('Ymd').'.xls'; /*set desired output file name here*/ function cleanData(&$str) { $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; if ($str=='') {$str='-';} } header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: application/vnd.ms-excel"); $flag = false; if ($result = $mysqli->query($query_exp)) { if ($result->num_rows>0) { $result->data_seek(0); while ($row = $result->fetch_assoc()) { if(!$flag) { print implode("\t", array_keys($row)) . "\r\n"; $flag = true; } array_walk($row, 'cleanData'); print implode("\t", array_values($row)) . "\r\n"; } } else { $debug = $debug.'<br/>Empty result'; /*DEBUG*/ } } else { $debug = $debug.'<br/>Oups, Query error!<br/>Query: '.$query_exp.'<br/>Error: '.$mysqli->error.'.'; /*DEBUG*/ } require '../lib/db_disconnect.php'; } 

You can call the function as:

 export('SELECT * FROM SAMPLE WHERE 1;','desired_file_name.extension')