Php – Encoding correctly CSV files for PHPexcel

encodingPHPphpexcelutf-16

I have a Json, decode it, write it in a CSV file with PHP, convert the CSV to .xls with the aid of PHPexcel

The goal is to export from a JSON into an excel file correctly.
I am having trouble with encoding and mainly with Excel for Mac 2011 which does not support UTF-8 CSV files: link If I preview it (spacebar) on my mac it looks well encoded. The trouble is the excel.

First the code for the CSV:

$file_csv = fopen('files/file.csv', 'w');

then the JSON:

$response_data = json_decode($connection->response['response'], true);
foreach ($response_data as $value)
  {
  //take the data I have 10 strings here in reality
  $text_lang = $value['lang'];
  $date = $value['date'];
  //insert into the csv
  $details = array($text_lang, $date );
  fputcsv($file_csv, $details);
};
fclose($file_csv);

Now if I import this CSV with the excel I have trouble with UTF-8. If I convert it with a text editor e.g. Textmate to UTF-16LE, and then import it to Excel everything is smooth.

And the PHPExcel code to create the .xls from here:

$objReader = PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->load('files/file.csv');  
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('files/summary.xls');

The summary.xls is broken on UTF-8 and when opening it requires to be repaired.

My first attempt was to convert the above file.csv into UTF-16LE in PHP to see if that will be ok with the excel.
I used iconv before fputcsv and the excel showed Chinese words…

Now I want to encode the csv file to UTF-16LE and load it well with PHPExcel.
I used the following lines and didn't work. Probably because the .csv I had created with the incov was partially (?) UTF-8 and UTF-16LE.

$objReader->setInputEncoding('UTF-16LE');
$objPHPExcel = $objReader->load('file.csv');

The goal is to render the data from the JSON into a .xls (or .xlxs) document without encoding problems on excel.

Best Answer

$objReader->setInputEncoding('');

$objPHPExcel = $objReader->load('file.csv');

this worked well for me.

Related Topic