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).
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
' 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 128
Downloads 48
CodeID
DB ID