Super-fast Excel to SQL Insert Statement Converter Workbook

Most data comes from our business folks in Excel and needs to be in SQL for us to provide the analysis they are typically after.  SQL has some tools to ingest data from Excel into SQL but most of them have proven to be a pain in the butt (data conversions to get unicode and non-unicode columns is at the front of that list).  So a few lines of Excel macro and a few sheets to go with those and I have a simple workbook that generates insert statements for you.  This has, since I threw it together, saved me a pile of time fiddling with getting data where I need it.  It supports options of adding a carriage return between inserts and overriding the default setting to escape quotes if you want.  It sends the resulting SQL statements straight to your clipboard.

You can specify how many columns to pick up, starting at the far left, and how many rows to pick up as well.  I could have made this sense last used on both axes but, well, didn't.  yet.

The file is available for download and is a macro-enabled Excel workbool (.xlsm) with the following hashes:

MD5 : f94307aeb7140a1f0fbe57d5812859de
SHA-1: 49b02668774d006b152621895f5cb1ed7aa7bf65

If that still makes you uncomfortable, below is the code and general layout so you can homebrew your own.

Control tab - Where you set your options.  If you call these tabs something else just change the Const values in the script.

 

Data tab - Not much to it, it will take the first row values and interpret them as the column names on the insert.

Running the sheet against the data in the image just puts the following in your clipboard:

INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Grohl','Dave','Foo Fighters')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Walker','Butch','Butch Walker')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Berg','Moe','TPOH')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Ellison','Jim','Material Issue')

Sub GenerateSql()
Const DataTab = "Data"
Const ControlTab = "Control"

Dim szDbName As String, szTableName As String, szLastCol As String, szLastColumn As String, iLastRow As Integer
Dim iColumnCount As Integer, iRowCount As Integer
Dim rCols As Range, rData As Range, szCols As String, szData As String
Dim szSql As String, szSqlRowPreface As String, szSqlRow As String, szSqlCols As String, szSqlDataRow As String
Dim bSettings_ExtraLines As Boolean, bSettings_NoEscape As Boolean

szDbName = Trim(Sheets(ControlTab).Range("C3").Value)
szTableName = Trim(Sheets(ControlTab).Range("C4").Value)
szLastColumn = Trim(Sheets(ControlTab).Range("C6").Value)
iLastRow = Trim(Sheets(ControlTab).Range("C7").Value)
iColumnCount = 0
iRowCount = 0

' Handle settings
    If Sheets(ControlTab).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
        bSettings_ExtraLines = True
    Else
        bSettings_ExtraLines = False
    End If
    If Sheets(ControlTab).Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
        bSettings_NoEscape = True
    Else
        bSettings_NoEscape = False
    End If

' Build ranges
    szCols = "A1:" & szLastColumn & "1"
    szData = "A2:" & szLastColumn & iLastRow
    Set rCols = Sheets(DataTab).Range(szCols)
    Set rData = Sheets(DataTab).Range(szData)

CopyTextToClipboard ("Error, import failed") ' In case we encounter problems

' Build insert, destination and columns:
    For Each col In rCols.Columns
        szSqlCols = szSqlCols + ("[" & col.Value & "],")
        iColumnCount = iColumnCount + 1
    Next col
    szSqlCols = Left(szSqlCols, Len(szSqlCols) - 1) ' remove trailing comma
    szSqlRowPreface = "INSERT INTO " & szDbName & ".dbo." & szTableName & " (" & szSqlCols & ") VALUES ("

' Build values
    For Each rw In rData.Rows
        iRowCount = iRowCount + 1
        szSqlDataRow = "" ' Reset this for each row
        For Each col In rw.Columns
            szSqlDataRow = szSqlDataRow + ("'" & FormatSql(col.Value, bSettings_NoEscape) & "',")
        Next col
        szSqlDataRow = Left(szSqlDataRow, Len(szSqlDataRow) - 1) ' remove trailing comma
        szSql = szSql + szSqlRowPreface & szSqlDataRow & ")" & vbNewLine
        
        If bSettings_ExtraLines = True Then
            szSql = szSql & vbNewLine
        End If
    Next rw

' Add to clipboard and complete:
CopyTextToClipboard (szSql)
MsgBox ("Completed with " & iColumnCount & " data columns and " & iRowCount & " rows." & vbNewLine & vbNewLine & _
    "Results are in the clipboard.")

End Sub

Function CopyTextToClipboard(ByVal inText As String)
  Dim objClipboard As Object
  Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  objClipboard.SetText inText
  objClipboard.PutInClipboard
  Set objClipboard = Nothing
End Function

Function FormatSql(ByVal inText As String, ByVal skip As Boolean)
    If skip = True Then
        FormatSql = Trim(inText)
    Else
        FormatSql = Trim(Replace(inText, "'", "''"))
    End If
End Function