VBA code that loops through an Excel table and generates SQL INSERT statements based on the data

ExportTableToSQL.bas

Sub ExportTableToSQL()

    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    Dim wsName As String
    wsName = "your_worksheet_name"  'Replace with your sheet name
    Set ws = wb.Sheets(wsName) 
    ws.Select
    
    Dim lastRow As Long
    Dim anchorColumn As String
    anchorColumn = "B"   'Define column that has full value (without blank cells)
    lastRow = ws.Cells(ws.Rows.Count, anchorColumn).End(xlUp).row
    
    Dim lastCol As Long
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim tableName As String
    tableName = "your_table_name"  'Define your sql table to insert
    
    Dim rangeName As String
    rangeName = "your_defined_range" 'add defined range for scope of data that wants to process
    
    Dim numberTypeColumns As Variant
    numberTypeColumns = Array() 'Define here for column that is not string (value should be unquoted). Put column number in array.
    
    Dim outputText As String
    Dim i As Long
    Dim j As Long
    
    Dim startRow As Long
    Dim startCol As Long
    startRow = 2 'Assuming the first row contains headers, so starting from row 2
    startCol = 1 'Define here for data to insert start from which column
    
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\sql\output_" & FormattedDateTime() & ".sql"
    
    Open fileName For Output As #1
    
    Dim sqlStatement As String
    For i = startRow To lastRow
        sqlStatement = "INSERT INTO `" & tableName & "` VALUES ("
        For j = startCol To lastCol
            If IsEmpty(ws.Range(rangeName).Cells(i, j).Value) Then
                sqlStatement = sqlStatement & "NULL"
            ElseIf IsInArray(j, numberTypeColumns) Then
                sqlStatement = sqlStatement & ws.Range(rangeName).Cells(i, j).Value
            Else
                sqlStatement = sqlStatement & "'" & ws.Range(rangeName).Cells(i, j).Value & "'"
            End If
            
            If j < lastCol Then
                sqlStatement = sqlStatement & ", "
            End If
        Next j
        sqlStatement = sqlStatement & ")"
        Print #1, sqlStatement
    Next i
    
    Close #1
    
    MsgBox "SQL statements have been exported to " & fileName

End Sub


Function FormattedDateTime() As String
    Dim formattedDate As String
    
    ' Get current date and time
    Dim currentDate As Date
    currentDate = Now()
    
    ' Format date as "YYYYMMDDHHMMSS"
    formattedDate = Format(currentDate, "YYYYMMDDHHMMSS")
    
    ' Return formatted date as plain string
    FormattedDateTime = formattedDate
End Function

Function IsInArray(valueToFind As Variant, arr As Variant) As Boolean
    Dim element As Variant
    
    ' Loop through each element in the array
    For Each element In arr
        ' Check if value exists in the array
        If element = valueToFind Then
            IsInArray = True
            Exit Function
        End If
    Next element
    
    ' Value not found in the array
    IsInArray = False
End Function