CODEWITHSUNDEEP
phpmysqlexcelexport-to-csv
Rohan
Rohan

Reputation: 13

trying to export csv in microsoft excel with special character like chinese but failed

I have a web app where I am trying to export to CSV from a database. It runs perfectly with english character set, but when I put some chinese text in the database my CSV shows dumb character like ????.

<?php
$con=mysqli_connect(global_dbhost,global_dbusername,global_dbpassword,global_dbdatabase);

if(isset($_GET['csv']))
{

        $query ='SELECT CONCAT("TC00", `t_id`),m_id,s_id,t_name,Description,start_date,end_date,start_time,end_time,status,active FROM tc_task';
        $today = date("dmY");
    //CSVExport($query);

$con=mysqli_connect(global_dbhost,global_dbusername,global_dbpassword,global_dbdatabase);
    //echo 'inside function';
    $sql_csv = mysqli_query($con,$query) or die("Error: " . mysqli_error()); //Replace this line with what is appropriate for your DB abstraction layer

    file_put_contents("csvLOG.txt","\n inside ajax",FILE_APPEND);

    header("Content-type:text/octect-stream");
    header("Content-Disposition:attachment;filename=caring_data".$today.".csv");
    while($row = mysqli_fetch_row($sql_csv)) {
        print '"' . stripslashes(implode('","',$row)) . "\"\n";
    }
    exit;

}
?>

Upvotes: 0

Views: 5618

Answers (1)

jmac
jmac

Reputation: 7128

Solution available here:

  1. Open in notepad (or equivalent)
  2. Re-Save CSV as Unicode (not UTF-8)
  3. Open in Excel
  4. Profit

Excel does not handle UTF-8. If you go to the import options for CSV files, you will notice there is no choice for UTF-8 encoding. Since Unicode is supported, the above should work (though it is an extra step). The equivalent can likely be done on the PHP side (if you can save as Unicode instead of UTF-8). This seems doable according to this page which suggests:

$unicode_str_for_Excel = chr(255).chr(254).mb_convert_encoding( $utf8_str, 'UTF-16LE', 'UTF-8');

Upvotes: 3

Related Questions