By now you may know that I love data connections in Excel. Sometimes I use them for the front-ends in finished projects, but mostly I use them for testing SQL. With its formulas, tables and pivot tables, Excel makes a great test environment for validating SQL results. You can of course just paste query output straight from SQL Server Management Studio or other development environments, but the it doesn’t always format correctly. For instance Varchar ID fields that are all numbers lose leading zeros and dates lose their formats. In my experience those problems don’t happen with data connections
In this post, we’ll start with the basics of a reusable Table/SQL connection to which you can then add your SQL. Then I’ll share some code that lets you point at one or more .sql files and creates a connected table for each one. (An .sql file is just a text file with SQL in it and an .sql extension for handy identification.)
A Reusable Table/SQL Connection
At work I have a default data connection to the main database we query, all set up with the Connection, Command Type and some dummy Command Text. Whenever I want to run some SQL against that database in Excel, I just click on that connection in Data > Existing Connections. If I worked at home and used SQL Server and kept the corporate database on my laptop, the connection could look like this.
I created it by going to Data > Connections > Other Sources > From SQL Server. After following the wizard, I modified the connection by changing the Command Type to SQL and the Command Text to the meaningless, but super-speedy query “SELECT ‘TEMP’ FROM TEMP.”
So now I’ve got a template I can call from Data > Existing Connections and quickly modify the SQL, say to something like:
Inserting SQL Directly From .sql Files
Recently I thought I’d take this a bit further and pull the CommandText directly from an .sql file. So I wrote some code that has you pick one or more .sql files, and then creates a new Worksheet/Table/Query for each one in a new workbook. The main query is below. The heart of it looks a lot like what you got if you ran the macro recorder while creating a new connection:
Sub AddConnectedTables()
Dim wbActive As Excel.Workbook
Dim WorksheetsToDelete As Collection
Dim ws As Excel.Worksheet
Dim qt As Excel.QueryTable
Dim sqlFiles() As String
Dim ConnectionIndex As Long
sqlFiles = PickSqlFiles
If IsArrayEmpty(sqlFiles) Then
Exit Sub
End If
Workbooks.Add
Set wbActive = ActiveWorkbook
'Identify the empty sheet(s) the workbook has on creation, for later deletion
Set WorksheetsToDelete = New Collection
For Each ws In wbActive.Worksheets
WorksheetsToDelete.Add ws
Next ws
For ConnectionIndex = LBound(sqlFiles) To UBound(sqlFiles)
wbActive.Worksheets.Add after:=ActiveSheet
'*** Modify the location below to match your computer ***
Set qt = ActiveSheet.ListObjects.Add(SourceType:=0, _
Source:="ODBC;DSN=Excel Files;DBQ=E:\DOCS\YOURSUMBUDDY\BLOG\POST_72_SQL_IMPORTER\Post72_Data.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", _
Destination:=Range("$A$1")).QueryTable
With qt
'Temporary command text makes the formatting for the real query work
.CommandText = ("SELECT 'TEMP' AS TEMP")
.ListObject.DisplayName = "tbl" & Format(Now(), "yyyyMMddhhmmss") & "_" & ConnectionIndex
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Refresh first with just the template query
.Refresh BackgroundQuery:=False
.CommandText = ReadSqlFile(sqlFiles(ConnectionIndex))
'Refresh again with the new SQL. Doing this in two steps makes the formatting work.
.Refresh BackgroundQuery:=False
.AdjustColumnWidth = False
'Name the just-created connection and table
.ListObject.DisplayName = Replace("tbl" & Mid$(sqlFiles(ConnectionIndex), InStrRev(sqlFiles(ConnectionIndex), Application.PathSeparator) + 1, 99) & Format(Now(), "yyyyMMddhhmmss") & "_" & ConnectionIndex, ".sql", "")
wbActive.Connections(1).Name = .ListObject.DisplayName
End With
Next ConnectionIndex
'Delete the empty sheet(s) the worbook had on creation
Application.DisplayAlerts = False
For Each ws In WorksheetsToDelete
WorksheetsToDelete(1).Delete
Next ws
Application.DisplayAlerts = True
End Sub
Notice that the code refreshes the querytable twice. If I just go straight to the query from the .sql file, I end up with the same type of formatting problem described at the beginning of this post. For example, dates come through without formatting, like 41985. Starting with a dummy query of SELECT ‘TEMP’ AS TEMP, refreshing it, setting the .CommandText to the correct SQL and refreshing again results in correct formatting.
The code also sets .AdjustColumnWidth twice because I like to start with correct column widths and then not have them adjust after that.
You’ll also note that the connection in the code above isn’t to a SQL Server database anymore, but to an Excel workbook. That’s because I created a downloadable folder for you to try this out in, and the easiest data source to include is an Excel workbook. See the end of this post for the link and a few instructions.
(Also as a weird bonus in the code above is something I came up with to delete the one or more vestigial empty worksheets that get created in a situation like this where your creating a new workbook in code.)
Below are the three functions called from the module above. One uses a File Dialog to pick one or more .sql files.
Private Function PickSqlFiles() As String()
Dim fdFileDialog As FileDialog
Dim SelectedItemsCount As Long
Dim sqlFiles() As String
Dim i As Long
Set fdFileDialog = Application.FileDialog(msoFileDialogOpen)
With fdFileDialog
.ButtonName = "Select"
.Filters.Clear
.Filters.Add "SQL Files (*.sql)", "*.sql"
.FilterIndex = 1
.InitialView = msoFileDialogViewDetails
.Title = "Select SQL Files"
.ButtonName = "Select"
.AllowMultiSelect = True
.Show
If .SelectedItems.Count = 0 Then
GoTo Exit_Point
End If
SelectedItemsCount = .SelectedItems.Count
ReDim sqlFiles(1 To SelectedItemsCount)
For i = 1 To SelectedItemsCount
sqlFiles(i) = .SelectedItems(i)
Next i
End With
PickSqlFiles = sqlFiles
Exit_Point:
End Function
This one returns the SQL from the .sql file, so that it can then be stuffed into the QueryTable’s .CommandText property:
Private Function ReadSqlFile(SqlFileFullName As String)
Dim SqlFileLine As String
Dim Sql As String
Open SqlFileFullName For Input As #1
Do Until EOF(1)
Line Input #1, SqlFileLine
Sql = Sql & SqlFileLine & vbNewLine
Loop
'Sql = Input$ '(LOF(#1), #1)
Close #1
ReadSqlFile = Sql
End Function
And this is Chip Pearson’s code for checking if an array, specifically that returned by the PickSqlFiles function, is empty:
Public Function IsArrayEmpty(Arr As Variant) As Boolean
'Chip Pearson
Dim LB As Long
Dim UB As Long
Err.Clear
On Error Resume Next
If IsArray(Arr) = False Then
' we weren't passed an array, return True
IsArrayEmpty = True
End If
UB = UBound(Arr, 1)
If (Err.Number <> 0) Then
IsArrayEmpty = True
Else
Err.Clear
LB = LBound(Arr)
If LB > UB Then
IsArrayEmpty = True
Else
IsArrayEmpty = False
End If
End If
End Function
Download and Instructions
This download marks a new level of complexity, so it’s got instructions.
After you download you’ll need to unzip the folder to wherever you want. It contains five files, the xlsm with the code, the workbook data source and three .sql files with queries against that data source:
There’s further instructions in the xlam file. As noted there, you’ll need to change the path in the VBA to your unzipped folder (technically, you don’t because Excel will prompt you when it can’t find the folder in the VBA, but it will be cooler if you do). There’s a handy Cell formula in the Post72_Import_SQL.xlsm which will give you the correct file path.
Here’s the downloadable folder. Let me know what you think!
