1

Is there a way I can export the qry results into a csv or excel file?

PMa
  • 1,751
  • 7
  • 22
  • 28
  • 1
    I think the following url will help you: http://stackoverflow.com/questions/1119312/mysql-export-into-outfile-csv-escaping-chars – mariusa Apr 14 '14 at 23:11

1 Answers1

2

You can do it by OUTFILE:
Here is an example of storing the result in CSV file.

SELECT * FROM table_name
INTO OUTFILE '/dir/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Visit here for more details: http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

Shreyos Adikari
  • 12,348
  • 19
  • 73
  • 82
  • Thanks Shreyos, if I want save the file to `'C:\Users\pma\Dropbox\testtest.csv'`, how should I put into the qry? I tried `/pma/Dropbox/testtest.csv` but couldn't find the file. Thanks! – PMa Apr 14 '14 at 23:36
  • I think you are looking file into the client machine. This will help http://stackoverflow.com/questions/11484471/cant-find-the-file-created-by-outfile-in-mysql – Shreyos Adikari Apr 15 '14 at 00:25
  • thanks, I read the tread, but still have two questions:1) where can I find the files that are being written to the server? 2) how can I change the qry so that the file is written to my harddrive? Thanks!! – PMa Apr 15 '14 at 01:29
  • Try to use C:/Users/pma/Dropbox/testtest.csv. This will do I think. – Shreyos Adikari Apr 15 '14 at 01:32
  • if you do not specify the path to the outfile then it is inside the data folder of your mysql install, and inside the folder of the same name as the database you queried. i.e. C:\Program Files\mysql\data\nameofdatabaseyouqueried – Shreyos Adikari Apr 15 '14 at 01:33
  • I checked two folders: 1) `C:\Program Files\MySQL\MySQL Server 5.6\data` and 2) `C:\Program Files (x86)\MySQL\MySQL Workbench 6.0 CE\data`, but nothing found. But when I ran the qry, it told me that `Query OK, 3371 rows affected`. Even when I do a search with file name 'testest.csv' on my harddrive, nothing come up. This is weird... where could the file be... – PMa Apr 15 '14 at 01:44
  • then when I do the following qry `select * from mydata into outfile 'C:/testest.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n';`, I got an error msg `Can't create/write to file C:\testest.csv .` But I should be able to create any file and save to to `C:\` – PMa Apr 15 '14 at 01:48
  • @PerriMa Then try to run mysql run as administrator(r-click->run as administrator) and see what happens . – Shreyos Adikari Apr 15 '14 at 01:53
  • I logged in as 'root' and still get the same error message :( – PMa Apr 15 '14 at 02:53
  • I changed to `./mysql/testest.csv`, it told me that qry OK. But when I went to the folder `C:\Program Files\MySQL\MySQL Server 5.6\data\mysql` still cannot find the csv file... is it possible the files were hidden? – PMa Apr 15 '14 at 02:57
  • Yes it can be possible. – Shreyos Adikari Apr 15 '14 at 14:07
  • Check this http://dba.stackexchange.com/questions/17029/cannot-output-mysql-data-to-file – Shreyos Adikari Apr 15 '14 at 19:54
  • Check if you can do it like c:\> mysql -u username -h nameofserver -p -e "SELECT ... FROM database.table WHERE ..." > c:\info\table1.txt – Shreyos Adikari Apr 15 '14 at 21:56
  • Refer: http://stackoverflow.com/questions/3755808/outfile-local-path-specification – Shreyos Adikari Apr 15 '14 at 21:57
  • IT WORKED!!!! Here is what I typed on the command line `mysql -u root -h localhost -p -e "select * from dashboard.tablets" > c:/table1.csv`. However I cannot include this script into a stored procedure :( I am writing a procedure that will do this automatically without existing to the command C:\... (thank you for being so patient with me.. you are super!) – PMa Apr 15 '14 at 23:23
  • @PerriMa : Wow .. Thats great. At last our effort have some values. :) – Shreyos Adikari Apr 15 '14 at 23:30
  • I found the files that were exported before by using `outfile` :) They were hidden! – PMa Apr 16 '14 at 00:19
  • Done! thanks again, my life suddenly got a lot more easier :D – PMa Apr 16 '14 at 04:36