How to i export Mysql Data to excel?

Question

How do i export mysql table data to microsoft excel?

is there a way to write a query to output this data?

i am assuming the query should take the form

SELECT * FROM Tablename
OUTPUT INTO SOMEFILE.xls;

kindly help

that post doesn't resolve this error ERROR 1 (HY000): Can't create/write to file '/home/inventor/ictbf.csv/' (Errcode: 21) — Inventor, Oct 25 '13 at 10:26
Then why don't you add to your question that you get this error? *sigh* See e.g. http://stackoverflow.com/questions/7209158/select-into-outfile-not-working-even-for-root http://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile — Ocaso Protal, Oct 25 '13 at 10:27
I have managed to accomplish the task SELECT * FROM mytable INTO OUTFILE '/root/tmp/myoutput.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' — Inventor, Oct 25 '13 at 12:11

score 3 · Answer 1 · answered Oct 25 '13 at 09:31

3

you can do it in CSV format:

SELECT *
FROM Tablename
INTO OUTFILE 'C:/tmp/result.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

answered Oct 25 '13 at 09:31

Elen

hi after trying your answer i encountered the following error ERROR 1 (HY000): Can't create/write to file '/home/inventor/ictbf.csv/' (Errcode: 21) – Inventor Oct 25 '13 at 10:24
@Inventor make sure you have writing permission on the folder you trying to write to – Elen Oct 25 '13 at 11:26
thanks i have managed – Inventor Oct 25 '13 at 12:10

score 1 · Answer 2 · answered Oct 25 '13 at 09:33

1

Maybe you can connect to the database using Excel and the just import the data using the specified query.

You can do this on the data tab of Excel. Assuming you have excel 2007 or higher.

answered Oct 25 '13 at 09:33

Rogier Wijsman

score 0 · Answer 3 · answered Oct 25 '13 at 11:23

0

You can use some GUI clients like SQLyog or Navicat and export. SQLyog has free version.

answered Oct 25 '13 at 11:23

Sathish D

3 Answers3