Export MySQL table to CSV file?

c has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Export MySQL table to CSV file? by dug (Chaplain) on Sep 25, 2002 at 20:46 UTC
http://www.mysql.com/doc/en/SELECT.html In particular, look at the section on 'export_options'. The example which is pertinant is: `SELECT a,b,a+b INTO OUTFILE "/tmp/result.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM test_table;` [download] hth -- dug	[reply] [d/l]
(jeffa) Re: Export MySQL table to CSV file? by jeffa (Bishop) on Sep 25, 2002 at 22:42 UTC
In the interest of this obscure little languange called Perl (anyone here ever heard of it? ;)), here is a quick and dirty way via DBD::AnyData: `use strict; use DBI; my $any_dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); my $mysql_dbh = DBI->connect( qw(DBI:mysql:database:host user pass), {RaiseError=>1} ); print $any_dbh->func( 'DBI', $mysql_dbh, 'CSV', undef, {table_name=>'table'}, 'ad_convert', );` [download] jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re: Export MySQL table to CSV file? by ignatz (Vicar) on Sep 25, 2002 at 20:48 UTC
Correct me if I'm wrong, but I think that you want mysqldump's `--fields-terminated-by=char` flag as well as `--fields-escaped-by=char`. ()-() \"/ `	[reply]
Re: Export MySQL table to CSV file? by thunders (Priest) on Sep 25, 2002 at 20:55 UTC
this is not really a perl question, though one could do this in perl fairly easily* with a module like DBI I 've had to do this a number of times, i'd say the best easiest most flexible program that does this is PhpMyAdmin which requires php 3 or 4 and a webserver. Not only does PhpMyAdmin allow a flexible set of options for CSV export, but it also allow you to export SQL or PHP code to recreate your database. *I say fairly easily but there are a number of gotchas involving dealing with data containg quotation marks and/or commas. There are functions in DBI that will make quoting these strings a bit easier.	[reply]
Re: Re: Export MySQL table to CSV file? by LTjake (Prior) on Sep 25, 2002 at 22:07 UTC
As for a perl solution, gossamer-threads has a product called MysqlMan. It's based off of PHPMyAdmin. I've used it on many occasions. The best feature, imo, is the ability to make a dump file of all of the data in a database.	[reply]