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.

  • http://pagesofinterest.net/blog/ Mike

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

    Bookmarked.

  • http://www.freeminutescall.com sshah

    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?

    • Amit

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

      • Heini

        Just like this:

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

        Just type in the drive you want.

      • Meena

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

      • Loki77

        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.

    • Udit Chaudhary

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

    • Sven

      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?

    • pesare_bad

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

  • Sumesh

    Is there a similar thing available in oracle?

    • http://Leion.net Leion

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

    • Ken

      You would use the spool command.

    • Joaquin

      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.

    • Niket

      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!)

  • jai

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

    • shiv`

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

      • Anonymous

        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.

  • Anonymous

    For oracle ,you can use toad

  • Anonymous

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

  • augusto

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

  • Anonymous

    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.

    • At

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

  • Anonymous

    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.

  • Anonymous

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

  • Anonymous

    Incorrect syntax near the keyword ‘into’.

  • Anonymous

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

  • Brad H

    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

  • Anonymous

    Dear Sir
    Its not working it shows error on into keyword

    • Brad H

      What way are you trying?

  • Ahsan Ghias

    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…

    • http://www.tech-recipes.com Quinn McHenry

      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.

  • ramakrishna

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

  • Keerthi

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

  • http://openid-provider.appspot.com/ompeag ag

    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

  • Nikos Stylianou

    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

  • Gannoswarthi

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

  • http://pulse.yahoo.com/_3K6ZZGPZKH7JZXUZKE3V7WULRA cool b

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

  • Ritesh

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

  • Unixrat

    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

  • Elim Qiu

    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

  • hima

    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

  • hima

    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

  • prabha

    thanks buddy.

  • fp

    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.

  • James

    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.

  • susheel

    Thanks ! Very much for providing step

  • hemant

    Dear Sir
    Its not working it shows error on into keyword

  • awlad

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

  • Kevin Harter

    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’”;

  • fzishk

    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 ?

  • gfd

    huhhahahaha