Excel Macro to Generate SQL Insert

Posted on by BusinessHut

I am in the process of creating a database of County information, and I needed to insert several thousand records. I was able to get the data into Excel, but now I needed to write an SQL Insert statement to get the records into my database. SQL wizards, I’m sure you know a more efficient way to write this INSERT. Actually, what is generated is a series of INSERT statements. If you’d like to share your knowledge, please leave a comment, and I’d gladly update the macro for your improved format.

How it works:

Let’s start by looking at my spreadsheet:

  • I’m using Row 1 as my header.
  • The values are: state, county, statefips, countyfips, and fipsclass
  • Beginning on row 2 I list that information.
  • Columns A, B and E are text.
  • Columns C and D are Integers. ***Note this in the macro when modifying.***


Upon running the macro, it will ask for the start row. Enter the number for the row that begins your data.

It then loops through columns A through E and puts those values into variables.

Then, it writes the insert statement, with our variables included, to the file specified by the “MyFile” variable. Currently, it will be written to the active/default Excel directory.

The macro will then loop down all of the rows until it finds a blank.

This macro is fairly short, so I’m copying it below.
I just realized that there are several things I could change to optimize this code and make it more useful. I’ll try to get an update out soon. Until then, what would YOU change in this code?

Sub generateInsert()

Dim state As String
Dim county As String
Dim statefips As Integer
Dim countyfips As Integer
Dim fipsclass As String
Dim myRow As Integer
Dim myCol As Integer

myRow = InputBox(“What row to START on?”)
myCol = 1
MyFile = “insertStmt.txt”

‘set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

Do Until ActiveSheet.Cells(myRow, myCol) = “” ‘Loop until you find a blank.

state = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
county = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
statefips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
countyfips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
fipsclass = ActiveSheet.Cells(myRow, myCol)

myCol = 1 ‘ Return to column A
myRow = myRow + 1 ‘ Move down 1 row

Print #fnum, “insert into countyTable (state, county, statefips, countyfips, fipsclass)”
Print #fnum, “values (‘” & state & “‘, ‘” & county & “‘,” & statefips & “, ” & countyfips & “, ‘” & fipsclass & “‘);”

Loop

‘ Close the file
Close #fnum

End Sub


I’m looking forward to your comments.

– Jason

