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).

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 = ""
    For I = 0 To ColumnsCo - 1
        If TabHead > "" Then TabHead = TabHead & ", "
        TabHead = TabHead & Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(0, I).Value
    Next
   
    TabInsert = "Insert into " & SQLTableName & "( " & TabHead & ") Values( {{$Row1$}} );"
    For J = 1 To RowsCo - 1
        TabRow = ""
        For I = 0 To ColumnsCo - 1
            ThisCell = Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(J, I).Value
            If Not IsNumeric(ThisCell) Then
                ThisCell = Chr(39) & Replace(ThisCell, "'", "''") & Chr(39)
            End If
            If TabRow > "" Then TabRow = TabRow & ", "
            TabRow = 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"

Views 118

Downloads 37

CodeID
DB ID

ANmarAmdeen
602
Attachments
Revisions

v2.0

Monday
January
15
2024