Thursday, July 10, 2008

Snippit Of The Day: MySQL SELECT INTO OUTFILE ...

Here's a handy code snippit from the MySQL manual. Use it to convert an arbitrary SELECT statement into a .csv file:

SELECT * INTO OUTFILE 'c:/temp/dump.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM <some-table>
  WHERE <some-where-clause>

I always forget how easy MySQL and PostgreSQL make it to get CSV data from a SELECT statement.

8 comments:

  1. ! Just solved a nightmare for me. Good work!

    ReplyDelete
  2. So glad I could help!

    -Ben

    ReplyDelete
  3. Anonymous1:17 AM

    hi.. when i tried to execute this, am getting error
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '\\Server\ServerShare\dump.csv'.

    my query is
    'Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '\\Server\ServerShare\dump.csv'.'

    help me :(

    ReplyDelete
  4. Anonymous1:18 AM

    Anonymous said...
    hi.. when i tried to execute this, am getting error
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '\\Server\ServerShare\dump.csv'.

    my query is
    SELECT * INTO OUTFILE '\\Server\ServerShare\dump.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM tblConnection

    help me :(

    ReplyDelete
  5. Hmmm...maybe you need to escape the slashes? So your path is:

    \\\\Server\\ServerShare\\dump.csv

    Or maybe network shares aren't supported?

    ReplyDelete
  6. Anonymous1:55 AM

    ya ya.. got it.. thanku so much !! its working fine now :)

    ReplyDelete
  7. SELECT * INTO OUTFILE '\\\\\Documents
    and Settings\\user\\My Documents\\`mobile code`.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM `mobile code`
    i execute this but incorrect error

    ReplyDelete
  8. Anonymous6:45 AM

    This query is giving error

    ReplyDelete