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.
1
-
1Would 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– summea2013-06-14 17:14:43 +00:00Commented Jun 14, 2013 at 17:14
Add a comment
|
3 Answers
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:
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:
Hope this helps.
5 Comments
chobo2
Ok. I will try that hopefully it can handle showing almost a Million rows.
Sergio
I'm afraid a million would be too much for it. If that's your case you are better off with stackoverflow.com/questions/356578/…
Patrick M
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.
Dan
@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.
8bitjunkie
This is an old screenshot. This answer needs updating and version information / path to screen needs providing too.
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
4 Comments
physicalattraction
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. :-)
phil_w
Yes it's much faster, but you need to access files on the database host, which is not always possible for everyone...
Barani r
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
The Dude
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.
MySQL Workbench 6.3.6
Export the SELECT result
After you run a
SELECT: Query > Export Results...
Export table data
In the Navigator, right click on the table > Table Data Export Wizard
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.
Click Next > Next > Finish and the file is created in the specified location
2 Comments
8bitjunkie
This answer needs expanding to include how to disable default limiting of the exported resultset
Eggcellentos
"export results" does not do what OP asked for. "Export table data" does - thanks!
