MySQL export into outfile : CSV escaping chars

Question

I've a database table of timesheets with some common feilds.

id, client_id, project_id, task_id, description, time, date  

There are more but thats the gist of it.

I have an export running on that table to a CSV file overnight to give the user a backup of their data. It also is used as a data import for a macro Excel file with some custom reports.

This all works with me looping through the timesheets with php and printing the lines to a file.

The problem is with a big database it can take hours to run which isn't acceptable. So I rewrote it with the MySQL INTO OUTFILE command and it reduced it down to a few seconds to run which was great.

The problem now is I can't seem to escape all the new line characters, etc., in the description field. Really, a user can type potentially any combination of characters in here including carriage returns/new lines.

This is a snippet of the MySQL code I have:

SELECT id,         client,        project,        task,        REPLACE(REPLACE(ifnull(ts.description,''),'\n',' '),'\r',' ') AS description,         time,        date         INTO OUTFILE '/path/to/file.csv'       FIELDS ESCAPED BY '""'       TERMINATED BY ',' ENCLOSED BY '"'       LINES TERMINATED BY '\n'       FROM .... 

But...

When I try look at the source of the output file, newlines still exist in the file, therefore the CSV import for the Excel breaks all the fancy macros and pivot tables the Excel wizard has created.

Any thoughts on a best course of action?

Answer 1

I think your statement should look like:

SELECT id,     client,    project,    task,    description,     time,    date     INTO OUTFILE '/path/to/file.csv'   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'   LINES TERMINATED BY '\n'   FROM ts 

Mainly without the FIELDS ESCAPED BY '""' option, OPTIONALLY ENCLOSED BY '"' will do the trick for description fields etc and your numbers will be treated as numbers in Excel (not strings comprising of numerics)

Also try calling:

SET NAMES utf8; 

before your outfile select, that might help getting the character encodings inline (all UTF8)

Let us know how you get on.