Excel Macro to Generate SQL Insert

by Jason Green on August 28, 2010 · 9 comments

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 comments… read them below or add one }

pavithra September 22, 2010 at 2:38 am

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

Yuvi February 2, 2011 at 2:05 am

Hey this coule possible in connection stn

Reply

Yuvi February 2, 2011 at 2:08 am

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

Jason Green September 23, 2010 at 10:02 pm

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

Mike September 26, 2010 at 3:58 pm

There is a number of scripts available to perform this data transformation task. The problem with those scripts that all of them database specific and they do not work with textiles

Advanced ETL processor can generate Insert scripts from any data source including text files
http://www.dbsoftlab.com/generating-insert-statements.html

Reply

Generate SQL Script from Excel October 23, 2010 at 11:57 pm

Hey we have started contest to win Querycell excel add-on by which you can Generate SQL Script from Excel on a single click of mouse.

Reply

Martin May 2, 2012 at 8:27 am

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

lalit May 22, 2012 at 2:13 pm

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

Dylan Gonzales March 12, 2014 at 8:08 am

Good to know. We used to use VB 6 standalone to process our Excel into SQL.

Reply

Leave a Comment

Previous post:

Next post: