How Export a MySQL table to a Coma Delited File

[raymundo]

Is there a way to extract/export the contenets of a MySQL table into a coma delimited text file for use with MS Excel?

If yes. What are the commands?

Thanks,
Ray

 

[sophisticate]

This should work from your shell prompt--

mysql -u username -p --exec='select * from yourtable' yourdatabase | tr "\t" "," > results.txt

So it executes the query, returns it in tab delimited format, pipes it through a translation and writes the results to results.txt.

Hope this helps,

Brendan Cassida
brendanc@icehouse.net

 

[raymundo]

Brendan, Thanks for the quick response. I tried the command but I can't tell if it has created a file. That is I can't find one. Is their anyway to tell it to write to a certain directory. I don't have direct access to my MySQl directory as Iam on a shared server.

I tried:
mysql> mysql -u username -p --exec='select * from Items' cigstore | tr "\t" "," > /tmp/cigstore.txt
->

It looked like it was waiting for another command.

Any ideas?

 

[sophisticate]

Actually, you want to execute that command from your shell prompt (on many Linux systems, this ends with a $ symbol), rather than the MySQL client. It will write the file to whatever directory you are currently logged into, provided that you have write permissions on that directory. You don't have to have access to the real MySQL directory.

Good luck,

brendanc@icehouse.net