Excel Macro to Generate SQL Insert

Comments

9 responses to “Excel Macro to Generate SQL Insert”

1. 

   pavithra

   September 22, 2010

   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
   1. 

      Yuvi

      February 2, 2011

      Hey this coule possible in connection stn

      Reply
   2. 

      Yuvi

      February 2, 2011

      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. 

   Jason Green

   September 23, 2010

   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
3. 

   Mike

   September 26, 2010

   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
4. 

   Generate SQL Script from Excel

   October 23, 2010

   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
5. 

   Martin

   May 2, 2012

   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. 

      lalit

      May 22, 2012

      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
6. 

   Dylan Gonzales

   March 12, 2014

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

   Reply