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

Excel-Export NULL as "NULL" String

User, date Message
Written by joeran
1 year ago
Category: Import/Export
4 posts since Sun, 28 Jul 13
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
Written by joeran
1 year ago
4 posts since Sun, 28 Jul 13
This issue was also discussed here https://code.google.com/p/heidisql/issues/detail?id=2733 but I wouldn't consider it "fixed" ;-)
Written by ansgar
1 year ago
4973 posts since Fri, 07 Apr 06
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.
Written by ansgar
1 year ago
4973 posts since Fri, 07 Apr 06
But probably it's still a good idea to introduce a "NULL value: []" option for CSV and Excel exports.
Written by joeran
1 year ago
4 posts since Sun, 28 Jul 13
>>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
Written by ansgar
1 year ago
4973 posts since Fri, 07 Apr 06
r4473 now provides a custom NULL value edit box in the grid export dialog.
Written by joeran
1 year ago
4 posts since Sun, 28 Jul 13
Great, thank you, this helps me a lot!
Written by kps21
1 year ago
2 posts since Fri, 23 Aug 13
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.
Written by ansgar
1 year ago
4973 posts since Fri, 07 Apr 06
\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.
Written by kps21
1 year ago
2 posts since Fri, 23 Aug 13
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.