How do I export a big table to CSV

[expired user #5085]'s profile image [expired user #5085] posted 12 years ago in Import/Export Permalink
Hi, I cant figure out how to export an entire table to .CSV
I have found the 'export database' but dont think this does CSV.
And I found the 'export grid rows' but I cant get this to export all rows. (I have 2 million rows).
ansgar's profile image ansgar posted 12 years ago Permalink
Such huge datasets are problematic in the "Export grid rows" dialog. You could try a SELECT ... INTO OUTFILE, and grab the output file from the server afterwards.

Second time I heard from a user about huge CSV exports. I should find and fix the memory problem in the export dialog.
[expired user #5085]'s profile image [expired user #5085] posted 12 years ago Permalink
Thanks anse, though just to be clear, I dont think I experienced a memory problem or other bug. I figured out later that what might of worked was to change the max number of rows to 2million in preferences and then click 'display all rows' and re-run. But I though it would be easier to use phpmyadmin, which is a pity as it would be handy to use HeidiSQL for everything.
Could there be a CSV export option in the 'export database' menu?
ansgar's profile image ansgar posted 12 years ago Permalink
Yes, changing the max rows should work theoretically, only the huge number will most probably cause memory errors or extreme CPU load, as the dialog is not optimized for such large datasets. So, my thought was to refactor the dialog to find and fixe these bottlenecks.

There is no "export database" menu - what do you mean?
[expired user #5085]'s profile image [expired user #5085] posted 12 years ago Permalink
Menu > Tools > Export Database as SQL

Ie could it be changed to...
Menu > Tools > Export Database
and then we can choose to have the tables outputted in any format such as SQL or CSV or gzipped CSV etc.
A bit like how phpmyadmin does it.
Cheers.
ansgar's profile image ansgar posted 12 years ago Permalink
Hm, the menu is ok I think, as there just two "export" items, saying clearly what they do. No need for a sub menu.
[expired user #6413]'s profile image [expired user #6413] posted 12 years ago Permalink
Had the same problem, idea with increasing limit was helpful, but it works slow. "Export as csv" option is a must
ansgar's profile image ansgar posted 12 years ago Permalink
Bigger tables are usually exported as an SQL file, not for Excel which has a hardcoded limit of 1 Mio rows. How big is the table in question and how many rows has it?
[expired user #6413]'s profile image [expired user #6413] posted 12 years ago Permalink
130 000 rows, 6 columns. My network connection is not very fast so direct download to csv file is an only option
[expired user #7295]'s profile image [expired user #7295] posted 10 years ago Permalink
I need to export big csv files to other data media - not just Excel.
Right now I'm spending h-o-u-r-s editing out the quotes and other stuff from a 15 million record "sql" file output by Heidi, cos I don't know how else to create that size csv file.
Does HeidiSQL have any facility to do this?
ansgar's profile image ansgar posted 10 years ago Permalink
Well you can fire your query and use the grid export dialog on that result. But you will run out of memory in such a situation. Instead, you should use mysqldump on the command line. See here for detailed instructions: http://stackoverflow.com/questions/12040816/mysqldump-in-csv-format

Please login to leave a reply, or register at first.