Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Export MySQL table to CSV file?

by c (Hermit)
on Sep 25, 2002 at 20:33 UTC ( #200726=perlquestion: print w/ replies, xml ) Need Help??
c has asked for the wisdom of the Perl Monks concerning the following question:

I've read through the mysqldump man page, and I searched on "mysql export csv" on this site, but I haven't found any docs or existing scripts that address the issue of exporting a sql table to a csv file. Anyone have a recommendation? Thanks -c

Comment on Export MySQL table to CSV file?
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;

    hth
    -- dug
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.
    ()-()
     \"/
      `                                                     
    
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.

      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.
(jeffa) Re: Export MySQL table to CSV file?
by jeffa (Chancellor) 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', );

    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)
    

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://200726]
Approved by valdez
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (7)
As of 2014-08-27 09:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (235 votes), past polls