Copy Data from Excel to SQL Server (or any DBMS) Script to generate Insert Statements in Excel

Unfortunately sometimes copy / paste from Excel into SQL server doesn’t work. Since moving data from Excel into SQL server (or another DBMS) can sometimes prove trickier than it should be I’ve created this handy script to help with just that. To get it to work:
1) Paste it into your excel project as a macro.
2) Select the sheet you want to copy
3) The first row of your sheet must contain the column names of the table to be inserted into.
4) Update the macro to use the correct table (look for TABLE_NAME_HERE)

That’s it! Put your cursor in the method and press F5. Upon completion you’ll get a message box with “complete” in it. Once the file is generated copy the contents of c:\temp\insert.txt into Management Studio / Toad / etc and run it.

Sub GenerateInsertRecords()
'Code thanks to https://anthonystechblog.wordpress.com
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
    Dim i As Integer, ii As Double, iColumn as integer
    Dim str As String, strInsert As String, strComplete As String
    str = ""
    str = "insert into TABLE_NAME_HERE ("
    For i = 1 To 999
        If Len(Cells(1, i)) < 1 Then Exit For
        str = str & Cells(1, i) & ","
    Next i
    str = Left(str, Len(str) - 1)
    str = str & ") values ("
    
    Dim oConvert As Variant
    strInsert = str
    strComplete = ""
    For ii = 2 To 65000
        If Len(Cells(ii, 1)) < 1 Then Exit For
        strComplete = strComplete & strInsert
        str = ""
        For iColumn = 1 To i - 1
            oConvert = getInput(Cells(ii, i))
            str = str & oConvert & ","
        Next iColumn
        str = Left(str, Len(str) - 1)
        str = str & ")"
        strComplete = strComplete & str & vbNewLine
    Next ii
    
    Dim oFS As Scripting.FileSystemObject
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Dim oText As Scripting.TextStream
    Set oText = oFS.OpenTextFile("c:\temp\insert.txt", ForWriting, True)
    oText.Write (strComplete)
    oText.Close
    MsgBox ("complete")
End Sub

Function getInput(theInput As Variant) As Variant
    Dim retval As Variant
    If IsNull(theInput) Then
        retval = "Null"
    ElseIf UCase(theInput) = "NULL" Then
        retval = "Null"
    ElseIf IsNumeric(theInput) Then
        retval = theInput
    Else
        retval = "'" & theInput & "'"
    End If
    getInput = retval
End Function