MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL export to CSV

with 8 comments

While working through export techniques in MySQL (5.1.34-community), I discovered some tricks and techniques with MySQL exports to a CSV file.

Here’s a standard export statement to a CSV file format:

mysql> SELECT *
    -> INTO OUTFILE 'C:/Data/City.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM City;
Query OK, 4079 ROWS affected (0.02 sec)

On Microsoft Windows, when you attempt to export it a second time, you’ll get an error unless you’ve previously deleted the physical file. You’ll also need the \r on the Windows platform but not the Linux platform.

mysql> SELECT *
    -> INTO OUTFILE 'C:/Data/City.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM City;
ERROR 1086 (HY000): File 'c:/Data/City.csv' already EXISTS

While reviewing Alan Beaulieu’s Learning SQL, 2nd Edition, I noticed he’s got a small example in his Appendix B. He’s using the back-quoted backslash approach to directories in Windows. You can use it, but I prefer the one shown in my examples. Here’s the alternative syntax for the outbound file line:

    -> INTO OUTFILE 'C:\\Data\\City.csv'

When you want to use the CASE statement, you need to use a derived (MySQL terminology). It appears that you can’t include a CASE statement in the SELECT clause when exporting the contents to an OUTFILE. Also, for reference, MySQL doesn’t support the WITH clause.

SELECT *
INTO OUTFILE 'c:/Data/City4.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
FROM (SELECT ID
      ,      CASE
               WHEN Name IS NULL THEN '' ELSE Name
             END AS Name
      ,      CASE
               WHEN CountryCode IS NULL THEN '' ELSE CountryCode
             END AS CountryCode
      ,      CASE
               WHEN District IS NULL THEN '' ELSE District
             END AS District
      ,      CASE
               WHEN Population IS NULL THEN '' ELSE Population
             END AS Population
      FROM City) Subquery;

Hope this helps somebody.

Written by maclochlainn

May 7th, 2009 at 2:23 pm

Posted in Microsoft XP,MySQL,sql

«
»