distal-attribute
distal-attribute
distal-attribute
distal-attribute

How do I export a big table to CSV

User, date Message
Written by spiderplant0
2 years ago
Category: Import/Export
9 posts since Fri, 06 Aug 10
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).
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
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.
Written by spiderplant0
2 years ago
9 posts since Fri, 06 Aug 10
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?
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
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?
Written by spiderplant0
2 years ago
9 posts since Fri, 06 Aug 10
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.
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
Hm, the menu is ok I think, as there just two "export" items, saying clearly what they do. No need for a sub menu.
Written by RinatL
2 years ago
2 posts since Tue, 29 May 12
Had the same problem, idea with increasing limit was helpful, but it works slow. "Export as csv" option is a must
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
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?
Written by RinatL
2 years ago
2 posts since Tue, 29 May 12
130 000 rows, 6 columns. My network connection is not very fast so direct download to csv file is an only option
Written by CorBlimey
7 months ago
1 posts since Fri, 04 Oct 13
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?
Written by ansgar
7 months ago
4800 posts since Fri, 07 Apr 06
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.