Dump MySQL table into CSV file

I needed to quickly dump bunch of MySQL tables into Excel so that our directors can play around with the data. It does make sense, believe me. It would take me at least a day or two to implement, test, and integrate the statistical analysis they want to do on the data into our web application. Meanwhile they can perform the same calculations on an excel sheet in the next 15 minutes. So I get to add bunch of items to my todo list, and they get bunch of data to play around with. Everyone wins :)

And yes, they specifically asked for Excel format, and the future reports must have “export to Excel” feature.

Anyways, here is why I love MySQL:

SELECT * INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM my_table;

You get a CSV file formated nicely for Excel. Now you can just open it, and save it as xls file if that is what you need.

If your table is to big for Excel you can of course use a WHY clause to cut down the number of results.

[tags]mysql, excel, mysql export, sql, databases[/tags]

This entry was posted in programming and tagged . Bookmark the permalink.



14 Responses to Dump MySQL table into CSV file

  1. Hi! I had the same problem and I developed 2 PHP classes to solve the problem.
    One is IAM_XLS, which exports data in Excel’s BIFF format. The class supports only text, integer and float value types but it’s kind of handy when all you need to do is dump a query result. It also has a “dump” function that does just that for you.

    The other, named IAM_CSVDump, does the same as your SQL statement but adds a couple of features that make downloading the file a nicer experience.

    Kind regards,
    Iván

    Reply  |  Quote
  2. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux says:

    Nice! Thanks. I actually wrote a short PHP script that does the CSV export here.

    I’ll definitely take a look on the BIFF format one though. Could be useful for the future. :)

    Reply  |  Quote
  3. jason UNITED STATES Mozilla Firefox Fedora Linux says:

    awesome! thanks for the tip

    Reply  |  Quote
  4. Jonathan Mozilla Firefox Windows says:

    Nice one! Exactly what I needed for some development I’m doing.

    I actually don’t care about the CSV bit, but was looking for a quick way to dump a single record from a table to file.

    This is a hell of a lot more efficient than some other methods!

    Reply  |  Quote
  5. boardtc IRELAND Mozilla Firefox Windows says:

    Perfect. Note the default location the file is saved in windows is, eg:
    C:\Program Files\MySQL\MySQL Server 5.0\data\myDatabaseName

    Reply  |  Quote
  6. Sean UNITED STATES Internet Explorer Windows says:

    I can’t seem to get Ivan’s IAM_XLS to work. When I modify the example.php file and access it via my website I get the following:

    Fatal error: Call to undefined method IAM_XLS::dump() in /export/home3/www/mentor/admin/example.php on line 8

    Yes I do have the iam_xls.php file in the same directory and have modified it to use my db settings, userid, password etc…

    Here is what my example.php file looks like:

    dump($query, ‘ceementor’, ‘mentoruser’, ‘********’, ‘mydbs1.ce.gatech.edu’);
    ?>

    If anyone could help it would be greatly appreciated!

    Thanks,

    [quote comment=”6382″]Hi! I had the same problem and I developed 2 PHP classes to solve the problem.
    One is IAM_XLS, which exports data in Excel’s BIFF format. The class supports only text, integer and float value types but it’s kind of handy when all you need to do is dump a query result. It also has a “dump” function that does just that for you.

    The other, named IAM_CSVDump, does the same as your SQL statement but adds a couple of features that make downloading the file a nicer experience.

    Kind regards,
    Iván[/quote]

    Reply  |  Quote
  7. Ivan ARGENTINA Mozilla Windows says:

    Hi! Sean asked this by email as well and the problem is that the correct “dump” method name is

    $mid_excel->WriteSQLDump($query, ‘ceementor’, ‘mentoruser’, ‘*******’, ‘mydbs1.ce.gatech.edu’);

    That will do the trick. Sorry about the confusion.

    Regards,
    Iván

    Reply  |  Quote
  8. Max CHILE Mozilla Firefox Ubuntu Linux says:

    In linux the place where this file is /var/lib/mysql/database_name/file.csv

    Reply  |  Quote
  9. Kal COLOMBIA Internet Explorer Windows says:

    Hi all, my script is running, thanks for the ideas. But I need to rewrite the file with the same name each time. How can I do that? Thanks!

    Reply  |  Quote
  10. Hilton Becker PHP says:

    @ Max:
    Hello,

    I would like to know how to change the location (path) where the file is saved. Is this possible? If so what do i need to do.

    many thanks in advance
    Hilton

    Reply  |  Quote
  11. kamlani CANADA Mozilla Firefox Windows says:

    @ Hilton Becker:

    write the full path in the file field

    Reply  |  Quote
  12. ryan JAMAICA PHP says:

    hi, i would like to know how to include the column names into the csv file

    Reply  |  Quote
  13. thiyagi INDIA Mozilla Firefox Windows says:

    thanks guys,…

    Reply  |  Quote
  14. Abhishek Tiwari INDIA Mozilla Firefox Windows says:

    this is working but I also want the coloum header with the data can any one tell me the code that how can i export data with coloum name from mysql to .csv format

    Reply  |  Quote

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>