ANmaSQL_InsertStatement

Creates SQL INSERT statements from a table in Excel.
Used to be executed to move data from Excel to SQL (or MySQL) database.
You need to run the macro while Excel table sheet is open, as always, can refer to workbook, sheet and startup cell in call.
Sheet should have table columns headers as 1st row (refer to attached screenshot).
Additional Sub is provided to save Insert statements (1 per row) into text file.
Edit 2024-01-15: Adding SQLTest2() function to save generated SQL Inserts into text file with support for non-English characters (like Arabic).
Edit 2024-05-25: Adding brackets for column names and fix issue with last comma.

CodeFunctionName
What is this?

Public

Tested

Original Work
Sub TestSQL1()
    ' Use to output into .sql file for large tables
    Open "D:\Docs\Links v2.sql" For Output As #1
    Print #1, ANmaSQL_InsertStatement("ANmaCCLinks")
    Close
End Sub
Sub TestSQL2()
    ' Use to output into .sql file for large tables
    Dim fsT As Object
    Set fsT = CreateObject("ADODB.Stream")
    fsT.Type = 2 'Specify stream type - we want To save text/string data.
    fsT.Charset = "utf-8" 'Specify charset For the source text data.
    fsT.Open 'Open the stream And write binary data To the object
    fsT.WriteText ANmaSQL_InsertStatement("[nesrnet_main].[Alwa7Settings]", , , "C5") ' "Your Arabic Text Here"
    fsT.SaveToFile "D:\Docs\Downloads\Alwa7Settings2024-01-15.sql", 2 'Save binary data To disk
End Sub


Function ANmaSQL_InsertStatement(SQLTableName, Optional Shee = "Active", Optional Wb = "This", Optional StartCell = "A1")
    ' Creates "Insert into " statement for a table found in Excel sheet to be run to upload into DB or as backup of table.
    '
    If Wb = "This" Then Wb = ThisWorkbook.Name
    If Wb = "Active" Then Wb = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
   
    ColumnsCo = Workbooks(Wb).Worksheets(Shee).Range(StartCell).CurrentRegion.Columns.Count
    RowsCo = Workbooks(Wb).Worksheets(Shee).Range(StartCell).CurrentRegion.Rows.Count
   
    Rett = ""
    TabHead = ""
    NewCols = 0
    For I = 0 To ColumnsCo - 1
        If Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(0, I).Value > "" Then
            TabHead = TabHead & IIf(TabHead > "", ", ", "") & "[" & Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(0, I).Value & "]"
            NewCols = NewCols + 1
        End If
    Next
   
    TabInsert = "Insert into " & SQLTableName & "( " & TabHead & ") Values( {{$Row1$}} );"
    For J = 1 To RowsCo - 1
        TabRow = ""
        For I = 0 To NewCols - 1 ' ColumnsCo - 1
            ThisCell = Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(J, I).Value
            If ThisCell = "" Then
                ThisCell = " ''"
            ElseIf Not IsNumeric(ThisCell) Then
                ThisCell = Chr(39) & Replace(ThisCell, "'", "''") & Chr(39)
            End If
            TabRow = TabRow & IIf(TabRow > "", ", ", "") & ThisCell
        Next
       
        Rett = Rett & vbCrLf & Replace(TabInsert, "{{$Row1$}}", TabRow)
    Next
   
    Rett = Rett & vbCrLf & ""
   
ByeBye:
    ANmaSQL_InsertStatement = Rett
End Function

SQLTableName, Optional Shee = "Active", Optional Wb = "This", Optional StartCell = "A1"

Sub TestSQL1()
    ' Use to output into .sql file for large tables
    Open "D:\Docs\Links v2.sql" For Output As #1
    Print #1, ANmaSQL_InsertStatement("ANmaCCLinks")
    Close
End Sub

Views 247

Downloads 95

CodeID
DB ID