Macro Excel : Générer les ordres SQL INSERT à partir d'une feuille Excel
Par jb le mardi 29 juin 2010, 16:57 - Décisionnel - Lien permanent
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
Résultat:
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 - http://blog.jbheren.com
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
Loop
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
Loop
dataSQL = dataSQL + ");"
myCol = myColInput ' Return to specifiedcolumn
myRow = myRow + 1 ' Move to next row
Print #fnum, columnsSQL + dataSQL
Loop
' Close the file
Close #fnum
End Sub