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

Question

Post reply

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

Viewing 4 posts - 46 through 48 (of 48 total)

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

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 1

I hate Excel...If your cutting and pasting into excel from SSMS, you could be losing leading zeros.

Usually the only time I do this is exchanging results among my team.

If I have to extract data and it is an easy one time request I use the SQL Server Import and export wizard...

If this is a repeatable data export or you might have to rerun the processing if the results are getting QAd...SSMS is the way to go as others have mentioned.

danajshields Grasshopper Points: 12 More actions · Answer 2

His point is valid. What it was a query against a customer database and my last name is NULL? Why not just leave the cell empty?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Chris Hurlbut wrote:

I hate Excel...If your cutting and pasting into excel from SSMS, you could be losing leading zeros. Usually the only time I do this is exchanging results among my team. If I have to extract data and it is an easy one time request I use the SQL Server Import and export wizard... If this is a repeatable data export or you might have to rerun the processing if the results are getting QAd...SSMS is the way to go as others have mentioned.

It's nothing to hate Excel for. It's doing exactly what it's told and the leading zero data looks like a number, not a string. SQL Server is also doing exactly what it was designed to do. MS will tell you that you're using the wrong tool and need to use SSIS instead. Yah... not me.

It's been a real long time but, IIRC, if you preface your leading zero strings with a single quote, I think your woes will be over. Of course, if Microsoft would finally realize with TRUE CSV/TSV files are, everyone would be happy. They finally got that word on BULK INSERT in 2017 but, consider that MS couldn't even figure out that 1900 wasn't actually a bloody leap year in Excel, why would anyone expect the interface between Excel and the SQL Server grid to work together. 😀 I haven't looked to figure out if they fixed BCP for extractions from SQL Server for CSV in 2017 either.

Not sure about the single quote leader... might have to be double quotes on both sides. I don't remember which and haven't had to do that type of thing in well over a decade.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kaj SSCommitted Points: 1607 More actions · Answer 4

This is an old thread and the tools are shifting. Today Azure Data Studio is the better tool for this kind of work. It has an export to Excel built in - and nulls show up in Excel as a blank cell. Just click on the export icon on the right side of the datagrid.

At least a "think" it's standard functionality. But I have a few extensions installed, so it might be part of an extention.