Let’s start with a simple query

It returns 10 rows containing an invoice number and an invoice date. When I hover over InvoiceDate, you can see its data type is date. What you can’t see, but trust me it’s there, is that Invoice is a varchar(50) (that’s a String in VBA parlance).

Copy and Paste

Once I run the query in SSMS, I can F6 down to the Query Grid, Ctrl+A to select everything, and Ctrl+C to copy it (Ctrl+Shift+C to copy the headers too). Then over to Excel where a Ctrl+V finishes the job.

That’s pretty good except for one problem. My string invoice numbers were converted to numbers. I can tell because they’re right-aligned. That’s not a particular problem here because they didn’t happen to have any leading zeros. But if they had, the leading zeros would have been lost.

I could have formatted the column for Text before I pasted.

Those little green triangles means everything is right with the world. My invoice numbers are text and my dates are dates. Percentage of the time I will remember to format the column before I paste: 0%.

External Data Query

If I want my varchars to remain varchars then one option is to use an External Data Query in Excel.

My invoice numbers look good and there’s no green triangles. But something is not quite right with those dates. I’m sure you noticed. I’ll get back to that in a minute. But first, here’s how you create an External Data Query.

Follow the wizard by first connecting to the server.

If you’re using SQL Server Express, you can use the command line to find the names of local servers: sqlcmd -L (that’s a capital L).

I have no idea what that is as I don’t have SQL Server Express on my machine. If you’re using regular old SQL Server then ask your DBA what the server name is. If you are the DBA, then you’re in trouble.

Next I tell it what database I want to use. In this case, I uncheck Connect to a specific table because I want to write a query.

On the last screen, I change the Friendly Name

In the next step, I select which table I want to use. In a previous step, we said don’t connect to a specific table. There are at least two kinds of OLEDB connections to SQL Server data: Table and SQL. If you check the box to connect to a specific table, it creates a Table connection and you get the whole table. If you uncheck the box, you get to pick the table and write a query, as we’ll see in a moment. For my purposes, I’m selected the view I used in the SQL statement at the start of this post.

On the Import Data dialog, you can just hit OK and bring in the whole table. But if you we’re going to do that, you might as well have checked the box a couple of steps ago. Because I want to write a query, I’m going to select Properties here.

On the Definition tab, I change the Command Type from Table to SQL and put my query in the Command Text box.

Click through the rest and you’re home. Wow, that’s a lot of work.

Automating External Data

Most of the queries I want come from one database. So I created this little gem to get me started

This puts a one column, one row External Data query into cell A1 that’s already connected to my favorite database.

I write my query in SSMS, copy the SQL statement, and head over to Excel. From a cell within the External Data Query range, I press Alt+D+D+E to get the Edit OLE DB Query box. From here I can change my Initial Catalog in the Connection in case I’m using a different database and I can paste my SQL statement into Command Text.

That’s-a nice-a doughnut.

Stupid Dates

Using an External Data Query, we solved our strings-that-look-like-numbers conversion problem, but introduced another problem. My dates no longer look like dates. They’re all left justified and weirdly formatted. Also, those strings-that-look-like-dates are pretty tenuous. If you F2 and Enter, you convert them to numbers. So if you’re making something permanent (as opposed to some quick and dirty analysis), you still want to format the column as Text.

The problem with the dates is that SQL Server uses one kind of data format and Excel uses a different, incompatible type. Basically Excel doesn’t know it’s a date. If you copy and paste, the trip through the clipboard forces Excel to analyze the data to determine it’s data type (that’s why my varchars turn to numbers), so the dates get converted because at least they look like dates. But through OLEDB, not so much. The data types stay true throughout the process. Hooray for varchars, too bad for dates.

SQL Server has a ton of date formats (well, five actually). The SMALLDATETIME is a pretty snazzy data type that just so happens to work in Excel.

Oh, if you’ve ever tried to convert an Access database to SQL Server, this date business is old news to you. Just know that you are not alone. Others are hurting too.

I’m not going to dumb down my SQL data types just for Excel. But I am willing to convert.

When I put that into Excel, my dates are dates.

Well, almost. They still need a little formatting love. Now all you have to do is remember to convert all your dates before you bring them over. The good news is that if you forget, you can simply edit the SQL to add the CONVERT function. If you have RedGate SQLPrompt, there’s even better news. I created this snippet.

You select your date field, press and release Ctrl, type xld (that stands for Excel Date) and it converts

to

SQL Prompt

I don’t know how much SQL Prompt costs because my employer pays for it. But I can’t imagine working in SSMS without it. In a recent update, they added a new right-click menu item to the Query Grid right-click menu: Open in Excel

As you might have guessed, this is the best of both worlds. You get varchars and dates acting as they should. You still need a little date formatting.

And that’s everything you ever wanted to know about getting data from SSMS to Excel. And then some.