Category: External Data

Getting SQL Server Data into Excel

Posted on by Dick Kusleika

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.

Editing SQL Statements in External Data Queries

Posted on by Dick Kusleika

Surprisingly, I’ve been using the

macro from this post quite a bit. SendKeys is dangerous, as I’ve said, but I like to live on the edge. Jan Karel commented that I should use Alt-DDE, which gives me the Command Text box to edit the SQL query, but doesn’t give me the opportunity to change the name of the Connection. As I thought about it more, changing the Connection name happens one time and isn’t really the major source of my frustration. In fact, if I were a little more disciplined I could change the name when I setup the Connection in the Friendly Name box.

Then it’s settled. I’ll use Alt-DDE to edit the SQL and I’ll force myself to set the name when I set it up. But wait. One of the things I was really looking forward to in building my own Command Text box was making it bigger by default so I could see the whole SQL string (or at least most of it). The Alt-DDE textbox is only slightly better than the Connection properties Command Text textbox. See for yourself.


That’s a crappy UI. And that’s from someone who spends a lot of time in the Visual Basic Editor.

Then it’s settled. I’ll build my own form for changing the properties I want to change. It’s what I really wanted to do anyway, so why stop lying to myself. What kind of features should I build into this UI? A big textbox is a must. Also, I’d like to be able to add white space and line breaks. Oh, and if I could have SQL parsing, autoformatting, and autocomplete… So basically what I want is SQL Server Management Studio. I already have that. It’s called SQL Server Management Studio. That lead me to my next bit of genius. If I want to edit the SQL, even only a little, I should do it in SSMS. I added a couple of buttons to the Ribbon.

The Copy button copies the SQL to the clipboard, ready for me to paste into SSMS.

I leave the button enabled and check to make sure a QueryTable exists in the procedure. If I wanted to enable/disable the button, I would need to run a SelectionChange event constantly. I didn’t test it, but it seems like too much overhead. The Paste button looks like this

I added one little safety step in here because I know how I am. I take what’s in the clipboard and insert it into the

property. But I put the previous

in the Clipboard when I’m done. That way, when I get distracted and accidentally put something else in the Clipboard before I paste, I can (relatively) easily revert back to what it was.

I’ll give this a try and see how it goes.

One unsolicited plug: I use Red Gate’s SQL Prompt in SSMS. I can’t imaging having to work in SSMS without it. It’s pricey, but if you’re spending any time in SSMS, you should give it a try.

Connection Properties of External Data Ranges

Posted on by Dick Kusleika

I have a workbook with several connections to SQL Server. When I need to change the SQL statement, I do that in Connection Properties.

I added a command to the QAT to show the connection properties dialog, but there’s something I don’t like about it. If I’m in a table with a connection, it’s pretty likely that I want to see the properties of that particular connection and not just a list of all connections. Of course I’m awesome at naming my connections so I don’t have to guess which is which, but if you weren’t so awesome you might have trouble distinguishing them.

The long-term answer is to write my own interface to change the things I want to change. But in the mean time, I want to open the connections dialog and highlight the connection related to the table I’m in, if any.

When I open the Connections dialog, I can start typing the name of the connection to get down to it. For example, I could start typing “dup” and it will highlight the first connection that starts with those keys.

With SendKeys, I can type the entire name. First I see if the ActiveCell is in a QueryTable. If it’s not, I just open the dialog. If it is, I open the dialog, wait a couple seconds, then send all the keys in the connection’s name. SendKeys can be very dangerous, but we’re just experimenting here.

What the above code actually does is open the Connections dialog, wait for it to close, then send all those keystrokes into the ActiveCell. Dangerous. And not helpful. Apparently the Connections dialog is modal and all code is suspended until it’s closed. I did a little searching and found this command, which does not help.

Maybe the old CommandBars behave differently than the Ribbon.

Nope. Same as ExecuteMso. One last try. This opens the dialog with SendKeys.

And it works. For some reason sending Alt+A+O opens the Connections dialog modeless, the SendKeys executes, and takes me to the “active” connection. I have a couple of applications on my machine that like to steal the focus, so I try to avoid SendKeys whenever I can (which is always). In this code, I’m using it twice, so I won’t be using it all. Interesting, though, that it seems to be the only way to get what I want.

Along the way, I discovered I could get to the “active” connection’s property sheet with this key sequence:

  1. right-click key
  2. b
  3. a
  4. tab
  5. tab
  6. enter

I guess that will work. It’s a lot of keystrokes, though.

Avoiding Date Conversion When Pasting an HTML Table

Posted on by Dick Kusleika

From Get Data From a Website…, MD comments:

However, the info in the table I copy is formatted ## / ## whereby # represents a number.
So when it tries to paste 10 / 10 in the worksheet for example, it auto-changes to 10/okt in my sheet.

This is a common problem that’s hard to solve. There’s no setting I can find in Excel that tells it to stop converting things that look like dates into dates. Since I’m reading in the data and putting it in the clipboard, I can just message the data before I do it. That’s not so easy when you just want to copy and paste, but if you’re using code, you may find the technique useful.

