Save MySQL query results into a text or CSV file

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.

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

51 Responses to “Save MySQL query results into a text or CSV file”

  1. February 28, 2009 at 2:24 am, Mike said:

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

    Bookmarked.

    Reply

  2. March 17, 2009 at 9:06 am, sshah said:

    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?

    Reply

    • April 22, 2009 at 3:05 pm, Amit said:

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

      Reply

      • May 18, 2009 at 12:06 pm, Heini said:

        Just like this:

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

        Just type in the drive you want.

        Reply

      • April 25, 2012 at 8:59 am, Meena said:

        > I am also facing the same problem. Any answers?

        Reply

      • August 06, 2013 at 2:46 am, Loki77 said:

        The file is stored in the MySQL-Server “data” Directory.
        If you used the command
        mysql>use DATABASENAME;
        it is stored in the database subdirectory.

        Reply

    • July 03, 2009 at 11:30 am, Udit Chaudhary said:

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

      Reply

    • July 22, 2009 at 3:53 pm, Sven said:

      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?

      Reply

    • November 08, 2013 at 2:39 pm, pesare_bad said:

      > because it will save the file on server side and not in your client side!

      Reply

  3. April 29, 2009 at 10:57 am, Sumesh said:

    Is there a similar thing available in oracle?

    Reply

    • May 06, 2009 at 9:32 am, Leion said:

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

      Reply

    • May 24, 2010 at 5:04 pm, Ken said:

      You would use the spool command.

      Reply

    • June 25, 2013 at 6:21 am, Joaquin said:

      For those who cannot do it, it is due to the fact that the file is written in the server and probably (as in my case) you are connecting remotely.

      Reply

    • August 19, 2013 at 7:05 pm, Niket said:

      Use the SPOOL command in SQLPlus.

      >SPOOL file
      >SELECT …. ;
      >SPOOL OFF

      Everything upto SPOOL OFF winds up in “file” (on the client machine! not on the server like MySQL!)

      Reply

  4. June 23, 2009 at 8:52 am, jai said:

    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…………….

    Reply

    • July 21, 2009 at 12:01 pm, shiv` said:

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

      Reply

      • April 07, 2010 at 11:38 pm, Anonymous said:

        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.

        Reply

  5. August 07, 2009 at 1:26 pm, Anonymous said:

    For oracle ,you can use toad

    Reply

  6. August 12, 2009 at 1:53 pm, Anonymous said:

    “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.

    Reply

  7. October 24, 2009 at 10:13 am, augusto said:

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

    Reply

  8. October 24, 2009 at 12:21 pm, Anonymous said:

    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.

    Reply

    • November 10, 2010 at 10:13 pm, At said:

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

      Reply

  9. October 25, 2009 at 1:20 pm, Anonymous said:

    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.

    Reply

  10. November 30, 2009 at 2:17 am, Anonymous said:

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

    Reply

  11. January 09, 2010 at 5:00 pm, Anonymous said:

    Incorrect syntax near the keyword ‘into’.

    Reply

  12. January 12, 2010 at 9:19 am, Anonymous said:

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

    Reply

  13. January 27, 2010 at 7:08 pm, Brad H said:

    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

    Reply

  14. February 18, 2010 at 7:39 am, Anonymous said:

    Dear Sir
    Its not working it shows error on into keyword

    Reply

    • February 19, 2010 at 2:37 pm, Brad H said:

      What way are you trying?

      Reply

  15. May 19, 2010 at 12:58 pm, Ahsan Ghias said:

    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…

    Reply

    • May 19, 2010 at 2:29 pm, Quinn McHenry said:

      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.

      Reply

  16. June 10, 2010 at 8:03 am, ramakrishna said:

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

    Reply

  17. August 11, 2010 at 8:05 pm, Keerthi said:

    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..

    Reply

  18. September 25, 2010 at 11:30 pm, ag said:

    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

    Reply

  19. November 02, 2010 at 4:48 pm, Nikos Stylianou said:

    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

    Reply

  20. December 03, 2010 at 9:17 am, Gannoswarthi said:

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

    Reply

  21. January 19, 2011 at 12:59 pm, cool b said:

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

    Reply

  22. February 10, 2011 at 12:31 pm, Ritesh said:

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

    Reply

  23. March 09, 2011 at 4:59 pm, Unixrat said:

    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

    Reply

  24. July 15, 2011 at 1:35 pm, Elim Qiu said:

    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

    Reply

  25. September 28, 2011 at 2:45 am, hima said:

    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

    Reply

  26. September 28, 2011 at 11:12 pm, hima said:

    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

    Reply

  27. October 19, 2011 at 3:26 am, prabha said:

    thanks buddy.

    Reply

  28. October 24, 2011 at 8:19 am, fp said:

    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.

    Reply

  29. April 19, 2012 at 9:00 pm, James said:

    I understand how to do this when I am the administrator on the MySQL server but it I query against a remote MySQL server/datbase, where I don’t have administrator access I can’t write to that remote drive. How do I pipe the out of a query or write it to another machine and drive in the Windows world.

    Reply

  30. May 03, 2012 at 8:16 pm, susheel said:

    Thanks ! Very much for providing step

    Reply

  31. September 06, 2012 at 11:55 am, hemant said:

    Dear Sir
    Its not working it shows error on into keyword

    Reply

  32. September 23, 2012 at 4:45 pm, awlad said:

    good article, can it overwrite the existing file with same name?

    Reply

  33. January 30, 2013 at 12:01 pm, Kevin Harter said:

    Thanks a ton for a well written example. After some server massaging (nothing kinky!), I was able to automate a monthly report that queries the database, places the results into a CSV file, and then emails that file as an attachment.

    Now, I have a couple of related issues to solve. Any help would be greatly appreciated!

    (1) Is there a way to display the results on the screen AND send the output to a CSV file?
    (2) How can I include the MySQL column headers in the CSV?
    (3) Is it possible to hide some of the columns in the CSV?
    (4) THE MOST NAGGING PROBLEM: Can I convert UNIX timestamps *inside* the query statement? The database table stores a “last login” as a UNIX timestamp, but I want human-readable info in the CSV file. Here’s my current code:

    $query = “SELECT `login`.`username`, `login`.`timestamp`, `employees`.`first_name`, `employees`.`last_name` FROM `login` LEFT JOIN `employees` on `employees`.`username` = `login`.`username` WHERE `userlevel` = ’5′ AND `timestamp` BETWEEN $dateInPast AND $currentTimestamp ORDER BY `timestamp` DESC INTO OUTFILE ‘/tmp/employee-usage-$currentTimestamp.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’”;

    Reply

  34. December 26, 2013 at 5:26 am, fzishk said:

    i followed the steps you mentioned above but its showing me the error:

    Can’t create/write to file ‘\tmp\member_personal_info2.csv’ (Errcode: 2)

    can you please solve this issue ?

    Reply

  35. March 22, 2014 at 1:26 am, gfd said:

    huhhahahaha

    Reply

Leave a Reply