Use MySQL into OUTFILE to generate Excel readable UTF8 data

Question

I have a problem similar to this question. That is - I need to export some UTF8 data within a MySQL database to MS Excel.

The gotchas kindly Excel provides:

• Excel opens UTF8 formatted CSV files as ANSCI, thus breaking
• Excel will open tab-seperated UTF8 files correctly, but there is no support for linebreaks (my data has linebreaks, though in a worst-case scenario I might be able to loose these)
• Excel will, apparently, open UTF-16LE (little endian) encoded CSVs OK. However, so far as I know, MySQL INTO OUTFILE does not accept content encoding argument, and just defaults to the database encoding (UTF8).

My web-app is PHP driven, but unfortunately I cannot use a PHP Excel-file-making library since the database is pretty large. All my exports must be done through MySQL.

If anybody knows how to make MySQL jump through Excel's hoops on this one, that would be great.

Many thanks,

Jack

Edit: This answer describes a solution that works for Excel 2007. Adding a 'BOM' to the file, which I may be able to do by providing the outputted file to the client via a PHP script that appends the BOM. Ideally I would like to find a solution that works in 2003 also.

Answer 1

For handling likebreaks I suggest adding:

FIELDS ENCLOSED BY '"'

A more complete example from mysql docs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

Answer 2

I recall running into this issue with Excel. The BOM fix does work for Excel 2007 and 2010. We also wanted to support 2003, however our solution was to just write XLS files instead of CSV files (using Java). That doesn't sound like an option for you since you're exporting from MySQL. One idea that comes to mind is to convert your UTF8 output to UTF-16LE after your export. This page explains how to do it with Perl.