In the above linked post, I automated Internet Explorer to login to a website. I don’t automate Internet Explorer any more, preferring XML instead. But it doesn’t matter which you use. It all ends up in an HTMLDocument, so it’s the same from there. Also, this example doesn’t log into a webpage. It uses Contextures’ Sample Data.

Sub GetTableNoDateConversion()

Dim xHttp As MSXML2.XMLHTTP
Dim hDoc As MSHTML.HTMLDocument
Dim hTable As MSHTML.HTMLTable
Dim hCell As MSHTML.HTMLTableCell
Dim doClip As MSForms.DataObject

'Get the webpage
Set xHttp = New MSXML2.XMLHTTP
xHttp.Open "GET", "http://www.contextures.com/xlSampleData01.html"
xHttp.send

'Wait for it to load
Do: DoEvents: Loop Until xHttp.readyState = 4

'Put it in a document
Set hDoc = New MSHTML.HTMLDocument
hDoc.body.innerHTML = xHttp.responseText

'Find the third table
Set hTable = hDoc.getElementsByTagName("table").Item(2)

'Fix anything that looks like a date
For Each hCell In hTable.Cells
If IsDate(hCell.innerText) Then
hCell.innerText = "'" & hCell.innerText
End If
Next hCell

'put it in the clipboard
Set doClip = New MSForms.DataObject
doClip.SetText "" & hTable.outerHTML & ""
doClip.PutInClipboard

'paste it to the sheet
Sheet1.Select
Sheet1.Range("A1").Select
Sheet1.PasteSpecial "Unicode Text"

'Make the leading apostrophes go away
Sheet1.Range("A1").CurrentRegion.Value = Sheet1.Range("A1").CurrentRegion.Value

End Sub

Once I get the table into an HTMLTable object, I loop through all the HTMLTableCells to see if any of them looks like a date. If so, I put an apostrophe in front of it. The apostrophe is the Excel way to say “No matter what I type next, assume it’s text”. Except when you’re pasting special as Unicode Text. In that case, it doesn’t hide the apostrophe like it should. So the last line of the code is the equivalent of pressing F2 and Enter all the say down the column and forcing Excel to reevaluate its decision to ignore that apostrophe.

This Database Has Been Opened Read-only when Used in External Data Table

Posted on by Dick Kusleika

If you use the From Access button on the Data Ribbon to create External Data, it uses OLEDB to link the data. In the bad ol’ days it used ODBC. Almost always when I bring data from Access to Excel, I need to open Access to fix the query or do something different. If the Excel workbook is still open, I get this unfriendly message:

This database has been opened read-only. You can only change data in linked tables. To make design changes, save a copy of the database

For some reason, the connection string created by Excel includes Mode=Share Deny Write and that causes the read-only message.

As far as I know, Excel can’t write data back to Access via the user-interface. I’d be happy to be proven wrong on that point – and very surprised. So why not just make the connection string read-only?

Fortunately, you can edit the connection string right there in the Connection Properties dialog. I changed the Mode to Mode=Read, and it fixes the problem.

If the database is open in Access at the time I create the external data table, it prompts me for data link properties. On the Advanced tab, I can change the mode. So I guess that’s easier than going through the connection properties after the fact.

Sadly, there’s no specific event for adding an external data table. If there were, I could change that setting for every new table added. I could use the Worksheet_Change event, but I don’t want that code running for every change in every worksheet. That would be crazy. So I’m stuck fixing it manually. And by manually, I mean clicking a button.

If the active cell is in a listobject that has external data, assume that’s the one to change. If not, assume we want to change the first one on the active sheet. If neither of those things work, don’t do anything, otherwise replace the Mode section of the connection string. Here’s the workflow:

  1. Create an external data table
  2. Open the database in Access
  3. Curse yourself for forgetting to change the connection string
  4. Run the above macro
  5. Re-open the database in Access

Get a Table from a Web Page with an XML Request

Posted on by Dick Kusleika

I’m tired of trying to remember the library names and the syntax for doing this. I’m putting here so when I search for XMLRequest, Web Request, HTTPRequest, I can find it.

Sub GetData()

Dim oHttp As MSXML2.XMLHTTP
Dim sHtml As String
Dim hDoc As HTMLDocument
Dim hTable As HTMLTable
Dim hRow As HTMLTableRow
Dim hCell As HTMLTableCell
Dim rStart As Range

Const sURL As String = "http://www.contextures.com/xlsampledata01.html"

Set oHttp = New MSXML2.XMLHTTP
Set hDoc = New HTMLDocument
Set rStart = Sheet1.Range("A1")

'Send the web request
oHttp.Open "GET", sURL
oHttp.send

'Give it enough time to process
Do
DoEvents
Loop Until oHttp.readyState = 4

'put the web page into an HTML Document
hDoc.body.innerHTML = oHttp.responseText

'Find the right table and write it to a sheet
For Each hTable In hDoc.all.tags("TABLE")
If hTable.Rows(0).Cells(0).innerText = "OrderDate" Then
For Each hRow In hTable.Rows
For Each hCell In hRow.Cells
rStart.Offset(hRow.RowIndex, hCell.cellIndex).Value = hCell.innerText
Next hCell
Next hRow
End If
Next hTable

End Sub

It sends a request to the sample data page over at Contextures, jams that into an HTML Doc, then writes the table out to a sheet.