title: MySQL export into outfile : CSV escaping chars
body: <p>I've a database table of timesheets with some common feilds.</p>
  
  <pre><code>id, client_id, project_id, task_id, description, time, date 
  </code></pre>
  
  <p>There are more but thats the gist of it.</p>
  
  <p>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. </p>
  
  <p>This all works with me looping through the timesheets with php and printing the lines to a file.</p>
  
  <p>The problem is with a big database it can take hours to run which isn't acceptable.  So I rewrote it with the MySQL <code>INTO OUTFILE</code> command and it reduced it down to a few seconds to run which was great.</p>
  
  <p>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.</p>
  
  <p>This is a snippet of the MySQL code I have:</p>
  
  <pre><code>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 ....
  </code></pre>
  
  <p>But...</p>
  
  <p>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. </p>
  
  <p>Any thoughts on a best course of action?</p>