Copying from SQLSMS grid, pasting in Excel, the word "NULL" is pasted!

  • When I copy/paste grid results from SQL Server Management Studio --> an Excel file, the WORD "NULL", the actual letters "N" "U" "L" and "L" are pasted into the cell for all NULL values.

    Dear geniuses at Microsoft: A NULL value does not equal the string "NULL" !!!!

    Those are two completely different things. Making users to a find/replace post-paste is ridiculous. Making us encapsulate every single field separately in our select with an ISNULL(Field,'') is ridiculous. What if a user has a SELECT * which contains hundreds of columns that they want to copy/paste into Excel?

    By now, I should not be surprised when a Microsoft product does something silly like this, but for the life of me, I cannot believe this is the native behavior of SQL Server Management Studio.

    Please, somebody, tell me there is an option or setting in SQLSMS that I am missing to prevent this very silly behavior.

  • A find/replace isn't that much work now is it? 🙂

    Presumably it's to maintain the difference between an empty string and NULL.

  • iglinka (3/5/2013)


    Dear geniuses at Microsoft: A NULL value does not equal the string "NULL" !!!!

    Those are two completely different things. Making users to a find/replace post-paste is ridiculous. Making us encapsulate every single field separately in our select with an ISNULL(Field,'') is ridiculous. What if a user has a SELECT * which contains hundreds of columns that they want to copy/paste into Excel?

    Your own complain doesn't hold much water here. A NULL also does not equal an empty string. Your ISNULL code is replacing NULL with ''.

    Imagine how completely ridiculous it would be if you actually wanted to know the difference between '' and NULL. How would you propose that be done? Excel doesn't have a DBNULL object you can insert into a cell or anything like that.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • If I'm not mistaken, SSMS shows NULL values with a yellow background to differentiate them from the rest of the result set. I personally think it's a good approach. The difference is that SQL Server is a database and Excel is a spreadsheet and they're different. If you want to make your query so it can be copied and pasted into Excel, try this:

    SELECT ID, ISNULL(string_field, '') string_to_paste

    FROM table_name

    ORDER BY ID;

    Sean brings up an excellent point about comparing NULLs to the value NULL. What a nightmare that would be.

  • iglinka (3/5/2013)


    What if a user has a SELECT * which contains hundreds of columns that they want to copy/paste into Excel?

    Redesign the table?

    My experience has been that a table with hundreds of columns is usually poorly normalized.:crazy:

    But seriously, how would SQL Server know the target of a copy and paste? It would have to know ahead of time that you were pasting into Excel in order to perform the appropriate translation. I suppose MS could add a right click "copy for Excel" option. And another for Access. And maybe another for a table in Word.


    Bob
    SuccessWare Software

  • I have had to deal with this constantly - SSMS 2000 did not do this, SSMS 2008R2 does. At least give me an option I can set to have nulls show up as blanks!

    I'm a senior data consultant who works with a healthcare database with over 11,000 tables, many of which have 100+ fields in them, often blank. When I'm investigating a new question, I often don't know which fields have data in them, or which that do are of interest, so I have to pull in hundreds of fields potentially from many tables as my starting point, and often pull in tens or hundreds of thousands of records. Doing a replace of NULL to blank on that can crash my system (as it is currently with the project I'm doing now), and replacing select * from infotable with an itemized list of a hundred+ isnull functions (potentially dozens of times a month) is not an option.

    Even when you save to a text file it keeps the ridiculous "NULL" in it, and there's no easy way to get rid of it. As someone mentioned above, knowing when a value is a NULL versus a blank string is an important question - to a DBA. To a data analyst it is largely irrelevant except in a few rare circumstances.

    Finally - it would be great if someone actually tried to answer the person who's asking a legitimate question rather than just saying his question is invalid because you personally don't need to do what he's trying to do or think that his problem is important.

  • sleipner (7/1/2014)


    Finally - it would be great if someone actually tried to answer the person who's asking a legitimate question rather than just saying his question is invalid because you personally don't need to do what he's trying to do or think that his problem is important.

    I have read this thread several times and nobody here said the question is invalid. The OP posted the only way that I know of to make this work in their original post.

    It would be nice if you could have an option to treat a NULL like an empty string but currently there isn't an option for that. Have you considered turning your data access the other way? Create the query in Excel and pull the data from that side? If nothing else you could toss some VBA at it so it can deal with the string NULL easily enough.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I have a data massage macro in EditPlus exactly for this situation, where i replace \tNULL\t and \tNULL\n and also \nNULL\t with an empty string;

    then i can copy and paste it with empty strings in place;

    so yea, i copy form SSMS to Editplus, click a button,a nd copy and paste to excel;

    not that difficult; it's no different from having to replace CrLf with CrLf <br />

    if i was pasting into an html page; same issue, jsut different destination rules

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It is just incredibly frustrating the number of days worth of work I've spent doing copy/replace on "NULL" strings over the last 4 years because of this design flaw. You'd think someone at Microsoft would have realized the problem and fixed it by now - because I do truly see it as a bug.

  • The reason behind this problem is that the results grid is not intended to be copied to Excel. For a huge amount of data, you could get out of memory.

    You could either connect to SQL Server through Excel (which will return you a single table or view), you could have an intermediate step such as creating a file with bcp, use VBA as Sean suggested, or you could simply create an SSIS package if it's going to be a recurring operation.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • It's recurring - but it's different every time, so there's no easy way to construct a workaround. It's hard to believe that everyone who uses this hasn't been screaming bloody murder about it for years. Saving it to a text file has the same problem, and if I want to pull it in from Excel I have to deal with connect strings and code, not to mention CopyFromRecordset limitations...

  • Besides which - if the results grid isn't designed for copy/paste to Excel or other programs - what the heck is it actually for? Who runs a query and then just looks at the results in SSMS grid? No one. They copy it into Excel then email it to whomever they built the report for.

  • sleipner (7/1/2014)


    Besides which - if the results grid isn't designed for copy/paste to Excel or other programs - what the heck is it actually for? Who runs a query and then just looks at the results in SSMS grid? No one. They copy it into Excel then email it to whomever they built the report for.

    What I like to do is use some SQL to help out with some of that repetitive COALESCE(COLUMN,'') or ISNULL stuff, since the catalog gives you a list of columns, you can develop queries that make short work of this sort of thing, regardless of the size of the queries.

    Not trying to downplay your gripe though, but I've found several flakey issues with pasting from SSMS, like when I paste dates into a "general" excel column I pretty much get trash unless I take care to do workarounds, I just think that lately Microsoft has been struggling with producing software and I think we just have to put up with their mess, I think its problems leaking from the Windows 8 crew to the rest of the company :hehe:

  • To give you an idea of the data volumes I deal with regularly - I'm looking at one day of lab orders. There's 776,000 records, and just one of the 7 tables I'm dealing with has 107 fields, and I'd estimate that about 75% of the data consists of nulls. I've crashed Excel several times today trying to do the copy/replace on Nulls 🙁

  • sleipner (7/1/2014)


    Besides which - if the results grid isn't designed for copy/paste to Excel or other programs - what the heck is it actually for?

    It's used to review the results from the query, nothing more.

    sleipner (7/1/2014)


    Who runs a query and then just looks at the results in SSMS grid?

    Developers and DBAs. A query is just a part of a solution.

    sleipner (7/1/2014)


    They copy it into Excel then email it to whomever they built the report for.

    If it's a report, it should be available in a front end for the user to avoid additional work for DBAs or DBDs.

    The bcp utility won't include NULLs and will leave an empty field. Connections to SQL Server from Excel aren't hard to configure, the wizard is really clear and a single connection can work for any table on the DB.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply