Excel-Export NULL as "NULL" String

[expired user #7178]'s profile image [expired user #7178] posted 11 years ago in Import/Export Permalink
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
[expired user #7178]'s profile image [expired user #7178] posted 11 years ago Permalink
This issue was also discussed here https://code.google.com/p/heidisql/issues/detail?id=2733 but I wouldn't consider it "fixed" ;-)
ansgar's profile image ansgar posted 11 years ago Permalink
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's profile image ansgar posted 11 years ago Permalink
But probably it's still a good idea to introduce a "NULL value: []" option for CSV and Excel exports.
[expired user #7178]'s profile image [expired user #7178] posted 11 years ago Permalink
>>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
Code modification/commit from ansgar.becker, 11 years ago, revision 8.0.0.4473
Provide edit box for custom NULL value, in grid export dialog. See http://www.heidisql.com/forum.php?t=13075
ansgar's profile image ansgar posted 11 years ago Permalink
r4473 now provides a custom NULL value edit box in the grid export dialog.
[expired user #7178]'s profile image [expired user #7178] posted 11 years ago Permalink
Great, thank you, this helps me a lot!
[expired user #7228]'s profile image [expired user #7228] posted 11 years ago Permalink
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's profile image ansgar posted 11 years ago Permalink
\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.
[expired user #7228]'s profile image [expired user #7228] posted 11 years ago Permalink
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.