How do I export a big table to CSV

spiderplant0 posted 6 years ago in Import/Export
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 posted 6 years ago
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.
spiderplant0 posted 6 years ago
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 posted 6 years ago
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?
spiderplant0 posted 6 years ago
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.
ansgar posted 6 years ago
Hm, the menu is ok I think, as there just two "export" items, saying clearly what they do. No need for a sub menu.
RinatL posted 5 years ago
Had the same problem, idea with increasing limit was helpful, but it works slow. "Export as csv" option is a must
ansgar posted 5 years ago
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?
RinatL posted 5 years ago
130 000 rows, 6 columns. My network connection is not very fast so direct download to csv file is an only option
CorBlimey posted 4 years ago
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 posted 4 years ago
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.