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

Excel-Export NULL as "NULL" String

joeran posted 1 year ago in Import/Export
I was using HeidiSQL 7.x until yesterday, and whenever I exported a database in which an entry was NULL, the Excel export also had a "NULL" in that place. With HeidiSQL 8 a database NULL will be exported to an empty string "" in Excel. For me, this is really annoying because I have looooots of statistical analyses made in Excel and once in a while I was updating it with the data from my SQL database. Now, I would have to change all my statistical formulas in Excel to adjust to the "" instead of NULL.

Is it possible that you add an option "Export NULL as String" (or even better make the old functionality as default and add the option "Export NULL as empty string").

Best regards
Joeran
joeran posted 1 year ago
This issue was also discussed here https://code.google.com/p/heidisql/issues/detail?id=2733 but I wouldn't consider it "fixed" ;-)
ansgar posted 1 year ago
Having a NULL string in an Excel file is what you want? That's wrong, as it's still a text value, not a real NULL. Excel cannot handle NULL values, neither in its native formats nor in imported CSV files.
ansgar posted 1 year ago
But probably it's still a good idea to introduce a "NULL value: []" option for CSV and Excel exports.
joeran posted 1 year ago
>>Having a NULL string in an Excel file is what you want?

Yes, exactly.

>>That's wrong, as it's still a text value, not a real NULL.
It might be "wrong" but for me it would be helpful anyway, because I would not have to change all my formulas in Excel checking for "NULL" to checking for "". In addition, there should be a distinction between a Null value and an empty string. Right now, with HeidiSQL 8, you could not see any difference in Excel for fields that are NULL in the SQL database and fields that contain an empty string "".

So, an option would be nice allowing the user to specify what HeidiSQL should export if the database field was NULL. Possible options are

- "" (empty string, as HeidiSQL 8 is doing)
- "NULL" (Null string, as HeidoSQL 7 was doing)
- ...?

Best,
Joeran

ansgar posted 1 year ago
r4473 now provides a custom NULL value edit box in the grid export dialog.
joeran posted 1 year ago
Great, thank you, this helps me a lot!
kps21 posted 1 year ago
Help! Now the latest "fixed" version forces me to make a selection for the NULL value when exporting grid rows in Excel CSV. When I paste directly into Excel from the grid, I get crazy characters in my cells like \0 or \t. In the previous version, the cells were blank, which it seems would be preferred.

How do I keep the same behavior as the old versions where NULL values are converted into blank cells. i.e. something where the excel function ISBLANK() will return true.
ansgar posted 1 year ago
\t is the column separator for Excel exports - that should not confuse Excel. And when you blank out the "NULL value:" option, you should not get \0 or so as NULLs.

You could post the copied contents here so I can analyze and probably help you more.
kps21 posted 1 year ago
You hit the nail on the head:

And when you blank out the "NULL value:" option, you should not get \0 or so as NULLs.

I didn't realize that you could leave that entry blank.

Thanks very much.

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