91

I am wondering how do I export table data into a csv? I read that I need to use mysql workbench command line but I can not figure out how to launch the cmd line(don't know what the command is).

Running on Windows 7 64bit.

chobo2
  • 83,322
  • 195
  • 530
  • 832
  • 1
    Would this type of question be a better fit for another SE site? Maybe I am misreading this question, but exporting data from an application seems more like a "how to use this feature in an application" versus a "how do I programmatically do this with an API" type of question. :) – summea Jun 14 '13 at 17:14

3 Answers3

82

You can select the rows from the table you want to export in the MySQL Workbench SQL Editor. You will find an Export button in the resultset that will allow you to export the records to a CSV file, as shown in the following image:

MySQL Workbench Export Resultset Button

Please also keep in mind that by default MySQL Workbench limits the size of the resultset to 1000 records. You can easily change that in the Preferences dialog:

MySQL Workbench Preferences Dialog

Hope this helps.

Sergio
  • 4,537
  • 4
  • 33
  • 41
  • Ok. I will try that hopefully it can handle showing almost a Million rows. – chobo2 Jun 14 '13 at 18:01
  • 1
    I'm afraid a million would be too much for it. If that's your case you are better off with http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format – Sergio Jun 15 '13 at 10:33
  • I ran into trouble trying to do this for binary data. Didn't want to mess with the encodings, so I just re-wrote one of our apps to move data between prod & test. But if you have any ideas, I'd love to read em. – Patrick M Aug 27 '14 at 19:52
  • 1
    @Sergio FYI they've updated the icons and the process is a little different now. There is now a section called "Export/Import" and an icon with a floppy disk for export. – Dan May 13 '15 at 15:14
  • This is an old screenshot. This answer needs updating and version information / path to screen needs providing too. – 8bitjunkie Apr 10 '18 at 11:39
64

U can use mysql dump or query to export data to csv file

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
laalto
  • 150,114
  • 66
  • 286
  • 303
Neeti
  • 731
  • 5
  • 4
  • 1
    I wonder why this answer is not the accepted one, it works like a charm, even for 100,000s of records! I used it with 180,000 records, it was done in 0.18 seconds. :-) – physicalattraction Nov 25 '14 at 09:00
  • 14
    Yes it's much faster, but you need to access files on the database host, which is not always possible for everyone... – phil_w Feb 19 '15 at 16:41
  • Getting this error "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' ". Please let me know the workaround – Barani r Feb 12 '16 at 06:28
  • 1
    I know this is an old answer, but I wanted to add another item to follow up to @phil_w response: additionally, the MySQL user may not be granted permissions to write to files, but it still works to use MySQL Workbench. – The Dude Apr 22 '20 at 17:12
45

MySQL Workbench 6.3.6

Export the SELECT result

  • After you run a SELECT: Query > Export Results...

    Query Export Results

Export table data

  • In the Navigator, right click on the table > Table Data Export Wizard

    Table Data Export

  • All columns and rows are included by default, so click on Next.

  • Select File Path, type, Field Separator (by default it is ;, not ,!!!) and click on Next.

    CSV

  • Click Next > Next > Finish and the file is created in the specified location

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199