During Open in Excel, how do I keep NULL as the value instead of blank

Ron_Isaac

steps to recreate:

1.  perform a query that has a few NULLs in the returned set

2.  copy and paste to excel

3.  Notice that NULL is still NULL

4   Use "Open in Excel"

5. Notice that NULL is blank

How can I control that so NULL can be NULL?

KarlB

Hi Ron,

Ah OK, understood. I've spent further time using the function you are utilizing, 'Open in Excel' and can replicate the same behavior as you. This is due to how Excel treats no values and unfortunately there isn't currently any setting in Prompt that when exporting into Excel would replace values, or no values. 

Ironically we had the opposing idea raised previously which is why it currently exports as no value. It has been raised on our User Voice for it to be a toggle option - I would recommend lending your support to it here as the greater the demand the higher chance of development getting reviewed! Click Here

KarlB

Hi Ron,

I've attempted to recreate this and can see 2 types of behavior when copying and pasting into Excel.

NULL
Where NULL is the value within the table, copying this into Excel the behavior pastes NULL into the Excel fields.

Blank
Where the value in the table is blank, ie no value, coping this into Excel the behavior pastes no data into the cell.

This is the expected behavior of Excel and how it handles blank data.

Do you have further detail about the example you are using and any screen shot which may help, in case I am looking into the wrong area and misunderstanding your query.

Ron_Isaac

Hi Karl,

I cannot use copy and paste, because rows with embedded carriage returns or tabs (not sure what type of whitespace) will break my rows and columns (a new row will start randomly)

So I started using the "Open in Excel" feature of SQL Prompt.

It turns NULLs to blanks (not a big deal, but a surprise)

Ron_Isaac

Thanks for the help!

I've also added a comment as you suggest.

Great support!!