Save MySQL query results into a text or CSV file

Contributor Icon Contributed by qmchenry  
Tag Icon Tagged: MySQL  

MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other applciation which accepts data in CSV format.


Given a query such as

SELECT order_id,product_name,qty FROM orders

which returns three columns of data, the results can be placed into the file /tmo/orders.txt using the query:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.txt'

This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

In this example, each field will be enclosed in “double quotes,” the fields will be separated by commas, and each row will be output on a new line separated by a newline (\n). Sample output of this command would look like:

"1","Tech-Recipes sock puppet","14.95"
"2","Tech-Recipes chef's hat","18.95"
...

Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.

 

39 Comments -


  1. Mike said on February 28, 2009

    Thanks for this, a MySQL noob like myself always forgets the exact syntax!

    Bookmarked.

  2. sshah said on March 17, 2009

    Thanks for the helpful post but my problem is that when i execute the query and go to the location where i saved the file, i can’t find any file there. However if i again try the same command it says “File already exists” but actually there is not. Wha’ts wrong?

  3. Amit said on April 22, 2009

    I am facing the same problem. Did you find the answer?

  4. Sumesh said on April 29, 2009

    Is there a similar thing available in oracle?

  5. Leion said on May 6, 2009

    How can I used a a date value in the output file?

  6. Heini said on May 18, 2009

    Just like this:

    SELECT order_id,product_name,qty
    FROM orders
    INTO OUTFILE ‘c:/tmp/orders.csv’

    Just type in the drive you want.

  7. jai said on June 23, 2009

    I dont know y the new line operator is not at all working in my MySql…
    am using version 5.0…

    could some one plz help me out…………….

  8. Udit Chaudhary said on July 3, 2009

    This happens only if you have executed the query twice and redirecting it to same location with same name otherwise rest is fine.

  9. shiv` said on July 21, 2009

    can u tell me how can i get the column names along wtiht the result set

  10. Sven said on July 22, 2009

    I got the same problem when I connect to a remote database server. The file is created on the remote server.

    Anyone know how to get this file created on the local host?

  11. Anonymous said on August 7, 2009

    For oracle ,you can use toad

  12. Anonymous said on August 12, 2009

    “Thanks for this, a MySQL noob like myself always forgets the exact syntax!

    Bookmarked.”

    it was those people who memorized syntax or function than understanding what a language can do or cant do was noob.

  13. augusto said on October 24, 2009

    how a very few lines may save lots of time…..
    thanks a lot!

  14. Anonymous said on October 24, 2009

    Thank you very much indeed. Your article has just saved me a tremdous amount of time, trouble & effort. An excellent example of the web being used as it should be.

    Many thanks again.

    Reg Bailey.

  15. Anonymous said on October 25, 2009

    Hi,

    Also discovered that including the ‘optionally’ keyword, eg:

    … optionally enclosed by ‘”‘ …

    that MySQL will decide which fields (eg. text, char etc) need enclosing in double quotes and which fields (eg. int, float etc) do not in order to comply with strict CSV format. Depending on your table make-up & size, this too is a great time saver. Mant thanks again for pointing me (and many others) in the right direction.

    Reg.

  16. Anonymous said on November 30, 2009

    it was very useful buddy..but i need how to save the commands given by me… :)

  17. Anonymous said on January 9, 2010

    Incorrect syntax near the keyword ‘into’.

  18. Anonymous said on January 12, 2010

    thanks…..
    this is more useful to my study……….
    once again my hearty thanks………

  19. Brad H said on January 27, 2010

    SELECT INTO OUTFILE Save the result on the server. If you are on a remote MySQL Console, you can use the following to save the result locally

    T logfile
    to log it to logfile. then use
    t
    to turn off logging

    http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html

  20. Anonymous said on February 18, 2010

    Dear Sir
    Its not working it shows error on into keyword

  21. Brad H said on February 19, 2010

    What way are you trying?

  22. Anonymous said on April 7, 2010

    I could really use the column names as well…Sorry for draggin up an old thread but this is the only one that really helped me after search for over an hour.

  23. Ahsan Ghias said on May 19, 2010

    HI,
    its my first post here on that site. I want to save the file in some directory other than tmp i.e like ‘/home/myname/’. Is it possible to do it ?
    Thanx in advance…

  24. Quinn McHenry said on May 19, 2010

    Oh yeah, you can save the file anywhere that the user running the server has permission to write. Anyone can write to /tmp, so that’s a certain place to use. If the server process owner doesn’t have write access to /home/myname, then you could make a subdirectory there and use chmod to make it writable by all or change the group of the directory and then chmod g+rwx the directory to make it writable by the group the server is running as.

  25. Ken said on May 24, 2010

    You would use the spool command.

  26. ramakrishna said on June 10, 2010

    but fields names are not coming on top of the result in csv file…..

  27. Keerthi said on August 11, 2010

    Hi,

    When I executed the below query, the results are not getting copied to csv file, instead *.sql file is getting created and the query is getting copied to that file..
    select x,y,z FROM temp_table t where x = 1 into outfile ‘orders.csv’

    Pelase help..

  28. ag said on September 25, 2010

    FWIW, on my windows box, when I follow the original recipe, the output wouldn’t go to any folder I specified explicitly (permissions I think — mySql is running as a service and not as a regular user).
    But, if I changed the line to:
    into outfile ‘c:bt_1to1.csv’
    it showed up ok in:
    C:Program FilesMySQLMySQL Server 5.0data

  29. Nikos Stylianou said on November 2, 2010

    i got an access denied because the mysql user i was using wasn’t granted OUTPUT priviledges, so after looking at the mysql docs http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html I can output the result as xml like this;

    mysql –xml -h 00.00.000.000 -uUSERNAME -pPASSWORD DATABASE_NAME -e “select whatever from whatever where whatever” > /path/to/save/file.xml

    That will output your results as xml :) fyi, the -e means to execute and exit, without that it won’t execute the query.

    Hope someone finds that useful

  30. At said on November 10, 2010

    The web should be used for distributing pictures of pretty girls.

  31. Gannoswarthi said on December 3, 2010

    I am having another problem with CSV files.If in select query any field is empty then in csv it’s displayed as N.

  32. cool b said on January 19, 2011

    nice article, everytime i use to forget the syntax this i have bookmarked

  33. Ritesh said on February 10, 2011

    how can we want to export data into text format with fix size ?

  34. Unixrat said on March 9, 2011

    Thanks a lot !!! even though I have long years in the IT world, I am not a programer and in SQL I am a rookie too ..
    Concise EXAMPLES helps a lot
    Oscar D’Antona

  35. Elim Qiu said on July 15, 2011

    I have select privilege to a remote mysql server, the 
    [into outfile] clause is interpreted to write query results into remote server rather than my local machine. Any ideas? Thanks

    yL

  36. hima said on September 28, 2011

    thank you for the clear explination,
    and i have one problem
    i am currently using mysql 5.5
    and \n is not working????
    plz suggest me

  37. hima said on September 28, 2011

    I got the solution for \n in windows..
    If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY ‘\r\n’ to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY ‘\r’.

    ex:
    instead of lines terminated by ‘\n’
    use lines terminated by ‘\r\n’
    it works on my system..

    reference:
    http://dev.mysql.com/doc/refman/5.5/en/load-data.html

  38. prabha said on October 19, 2011

    thanks buddy.

  39. fp said on October 24, 2011

    In my installation (MySQL 5.1 on Ubuntu 11.04), only mysql root user has privileges to write the output file (absolute path). But… why?
    Maybe this is useful for others.

 

RSS feed for comments on this post. TrackBack URL

Leave a comment -