How can I output a UTF-8 CSV in PHP that Excel will read properly?
To quote a Microsoft support engineer,
Excel for Mac does not currently support UTF-8
Update, 2017: This is true of all versions of Microsoft Excel for Mac before Office 2016. Newer versions (from Office 365) do now support UTF-8.
In order to output UTF-8 content that Excel both on Windows and OS X will be able to successfully read, you will need to do two things:
Make sure that you convert your UTF-8 CSV text to UTF-16LE
mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
Make sure that you add the UTF-16LE byte order mark to the start of the file
chr(255) . chr(254)
The next problem that appears only with Excel on OS X (but not Windows) will be when viewing a CSV file with comma separated values, Excel will render rows only with one row and all of the text along with the commas in the first row.
The way to avoid this is to use tabs as your separated value.
I used this function from the PHP comments (using tabs "\t" instead of commas) and it worked perfectly on OS X and Windows Excel.
Note that to fix an issue with an empty column as the end of a row, that I did have to change the line of code that says:
$field_cnt = count($fields);
to
$field_cnt = count($fields)-1;
As some of the other comments on this page say, other spreadsheet apps like OpenOffice Calc, Apple's own Numbers and Google Doc's Spreadsheet have no issues with UTF-8 files with commas.
See the table in this question for what works and doesn't work for Unicode CSV files in Excel
As a side note, I might add that if you are using Composer, you should have a look at adding League\Csv
to your requires. League\Csv
has a really nice API for building CSV files.
To use League\Csv
with this method of creating CSV files, check out this example
Is it possible to force Excel recognize UTF-8 CSV files automatically?
Alex is correct, but as you have to export to csv, you can give the users this advice when opening the csv files:
- Save the exported file as a csv
- Open Excel
- Import the data using Data-->Import External Data --> Import Data
- Select the file type of "csv" and browse to your file
- In the import wizard change the File_Origin to "65001 UTF" (or choose correct language character identifier)
- Change the Delimiter to comma
- Select where to import to and Finish
This way the special characters should show correctly.
PHP export CSV when data having UTF8 charcters
This post solved my problem: https://stackoverflow.com/a/4762708/2349494
And this is what did the conversion:
print chr(255) . chr(254) . mb_convert_encoding($csv_output, 'UTF-16LE', 'UTF-8');
Earlier I was getting garbage characters now correct data is exported.
PHP output Issues with utf-8 characters
You should check the character encoding of the xlsx file. If the file was created on windows then it may have the Windows-1252 (CP1252) character encoding. If so then it needs to be converted to UTF-8. See the documentation on how to handle the character encoding of excel files. Following should be useful:
https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/10-Reading-and-Writing.md#reading-a-csv-file and
https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/10-Reading-and-Writing.md#writing-utf-8-csv-files
https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/10-Reading-and-Writing.md#writing-utf-8-html-files
Also see this related issue: How can I output a UTF-8 CSV in PHP that Excel will read properly?
Related Topics
How to Retrieve Images from MySQL Database and Display in an HTML Tag
How to Find Where I Will Be Redirected Using Curl in PHP
How to Find All Youtube Video Ids in a String Using a Regex
How Can One Check to See If a Remote File Exists Using PHP
How to Call a Function from a String Stored in a Variable
PHP - Check If Two Arrays Are Equal
PHP Generate File For Download Then Redirect
Is There a PHP Function That Can Escape Regex Patterns Before They Are Applied
Remove a Child With a Specific Attribute, in Simplexml For PHP
How to Rename Uploaded File Before Saving It into a Directory
Multiple Returns from a Function
How to Send 100,000 Emails Weekly
Use PHP to Create, Edit and Delete Crontab Jobs