Toad World® Forums

Nulls in TDP Exports as Not Null in Excel Instances

Hello,

I noticed that when I am exporting from Toad Data Point (3.7.1.1011 Base Edition) to excel that Null values in Toad were registering at Not Null in Excel. This is a significant issue since users were pivoting and counting off of the data and expected the blank cells the actually be blank.

I tried doing a simple test (select null as test from dual) just to make sure that this was an issue with the export process and the results of this test are consistent.

  • The Trim function in Excel and SQL does not fix the problem
  • Trunc in SQL does fix the problem but cannot be used consistently since some of the fields are datetime
  • Crystal reports correctly recognize and exports the nulls as null to Excel instances
    Is there a setting that I am missing that changes how Nulls get exported?

Thank you


I am adding some words at the bottom because this is being flagged as a duplicate of a question I asked in Toad for Oracle.

Hello Jhampl.

maybe you can try to use NVL function in your statement to handle null expressions.

With regards J.

This might be an Excel issue. I recently had issues with Excel counting cells and treating blank cells as not blank. Also if you put a formula in a cell to trim spaces, copy that formula cell and paste the just the value into another cell the spaces come back. I found this because nesting a trim function inside another function (like value to convert a text number to a real number) does not work as the trim function in Excel is not working a it should.

Can you try our latest Trial or Beta? We are using a different third party component.

I will check if I can try that but it will be up to the security team. Thanks