9 thoughts on “Excel Macro to Generate SQL Insert”

  1. hello jason, i tried the macro which u mentioned above. its working. now, i have a problem with the same concept. i am trying to write a macro for geting the data from sql data base based on some conditions and storing those data in excel spreed sheet and later on by using excel data we are making dash boards. but i did’nt get the logic and how to implement. please help to write the macro.

    Reply

    2. Here is the code which will do the job for you.

      Sub SQLtoExcel_Data_Import()
      Dim ctn As ADODB.Connection
      Dim rst As ADODB.Recordset
      Dim stSQL As String
      Dim wbBook As Workbook
      Dim wsSheet As Worksheet
      Dim rnStart As Range
      Const stADO As String = “Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _
      “Persist Security Info=False;” & _
      “Initial Catalog=master;” & _
      “Data Source=IANBFS119\SQLEXPRESS” ‘CHANGE THE DATASOURCE
      Set wbBook = ActiveWorkbook
      Set wsSheet = wbBook.Worksheets(1)
      With wsSheet
      .Range(“A1:IV65536”).Clear
      Set rnStart = .Range(“A1”)
      End With
      stSQL = “select * from dbo.spt_values where name like ‘%c'”
      Set ctn = New ADODB.Connection
      With ctn
      .CursorLocation = adUseClient
      .Open stADO
      .CommandTimeout = 0
      Set rst = .Execute(stSQL)
      End With
      rnStart.CopyFromRecordset rst
      RowCount = Application.WorksheetFunction.CountA(Range(“A:A”))
      Formating
      MsgBox RowCount & ” Records Exported!!!”, vbInformation, “User_Info”
      rst.Close
      ctn.Close
      Set rst = Nothing
      Set cnt = Nothing
      End Sub

      Thx
      Yuvi

      Reply

  2. Pavithra,

    There are many ways to get data from SQL into Excel, so I’m afraid I won’t be much help without more detail on your specific situation. However, I would generally not use a macro to get the data from the database. Instead, I’d create a connection using the “From SQL Server” option under the Data tab. (Screenshot: http://screencast.com/t/MTFkODM0N )

    I’d pull that data into a “raw data” sheet and would create my dashboard against that sheet. I find this gives me more options to manipulate my data directly within Excel.

    I hope this helps, but let me know if it doesn’t. If you can provide some more specifics, I’d be happy to follow up.

    Reply

  5. Here is a more generic function to do this.
    There is a section of variable you must edit at the top and everything else is done by the program.
    Enjoy

    Sub generateInsert()

    Dim state As String
    Dim county As String
    Dim statefips As Integer
    Dim countyfips As Integer
    Dim fipsclass As String
    Dim myRow As Integer
    Dim myCol As Integer

    ‘value to edit
    Const numberOfCol = 11
    MyFile = “C:\insert.txt”
    Dim tableName As String
    tableName = “TABLE_NAME”
    Dim colType(1 To numberOfCol) As Integer
    ‘1 if its a number
    colType(1) = 1
    colType(2) = 0
    colType(3) = 0
    colType(4) = 0
    colType(5) = 0
    colType(6) = 0
    colType(7) = 1
    colType(8) = 0
    colType(9) = 0
    colType(10) = 0
    colType(11) = 1
    ‘END value to edit

    myRow = InputBox(“What row to START on?”)

    ‘set and open file for output
    fnum = FreeFile()
    Open MyFile For Output As fnum

    myCol = 1
    Dim nameCol(1 To numberOfCol) As String
    For i = 1 To numberOfCol
    nameCol(i) = ActiveSheet.Cells(1, myCol + i – 1)
    Next i

    myCol = 1
    Do Until ActiveSheet.Cells(myRow, myCol) = “” ‘Loop until you find a blank.

    Dim col(1 To numberOfCol) As String
    For i = 1 To numberOfCol
    col(i) = ActiveSheet.Cells(myRow, myCol + i – 1)
    Next i

    myCol = 1 ‘ Return to column A
    myRow = myRow + 1 ‘ Move down 1 row

    Dim insertCommand As String
    insertCommand = “insert; into; ” & tableName & “(”
    For i = 1 To numberOfCol
    insertCommand = insertCommand & nameCol(i)
    If i numberOfCol Then
    insertCommand = insertCommand & “, ”
    Else
    insertCommand = insertCommand & “);”
    End If
    Next i
    Print #fnum, insertCommand

    Dim valueCommand As String
    valueCommand = “values(”
    For i = 1 To numberOfCol

    If colType(i) = 0 Then ‘is a string
    valueCommand = valueCommand & “‘”
    End If

    valueCommand = valueCommand & col(i)

    If colType(i) = 0 Then ‘is a string
    valueCommand = valueCommand & “‘”
    End If

    If i numberOfCol Then
    valueCommand = valueCommand & “, ”
    Else
    valueCommand = valueCommand & “);”
    End If

    Next i
    Print #fnum, valueCommand

    Loop

    ‘ Close the file
    Close #fnum

    End Sub

    Reply

    1. very nice,just changed a little bit to get no of columns and table name as input from user.

      Sub generatesql()
      numberOfCol = InputBox(“no of columns”)
      numberOfCol = numberOfCol + 0
      Const cols = 30
      MyFile = “D:\DEV\insert.txt”
      Dim tableName As String
      tableName = InputBox(“table name”)
      ‘tableName = “TABLE_NAME”
      Dim colType(1 To cols) As Integer
      ‘1 if its a number
      ‘colType(1) = 1
      ‘colType(2) = 0
      ‘colType(3) = 0
      ‘END value to edit

      myRow = InputBox(“What row to START on?”)

      ‘set and open file for output
      fnum = FreeFile()
      Open MyFile For Output As fnum

      myCol = 1
      For i = 1 To numberOfCol
      colType(i) = ActiveSheet.Cells(2, (myCol + i) – 1)
      Next i

      myCol = 1
      Dim nameCol(1 To cols) As String
      For i = 1 To numberOfCol
      nameCol(i) = ActiveSheet.Cells(1, (myCol + i) – 1)
      Next i

      myCol = 1
      Do Until ActiveSheet.Cells(myRow, myCol) = “” ‘Loop until you find a blank.”

      Dim col(1 To cols) As String
      For i = 1 To numberOfCol
      col(i) = ActiveSheet.Cells(myRow, myCol + i – 1)
      Next i

      myCol = 1 ‘ Return to column A
      myRow = myRow + 1 ‘ Move down 1 row

      Dim insertCommand As String
      insertCommand = “insert into ” & tableName & “(”
      For i = 1 To numberOfCol
      insertCommand = insertCommand & nameCol(i)
      If i numberOfCol Then
      insertCommand = insertCommand & “,”
      Else
      insertCommand = insertCommand & “)”
      End If
      Next i
      Dim sqlCommand As String
      ‘sqlCommand = insertCommand
      ‘Print #fnum, insertCommand

      Dim valueCommand As String
      valueCommand = “values(”
      For i = 1 To numberOfCol

      If colType(i) = 0 Then ‘is a string
      valueCommand = valueCommand & “‘”
      End If

      valueCommand = valueCommand & col(i)

      If colType(i) = 0 Then ‘is a string
      valueCommand = valueCommand & “‘”
      End If

      If i numberOfCol Then
      valueCommand = valueCommand & “, ”
      Else
      valueCommand = valueCommand & “);”
      End If

      Next i
      sqlCommand = insertCommand & ” ” & valueCommand
      Print #fnum, sqlCommand

      Loop

      ‘ Close the file
      Close #fnum

      End Sub

      Reply

Leave a Reply

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