Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

Macro Excel : Générer les ordres SQL INSERT à partir d'une feuille Excel

Voici le code d'une macro permettant la génération automatique d'ordres SQL INSERT à partir des données d'une feuille excel.

prérequis :

  • La première ligne de données doit contenir le nom des colonnes.
  • Le nom de la feuille doit correspondre au nom de la table


La macro génère un fichier .sql portant le nom de la table cible, contenant les ordres SQL du type INSERT INTO mytable(col1,col2,...) VALUES(val1,val2,...).

Sub generateSQLInsert()
  'Loop on current scheet and generate sql INSERT based on column names as columns an sheet name as table
  ' Will create one statement per line aka  INSERT INTO mytable(col1,col2,...) VALUES(val1,val2,...)
  ' Author: Jean-Baptiste Heren - 29-06-2010 -
  Dim tableName As String 'tablename extracted from first row of each column
  Dim filePath As String
  Dim slashPosition As Integer
  Dim pathOnly As String
  Dim columnsSQL As String ' will contain the insert into table (col1,col2,...) statement
  Dim dataSQL As String    ' will contain the values (val1,val2,...) statement
  Dim separator As String
  Dim myRow As Integer 'Row counter
  Dim myCol As Integer 'Column counter
  'set Tablename from sheet name
  tableName = ActiveSheet.Name
  ' get current excel file Path
  filePath = ThisWorkbook.FullName
  slashPosition = InStrRev(filePath, "\")
  pathOnly = Left(filePath, slashPosition)
  MyFile = pathOnly + tableName + ".sql"
  'get column names from first row
  myColInput = InputBox("What column to START on ?")
  myCol = myColInput
  myRow = 1
  columnsSQL = "INSERT INTO " + tableName + " ("
  separator = " "
  Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop until you find a blank.
    columnsSQL = columnsSQL + separator + Trim(ActiveSheet.Cells(myRow, myCol))
    separator = ", "
    myCol = myCol + 1 ' Move to next column
  columnsSQL = columnsSQL + ") "
  'get column values from second row
  myRow = 2
  myCol = myColInput
  'set and open file for output
  fnum = FreeFile()
  Open MyFile For Output As fnum
  Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on rows until blank.
    dataSQL = "VALUES ("
    separator = " "
      Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on columns until blank.
        dataSQL = dataSQL + separator + "'" + Trim(CStr(ActiveSheet.Cells(myRow, myCol))) + "'"
        separator = ", "
        myCol = myCol + 1
    dataSQL = dataSQL + ");"
    myCol = myColInput ' Return to specifiedcolumn
    myRow = myRow + 1 ' Move to next row
    Print #fnum, columnsSQL + dataSQL
  ' Close the file
  Close #fnum
End Sub

Jean-Baptiste Heren

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Comments are closed

You Might Also Like


Cognos Report : Display a different measure in row for the aggregated column

Following tip is when you use a Relational Datamodel (DMR). In a Cognos Report crosstab, If You need to Display different measure in the aggregated column, you can do it using a simple if-then-else...

Continue reading

Cognos 8 - convert a member unique name from one hierarchy to another

If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once. You can convert the member unique name from one hierarchy to...

Continue reading