MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL export to CSV

with 6 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

6 Responses to 'MySQL export to CSV'

Subscribe to comments with RSS or TrackBack to 'MySQL export to CSV'.

  1. This really helps! Thanks!

    Monica

    14 Oct 10 at 12:44 pm

  2. Gettting a SQL not properly ended at the filename and from is not expected in that position?

    Richie

    28 Mar 13 at 5:03 am

  3. For inputs, try putting a return after the end of the last line of data.

    maclochlainn

    28 Mar 13 at 10:31 am

  4. On the export, are you using forward slashes or backslashes? If you’re using backslashes double them because otherwise they back quote the closing single or double quote.

    maclochlainn

    28 Mar 13 at 10:41 am

  5. Hi, I´ve used your code exactly as you posted but I have this error:

    Error Code: 1. Can’t create/write to file ‘C:\libros.csv’ (Errcode: 13 – Permission denied)

    I don’t know what to do, I have tried many things but I always get this error, I hope you can help me, I need it so much.

    Xavi

    15 May 13 at 10:57 pm

  6. This shows you how to grant a global file permissions to a restricted user. It is only necessary when you want the user to read or write server-side files.

    Connect as the root user:

    C:\Data> mysql -uroot -pcangetin -P3306

    Then, grant the global privilege to the user:

    mysql> GRANT FILE ON *.* TO 'student'@'localhost' IDENTIFIED BY 'student';

    Now, you can connect to the data base as the restricted user and read an external server-side file:

    C:\Data> mysql -ustudent -pstudent -P3306

    Once you’ve granted these permissions, the user is no longer truly a restricted user. I’d strongly discourage doing this if the user is accessed via web applications.

    maclochlainn

    17 May 13 at 5:42 pm

Leave a Reply