Importing SQL Files Into Data Connections

Importing SQL Files Into Data Connections

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.

SQL Server template connection

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:

SQL Server template connection 2

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

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;", _
    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
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.Add "SQL Files (*.sql)", "*.sql"
    .FilterIndex = 1
    .InitialView = msoFileDialogViewDetails
    .Title = "Select SQL Files"
    .ButtonName = "Select"
    .AllowMultiSelect = True
    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

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
'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

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
    LB = LBound(Arr)
    If LB > UB Then
        IsArrayEmpty = True
        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:

Unzipped folder

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!

2 thoughts on “Importing SQL Files Into Data Connections

    • Hey Dick! Good point. I should have said that this tool, and the QueryTable’s CommandText object expect a single SELECT query and nothing more. No compiler statements like NOCOUNT (always makes me think of a rascally varmint) or your USE statement, no Append, Delete or non-Select queries.

      Other than that though, it takes WITH statements, UNIONS, SELECT subqueries, etc.. There is a length limit, but I’ve only hit it once, when I had a WHERE with hundreds of ID’s, because I was testing something.

      At my job the SQL we write is mostly straight SELECT queries. I mainly anticipate using this tool when we do a database upgrade and want to test a bunch of queries to see if they return the same results before and after. I’ll be able to generate before and after workbooks with each sheet/table/query pulled from a sql file. I have other tools that can quickly do a sheet-by-sheet comparison of both workbooks.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>