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.
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.
instead of
I think I’ll make that an option that, if selected by the user, will assume the first row is field names.
Another approach is to use Excel to create a text file of the data and then use the MySQL command ‘LOAD DATA INFILE’
Looks dead post, but nevertheless, is it possible to use Access?