Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Export of SQL-Data in CSV

jk3's profile image jk3 posted 3 years ago in General

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.

Mifczu's profile image Mifczu posted 3 years ago

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

ansgar's profile image ansgar posted 3 years ago

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.

Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.
jk3's profile image jk3 posted 3 years ago

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

ilyar's profile image ilyar posted 5 months ago

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?

ansgar's profile image ansgar posted 5 months ago

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?

VFRDavid's profile image VFRDavid posted 2 months ago

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...

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