MySQL export to CSV
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.