Export of SQL-Data in CSV

Hello everybody,

I would like to know, how it is possible to export my sql data from HeidiSQL into CSV files. I know that it is possible to export the SQL Database (Tools), but how do I manage it for a CSV output?

I think it is really easy but I am just started to work with heidisql and that's why I hope, that somebody can help me with this concern.

Thanks a lot in advance.

RPM on results and click export option. Is that is what you mean?

CSV files are generated in HeidiSQL with a right-click on any data result, plus clicking "Export grid data". Many other output formats can be selected there, amongst CSV.

Thanks a lot for your answers! I already found a way to solve my problem.

I've exported the file but all long IDs converted and ruined: This one: 100001100000026336 converted to: 1.00001E+17 How do I keep the original one?

1.00001E+17 is just a scientific format of the number 100001000000000000.

But the very right digits are cut off, so the numbers are really ruined.

But I'm sure this is not done by HeidiSQL itself, or?

If you open the CSV in a TEXT editor (like NANO / Windows NOTEPAD, NOTEPAD++, etc) - and you see the Scientific Notation - then, it's Heidi - but - I seriously doubt that it is...

If, on the other hand, you are not seeing the Scientific Notation until you open the file in your spreadsheet program - I am going to presume that you are using MS-Excel. Excel takes numbers that it deems long (usually 11+ digits), and converts them - HOWEVER - they only seem to support integer precision of up to 15 digits - so - for something like a Visa card (16 digits), you lose that last digit of precision when converting back to non-scientific notation. Sun's OpenOffice file format supports higher integer precision - but I am not sure how many more digits they support. Since MS is often the "standard" among people you are transferring data with, this means that if the file passes through the hands of anyone using Excel, the value will ALWAYS be ruined - UNLESS - you force MS-Excel to treat it like a string. Their internal notation for this is to prefix the value with a "'" - single quote - people use this all the time for things like US Zip Codes that start with zeroes - e.g. 00103 would convert to 103 in Excel (and most spreadsheet programs), but '00103 just displays as 00103 (or you could format it with leading zeroes - but- the "'" allows you to enter a single cell on the fly without typically having to do anything else).

However, I find that sometimes, the "'" isn't enough when opening up a text file in Excel - so - I do things like - put letters in front - so, instead of 4141555566667777 for a Visa card that would shift to SciNotation, and then become 4141555566667770 when converting back - I would export it as something like "VS:4141555566667777" - which would force Excel to try not to be smarter than I need it to be in this instance. This should also force Heidi to do the same thing - if - the data is "ruined" at time of export - but - again - I find that highly unlikely...

Please note - this is NOT something isolated to text files being converted when opened as a spreadsheet in Excel - if you save an actual XLS / XLSX file with a 16+ digit number - you will LOSE PRECISION...

Not to go off topic, but - MS has done things like increase row count (used to be 16k now it's over 1M), column count (don't know what it was / is - but - they have added more)...and yet - never corrected this - which IS not an issue in other spreadsheet programs (not sure about Google Sheets). One solution might be - if they cannot increase their integer precision - is for them to treat it as text when converting out of scientific notation - but - I have no real knowledge of how complicated that might be for them.

Anyway - hope this helps shed some light on the problem that it sounded like you were having...

I use to use OUTFile, but my new server installation has given my permission hell so I've looked at other methods. When I use HeidiSQL 11 to export a table in .csv by the right click export method above I found in NotePad++ that the output was limited 1000 rows (my table has 20,000 rows).

Is there a method in Heidi that allows .csv export with no row limit?

Alan

The export method is not limited, but the underlying grid loads 1000 rows by default. You can press the "Show all" button on the top right to get all of your 20,000 rows:

Note that this also has a maximum number of 100,000 rows. You can set both values (step/maximum) in Tools > Preferences > Grid formatting.

Thanks for that tip.

I still think a direct table export would be a nice feature. Possibliy with saved presets that select the table and specific the path of export.

Alan

CSV files are generated in HeidiSQL with a right-click on any data result, plus clicking "Export grid data". Many other output formats can be selected there, amongst CSV.

Hi, Whats the target location of the export?

Well you define the location yourself:

Yes, Thanks for your fast response. This helped :).

instead of manually save, is it possible to auto save a file to a specified path?

is it possible to auto save a file to a specified path?

What do you mean by "auto save" ?

is it possible to auto save a file to a specified path?

What do you mean by "auto save" ?

sorry, like run a command within heidisql so the output is saved automatically in a specified folder path.

other than using mysqldump.exe

Ok, that's not doable with Heidi.

Why not mysqldump? It's perfect for such automated commands. Through a crontab or task planner you can even schedule these commands.

Ok, that's not doable with Heidi.

Why not mysqldump? It's perfect for such automated commands. Through a crontab or task planner you can even schedule these commands.

i see..ok tq will explore this!