Create INSERT INTO SQL Statements

I had to add about 50 projects to Paragon’s website this week. There was no way that I was going to go through the UI for 50 jobs. It’s just too much work and since it’s web based it’s horribly slow. I decided I would just populate the MySQL tables directly. This sub evolved over the several tables I had to fill.

Sub MakeInsertInto(ByVal sTable As String, _
    ByRef wsh As Worksheet, _
    Optional ByVal bAutoIncrement As Boolean = True, _
    Optional ByVal lFIRSTROW As Long = 1, _
    Optional ByVal sFNAME As String = “tempsql.txt”, _
    Optional ByVal sPath As String = “C:”, _
    Optional ByVal sHeader As String)
   
    Dim rCell As Range
    Dim rRng As Range
    Dim i As Long
    Dim sSQL As String
    Dim lMaxCol As Long
    Dim lFnum As Long
   
    ‘Find the last column
   lMaxCol = wsh.Range(“IV” & lFIRSTROW).End(xlToLeft).Column – 1
   
    ‘Assumes data starts in column A
   Set rRng = wsh.Range(“A” & lFIRSTROW, wsh.Range(“A” & lFIRSTROW).End(xlDown))
   
    ‘Optional header with comment escape characters
   If Len(sHeader) > 0 Then
        sSQL = sSQL & “–“ & vbNewLine
        sSQL = sSQL & “– “ & sHeader & vbNewLine
        sSQL = sSQL & “–“ & vbNewLine
    End If
   
    ‘Loop through the data and create an INSERT INTO statement for each record
   For Each rCell In rRng.Cells
        sSQL = sSQL & “INSERT INTO “ & sTable & ” VALUES (“
        If bAutoIncrement Then
            sSQL = sSQL & “DEFAULT, “
        End If
        For i = 0 To lMaxCol
            If Left(rCell.Offset(0, i).Value, 2) = “!!” Then ‘force a number to be text
               sSQL = sSQL & “‘” & _
                    Replace( _
                        Replace( _
                            Right$(rCell.Offset(0, i).Value, Len(rCell.Offset(0, i).Value) – 2), _
                        “‘”, “””), _
                    “_”, “__”) & “‘, “
            ElseIf IsEmpty(rCell.Offset(0, i).Value) Or Not IsNumeric(rCell.Offset(0, i).Value) Then
                sSQL = sSQL & “‘” & _
                    Replace( _
                        Replace(rCell.Offset(0, i).Value, “‘”, “””), _
                    “_”, “__”) & “‘, “
            Else
                sSQL = sSQL & rCell.Offset(0, i).Value & “, “
            End If
        Next i
        sSQL = Left$(sSQL, Len(sSQL) – 2)
        sSQL = sSQL & “);” & vbNewLine
    Next rCell
   
    ‘Write it to a file
   lFnum = FreeFile
   
    On Error Resume Next
        Kill sPath & “” & sFNAME
    On Error GoTo 0
   
    Open sPath & “” & sFNAME For Output As lFnum
   
    Print #lFnum, sSQL
   
    Close lFnum
   
End Sub
 
Sub makesql()
   
    Dim wsh As Worksheet
    Const sTBL As String = “wp_postmeta”
    Const lFIRSTROW As Long = 10
    Const sFNAME As String = “metasql.txt”
   
    Set wsh = ThisWorkbook.Sheets(“Sheet5”)
   
    MakeInsertInto sTBL, wsh, , lFIRSTROW, sFNAME, , “Import meta data”
   
End Sub

I had a field that was a dollar amount, but the field was text in the MySQL table. I couldn’t figure an easy way to include quotes around that field but not around other numeric fields. I ended up putting two exclamation points as a prefix to any field that looks numeric but needs to be treated as text. Pretty kludgy, but hey, I’m a hack.

I should have passed it a range as an argument which would give me more flexibility instead of passing a worksheet and a starting row. That’s the joy of code that evolves instead of being planned. Also, I need to include an option to write the column names so I can INSERT INTO certain columns instead of the whole table.

INSERT INTO tblname (col2, col4) VALUES (val2, val4)

instead of

INSERT INTO tblname VALUES (DEFAULT, val2, DEFAULT, val4)

I think I’ll make that an option that, if selected by the user, will assume the first row is field names.

Posted in Uncategorized

2 thoughts on “Create INSERT INTO SQL Statements

  1. Another approach is to use Excel to create a text file of the data and then use the MySQL command ‘LOAD DATA INFILE’


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

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