Add the option to replace NULL values for strings as empty strings when using 'Open in Excel'
When using the new feature 'Open in Excel' (the results from the grid), it'd be handy to have an option to replace NULL string values with empty strings ''. Sure you could do ISNULL on all the columns that are a string data type, but it'd be handy to just have the option to have the 'Open in Excel' feature do it.
The only way I've found to quickly do it is to export the data as a csv and use a powershell script with something like:
Export-CSV -NoTypeInformation -Encoding "Unicode" -Delimiter "," $AttachmentPath
(Get-Content $AttachmentPath) -replace "`0", "" | Set-Content $AttachmentPath
Which is less than ideal. I was actually surprised Microsoft didn't put in the option themselves.
SQL Prompt 7.2 now exports NULL values as empty cells when using Open in Excel. You can download the latest version from http://www.red-gate.com/products/sql-development/sql-prompt/
I have an issue that the null values result in fact into a non blank cell. IsBlank(cellReference) results in false; cellReference="" results in true. This causes an issue in calculating with these cells; calculations result in #VALUE. If you clear the cell, the calculation works as default
Ron Isaac commented
It'd be great to make it an option. I liked seeing the difference between NULL and blank
Eric Forden commented
Is there a setting somewhere that can be toggled to adjust this behavior? In my case, I want to export empty cells as "NULL" instead of an empty cell.