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

User · Answer

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.

User · Answer

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

User · Answer

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   quot SELECT  quot   header row       quot  UNION ALL  quot        quot SELECT  quot   select columns        quot  INTO OUTFILE  quot    quot   quot  filename  quot   quot       quot  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY    quot   ESCAPED BY   quot  quot    quot        quot  LINES TERMINATED BY   n  quot       quot  FROM  quot   tab name   quot   quot        case when where clause is null then  quot  quot  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

User · Answer

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      Excel saves  r n for line separators  Excel saves  n for newline characters within column data Have to replace  r n inside your data first otherwise Excel will think its a start of the next line

User · Answer

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

User · Answer

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

[mysql] MySQL export into outfile : CSV escaping chars

Examples related to mysql

Examples related to sql

Examples related to excel

Examples related to into-outfile