[mysql] MySQL export into outfile : CSV escaping chars

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?

What happens if you try the following?

Instead of your double REPLACE statement, try:

REPLACE(IFNULL(ts.description, ''),'\r\n', '\n')

Also, I think it should be LINES TERMINATED BY '\r\n' instead of just '\n'

Probably won't help but you could try creating a CSV table with that content:

DROP TABLE IF EXISTS foo_export;
CREATE TABLE foo_export LIKE foo;
ALTER TABLE foo_export ENGINE=CSV;
INSERT INTO foo_export SELECT id, 
   client,
   project,
   task,
   REPLACE(REPLACE(ifnull(ts.description,''),'\n',' '),'\r',' ') AS description, 
   time,
   date
  FROM ....

Without actually seeing your output file for confirmation, my guess is that you've got to get rid of the FIELDS ESCAPED BY value.

MySQL's FIELDS ESCAPED BY is probably behaving in two ways that you were not counting on: (1) it is only meant to be one character, so in your case it is probably equal to just one quotation mark; (2) it is used to precede each character that MySQL thinks needs escaping, including the FIELDS TERMINATED BY and LINES TERMINATED BY values. This makes sense to most of the computing world, but it isn't the way Excel does escaping.

I think your double REPLACE is working, and that you are successfully replacing literal newlines with spaces (two spaces in the case of Windows-style newlines). But if you have any commas in your data (literals, not field separators), these are being preceded by quotation marks, which Excel treats much differently than MySQL. If that's the case, then the erroneous newlines that are tripping up Excel are actually newlines that MySQL had intended as line terminators.

Below procedure worked for me to resolve all the escaping issues and have the procedure more a generic utility.

CREATE PROCEDURE `export_table`(
IN tab_name varchar(50), 
IN select_columns varchar(1000),
IN filename varchar(100),
IN where_clause varchar(1000),
IN header_row varchar(2000))

BEGIN
INSERT INTO impl_log_activities(TABLE_NAME, LOG_MESSAGE,CREATED_TS) values(tab_name, where_clause,sysdate());
COMMIT;
SELECT CONCAT( "SELECT ", header_row,
    " UNION ALL ",
    "SELECT ", select_columns, 
    " INTO OUTFILE ", "'",filename,"'"
    " FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '""' ",
    " LINES TERMINATED BY '\n'"
    " FROM ", tab_name, " ",
    (case when where_clause is null then "" else where_clause end)
) INTO @SQL_QUERY;

INSERT INTO impl_log_activities(TABLE_NAME, LOG_MESSAGE,CREATED_TS) values(tab_name, @SQL_QUERY, sysdate());
COMMIT;

PREPARE stmt FROM @SQL_QUERY;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

Here is what worked here: Simulates Excel 2003 (Save as CSV format)

SELECT 
REPLACE( IFNULL(notes, ''), '\r\n' , '\n' )   AS notes
FROM sometables
INTO OUTFILE '/tmp/test.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

1. Excel saves \r\n for line separators.
2. Excel saves \n for newline characters within column data
3. Have to replace \r\n inside your data first otherwise Excel will think its a start of the next line.