October 18, 2011 at 3:35 pm
If I have to do one more replace all "NULL" -> "" in Excel I'm going to scream. There is no time ever I want to export my data so that NULL values are listed as NULL. This messes up formulas, pivot tables, etc. as well as exploding the size of my spreadsheets and confusing my end users.
It seems as if this should be a no brainer setting to find and change but I haven't found it yet and I've been googling for a half hour, and have looked a few times before.
Before you suggest the Isnull(fld,'') option - the tables I deal with often have dozens to hundreds of sparsely populated fields (medical billing). I just want to be able to turn it OFF permanently.
October 19, 2011 at 12:35 pm
Sorry. I don't believe there is a setting anywhere for this. If you are doing this regularly why not do an SSRS report which will treat NULL as empty?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2011 at 2:40 pm
Why not do a replace in the excel sheet itself. Replace all your NULLs with a blank.
October 19, 2011 at 2:45 pm
I haven't really used SSRS (we just upgraded from SQL Server 2000 and didn't have it before) - is it possible to create a generic wrapper report that will take the results of any query and output "NULL" as blanks instead?
In my current position I do more query design and data analysis than programming, so frequently I run a query multiple times in sequence with minor modifications to make sure I have everything correct. When I copy/paste into Excel to view or compare against other results I need it to be quick and correct, because I often do that dozens of times a day.
The thing I still don't understand is why anyone would EVER want SSMS to output Null values as a literal string instead of a blank, especially in numeric fields and dates, it messes up almost everything and doesn't make sense at all.
October 19, 2011 at 2:51 pm
R_S - I have been using the Excel replace method. I just was hoping there was an alternative setting that would make that permanently unnecessary because I've done it hundreds if not thousands of times now. Also when investigating how to do this, I saw people requesting it as early as 5 years ago in SSMS 2005, but couldn't find any posts on how to do it.
In addition, in Excel 2007 sometimes for very large data sets doing a universal replace of NULLs bogs down and can crash Excel, I tried that with a hundred thousand rows x about 300 columns and it took a half hour then crashed. Part of that was probably memory limits - we're still stuck on XP here. Before anyone complains re. 300 columns - I was exploring several large subtables of a very large table using select * to see which if any had the data I needed.
October 19, 2011 at 3:07 pm
October 19, 2011 at 3:52 pm
This tool converts SQL table or SQL query resultset to Excel.
NULL will be replaced by empty string by default.
October 19, 2011 at 6:14 pm
R_S - using IsNull or Coalesce isn't an option, because I regularly deal with hundreds of columns, any of which can have nulls. To change my ad-hoc query to add this is a far less useful solution than global replacing NULL in Excel, and would explode every query in size and unreadability.
Thanks for the link Grasshopper, but Nolan Software the producer of that app appears to be a small shareware company, and we're in a locked down environment dealing with patient confidential information. I could lose my job if I installed something like that on my machine, if I even could (no admin rights on my machine).
The only solution so far that works is to do everything via pass-through queries in Access, which has a useless query editor compared to SSMS, and it's annoying having to switch back and forth, so it's only a "last-resort" choice.
I'm already having to use Access for copy/pasting things into SQL Server because the newer version of SSMS doesn't seem to like being pasted into, especially if carriage returns are embedded in fields.
Again, only thing I'm looking for is something to universally display (or at least copy/paste and/or export) Null values in the SSMS output grid as just blank instead of "NULL", either as a server setting, an SSMS setting, or a pre-query directive of some sort.
October 20, 2011 at 7:00 am
sleipner (10/19/2011)
I haven't really used SSRS (we just upgraded from SQL Server 2000 and didn't have it before) - is it possible to create a generic wrapper report that will take the results of any query and output "NULL" as blanks instead?In my current position I do more query design and data analysis than programming, so frequently I run a query multiple times in sequence with minor modifications to make sure I have everything correct. When I copy/paste into Excel to view or compare against other results I need it to be quick and correct, because I often do that dozens of times a day.
The thing I still don't understand is why anyone would EVER want SSMS to output Null values as a literal string instead of a blank, especially in numeric fields and dates, it messes up almost everything and doesn't make sense at all.
SSRS doesn't require any wrapper at all for this: it will automatically do exactly what it sounds like you want it to do, which is to display NULL as blanks. If you then export from SSRS to Excel, you'll get blank cells in Excel. You can write your SQL stored procedures to retain the NULLs with no special treatment whatsoever.
I hear your frustration, but I can't say I share it. Dumping enormous quantities of data from SSMS into Excel is not a common practice, and I would think it would be faster to do the data comparisons within T-SQL if you could.
The thing is, a zero-length string -- what you call a "blank" -- is not the same thing to SQL server as NULL. Neither is the string "NULL" the same thing as NULL, for that matter, but there has to be some way for the NULL result from SQL to be preserved, displayed, and exported. Any solution can only be approximate, b/c there's no way to express NULL in Excel. The closest thing I can think of is the Excel function =NA(). This works the way NULL does in SQL in that adding a cell containing NA() to a cell containing a value returns NA(). Also, if cells A1 and B1 both contain NA(), entering the formula =A1=B1 into cell C1 will also return NA(), which is again what T-SQL would do.
Rich
October 20, 2011 at 8:45 am
Rather than copy/pasting into excel, why not use Excel's Microsoft Query option and have Excel run the query and do the import?
When that happens Excel will blank out the NULL values automatically.
Just remember to go into Excel->Data->From Other Sources->From Microsoft Query->Pick/Create a data source
When you get to the Query Wizard - Choose Columns, click Cancel
When you get
"Do you want to continue editing this query in Microsoft Query?"
Click Yes
Click Close at Add Tables, then click the SQL button in the tool bar.
Paste your query and click OK, then under File, return data to Microsoft Excel.
And done. 🙂
October 20, 2011 at 11:20 am
rmechaber - thanks, I'll give SSRS a try. I use Excel often as a cross-check on the results, copy the data over then use pivots to validate results. Also we often use it for our reports.
I'm quite familiar with NULL as a concept - Teradata SQL Assistant copy/pastes them correctly (as a blank), as does MS Access and did Query Analyzer in SQL Server 2000. SSMS is the only program I've ever seen where Nulls get interpreted as strings. In Excel, I can't imagine why anyone would ever think of a Null value as anything other than a blank cell, that makes all the various functions work as they would in a database, while "NULL" breaks everything.
mtassin - I suppose I could do that, but since I develop the queries in SSMS it's again adding another layer of work to what should be a simple setting within SSMS. It would be just as easy to run it in Access then copy/paste the results over.
October 20, 2011 at 11:34 am
sleipner (10/20/2011)
rmechaber - thanks, I'll give SSRS a try. I use Excel often as a cross-check on the results, copy the data over then use pivots to validate results. Also we often use it for our reports.I'm quite familiar with NULL as a concept - Teradata SQL Assistant copy/pastes them correctly (as a blank), as does MS Access and did Query Analyzer in SQL Server 2000. SSMS is the only program I've ever seen where Nulls get interpreted as strings. In Excel, I can't imagine why anyone would ever think of a Null value as anything other than a blank cell, that makes all the various functions work as they would in a database, while "NULL" breaks everything.
mtassin - I suppose I could do that, but since I develop the queries in SSMS it's again adding another layer of work to what should be a simple setting within SSMS. It would be just as easy to run it in Access then copy/paste the results over.
You're welcome. If you use Excel a lot for reports, then SSRS really would be a great idea for you. I've been very happy with RS 2008 R2 as a tool for reporting to users. Be warned: there are some finicky bits to formatting reports in RS to get them to display properly when exported to Excel such that they don't wind up with merged cells (which break sorting and formulas). I've usually been able to get this tweaked so that it works OK, but it can be a hassle to set up.
If you want to follow mtassin's suggestion (execute the query from Excel) without a lot of extra work, I have a thought: put the T-SQL in a stored proc. Then with VBA in Excel, call the SP, dump it to a RecordSet and paste into the spreadsheet. If you're doing a bunch of ad hoc testing, all you would need to do is either change a single SP definition, or create a new one and change the name of the called SP in VBA. You'd only be changing the code once, in SSMS. If you do a bunch of generic one-off testing, you could have one ad hoc SP whose definition you change to suit your needs -- the Excel file wouldn't change at all.
Rich
October 20, 2011 at 2:42 pm
When I have queries finalized I do turn them into a view or TSQL, it's just the design process usually pretty complicated and finicky, so I use SSMS as I develop them. Encapsulating it in a stored procedure helps for running it but can make debugging it harder, and I often deal with queries that are over 20,000 characters long. Generally my TSQL ones are shorter than my Teradata ones because I have full access on the SQL Server, but we can't do procedures in Teradata, only (temporary) macros and views, and I'm less familiar with the environment.
We are intending to move to SSRS for at least some of our reporting, possibly the web version of it, which makes distribution easier but security harder. We also have Crystal Reports which (imo) sucks...very powerful but crappy and nonintuitive designer interface, and I avoid it unless I have no other choice. It's possible we may also get access to Cognos, which I heard is powerful, but I'm unfamiliar with it.
For our permanent reports, in the past I often did Excel VBA automation from within Access, makes formatting, chart & pivot table creation, etc a snap. I heard it was possible to do automation from within SQL Server too, but I suspect using SSRS is probably the preferred way to go about doing that.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply