Save MySQL query results into a text or CSV file
Posted by Quinn McHenry in 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.
About Quinn McHenry
View more articles by Quinn McHenry
The Conversation
Follow the reactions below and share your own thoughts.
February 28, 2009 at 2:24 am, Mike said:
Thanks for this, a MySQL noob like myself always forgets the exact syntax!
Bookmarked.
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?
April 22, 2009 at 3:05 pm, Amit said:
I am facing the same problem. Did you find the answer?
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.
April 25, 2012 at 8:59 am, Meena said:
> I am also facing the same problem. Any answers?
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.
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?
April 29, 2009 at 10:57 am, Sumesh said:
Is there a similar thing available in oracle?
May 06, 2009 at 9:32 am, Leion said:
How can I used a a date value in the output file?
May 24, 2010 at 5:04 pm, Ken said:
You would use the spool command.
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…………….
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
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.
August 07, 2009 at 1:26 pm, Anonymous said:
For oracle ,you can use toad
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.
October 24, 2009 at 10:13 am, augusto said:
how a very few lines may save lots of time…..
thanks a lot!
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.
November 10, 2010 at 10:13 pm, At said:
The web should be used for distributing pictures of pretty girls.
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.
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…
January 09, 2010 at 5:00 pm, Anonymous said:
Incorrect syntax near the keyword ‘into’.
January 12, 2010 at 9:19 am, Anonymous said:
thanks…..
this is more useful to my study……….
once again my hearty thanks………
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
February 18, 2010 at 7:39 am, Anonymous said:
Dear Sir
Its not working it shows error on into keyword
February 19, 2010 at 2:37 pm, Brad H said:
What way are you trying?
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…
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.
June 10, 2010 at 8:03 am, ramakrishna said:
but fields names are not coming on top of the result in csv file…..
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..
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
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
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.
January 19, 2011 at 12:59 pm, cool b said:
nice article, everytime i use to forget the syntax this i have bookmarked
February 10, 2011 at 12:31 pm, Ritesh said:
how can we want to export data into text format with fix size ?
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
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
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
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
October 19, 2011 at 3:26 am, prabha said:
thanks buddy.
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.
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.
May 03, 2012 at 8:16 pm, susheel said:
Thanks ! Very much for providing step
September 06, 2012 at 11:55 am, hemant said:
Dear Sir
Its not working it shows error on into keyword
September 23, 2012 at 4:45 pm, awlad said:
good article, can it overwrite the existing file with same name?
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’”;