Create INSERT statements from Excel
-
Comments posted to this topic are about the item Create INSERT statements from Excel
-
Hello! I too used to use Insert statements to get my data from Excel into SQL, however, I have had to move away from this process (to using the Import function in SQL) because my data has single quotation marks in it. For example: "Bob's Auto Shop". Using the Insert process creates an unusable SQL script. Would you happen to know a way around this? Note: Removing the single quote is not an option.
Thank you,
Sophie
-
Adding :
File = InputBox("Enter file destination like C:\\filename.sql", "Filename", "c:\\InsertScript.sql")will give you the option of saving somewhere else as the default setting.
I haven't found a way of picking up the formatting of the column to eliminate the ' around numbers etc.
-
To include single quotation marks in the text of an insert statement, you need to replace the single quotation mark with two single quotation marks. (NOT a double quote - thats a single charactor, but two single quotes with no seperation)
The following
INSERT INTO [BASE_TABLE]([C1]) VALUES('Joe''s Bar')will work. Add code to the macro to replace ' with '' as you build the insert statement and you should be back in business
-
The script shown is really nice, rather the author has shown a generic approach on how we can build tools to make our lives easier!!!
Nice script, keep posting. 🙂
Regards,
Sriram
Sriram
-
Thank you to all those that replied. The single quote twice works the best in my particular environment. Perfect!!
Thanks again!
Sophie
-
I too liked this script 🙂
I modified it a little so that any single quotes are replaced, the results are put into the last column and it prompts you for the table name, for when you forget to change the name of the sheet. 😉
As ever when looking a utility macros, I always like to mention it's worth storing them in your PERSONAL.xls, so that they are available in any worksheet, just in case someone reading this doesn't know about that handy feature.
Well done Arun
HTH
Dave Jackson
Sub CreateInsertScript()
Dim Row As Integer
Dim Col As Integer
'To store all the columns available in the current active sheet
Dim ColNames(100) As String
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1
Dim LastRow As Long
Dim LastCol As Integer
Dim S As Worksheet
Set S = ActiveSheet
LastRow = S.UsedRange.Rows.Count
LastCol = S.UsedRange.Columns.Count
'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.", , 2)
Dim MaxRow As Integer
MaxRow = InputBox("Give the Maximum Row No.", , LastRow)
Dim tableName As String
tableName = ActiveSheet.Name
tableName = InputBox("Give the Table name.", , tableName)
Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable to store partial statement
Do While Row <= MaxRow
StringStore = ""
CellColCount = 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "
Do While CellColCount <= ColCount
StringStore = StringStore + ColNames(CellColCount)
'To avoid "," after last column
If CellColCount <> ColCount Then
StringStore = StringStore + " , "
End If
CellColCount = CellColCount + 1
Loop
StringStore = StringStore + " ) values("
CellColCount = 0
Do While CellColCount <= ColCount
StringStore = StringStore + " '" + Replace(CStr(ActiveSheet.Cells(Row, CellColCount + 1)), "'", "''") + "'"
If CellColCount <> ColCount Then
StringStore = StringStore + ", "
End If
CellColCount = CellColCount + 1
Loop
'Update the last column with the statement
Cells(Row, LastCol + 1).Value = StringStore & ");"
Row = Row + 1
Loop
MsgBox ("Successfully Done")
End Sub
-
If you want to save the INSERT script in the same location where the spreadsheet is located and name the file like the active sheet:
Dim FieldName As StringFilePath = ActiveWorkbook.Path
FieldName = ActiveSheet.Name
Open FilePath & "\" & TableName & ".sql" For Output As #1
And to avoid to manually specify the size (end row and column), use the spreadsheet size:
Dim SheetObj As VariantSheetObj = Sheets(1).UsedRange.Value
The VBA function UBound(SheetObj, 1) gives the last row number and UBound(SheetObj, 2) the last column used
Note: Empty trailing rows are also considered so your code needs to deal with that.
Finally if the purpose is to insert data into a DB/table, why not insert it directly from EXCEL using i.e. an ADODB object?
-
Use array, as Join function helps with concatenation. Consider this macro:
Dim FieldCount As Integer
Sub makeInsert()
Dim r As Range
Dim BeginOfCommand As String
Dim RestOfCommand As String
Dim Command As String
FieldCount = 0
File = "c:\temp\InsertCode.txt" 'Associate file
fHandle = FreeFile() ' Clean it
Open File For Output As fHandle 'and open it for writing
For Each r In ActiveSheet.UsedRange.Rows 'Go through all nonempty, continuous rows
If r.Row = 1 Then ' first row contains field names
BeginOfCommand = "INSERT INTO [myTable] (" & CollectFields(r, 1) & ") VALUES "
Else
RestOfCommand = CollectFields(r, 2) ' the others contains data for input
Command = BeginOfCommand & "(" & RestOfCommand & ");" 'prepare INSERT declaration
Print #fHandle, Command ' and write it in the file
End If
Next
Close #fHandle
End Sub
Function CollectFields(r As Range, FieldType As Integer) As String
If FieldType = 1 Then 'Detect how many fields we have
While r.Cells(1, FieldCount + 1) <> ""
FieldCount = FieldCount + 1
Wend
FieldCount = FieldCount - 1
End If
ReDim arr(FieldCount) As Variant 'Declare array
Dim v As Variant
For i = 0 To FieldCount
v = r.Cells(1, i + 1).Value
If IsNumeric(v) Then
arr(i) = v
Else
If FieldType = 1 Then
arr(i) = "[" & v & "]"
Else
arr(i) = "'" & v & "'"
End If
End If
Next
CollectFields = Join(arr, ", ")
End Function
Here You only need to name the fields in the first row and fill the data under. Oh, and if there is no data, just add "NULL"
-
This is a creative script and very flexible and I thank you for it.
However I have used Excel to create Insert statements for a long time without leaning on the Excel object model and VBA quite successfully. I actually am surprised more developers don't use a similar approach.
The trick is to use the "Concatenate" function to piece together the ascii portions of the the statement into one long string, providing single quotes where required. Each row can have values for the insert as was shown in the example here. Once the concatenage funtion is right for the first line, the formula can be copied to subsequent lines. The formula can be turned into a string by using the "copy"-"paste special"-"text" option. The result is that each line will have will have a formatted insert statement. This column can be copied and pasted into a query window in SSMS or a Text file as required.
-
I don't see the script in the article. Am I missing anything?
Stan
-
suslikovich (5/3/2011)
I don't see the script in the article. Am I missing anything?Stan
Actualy, the script we are talking about is in the article at:
http://www.sqlservercentral.com/scripts/insert/66001/
few lines after conclusion.
-
In case anyone is interested I modified the script to be used for writing updates as well. It assumes that the fields which make up your primary key are the first X columns in the excel file. This is needed so they don't try to get updated, and so they appear in the where clause. It prompts for the number of unique columns. I'm sure some of my syntax can be cleaned up, this it my first time with VB.
Sub CreateUpdateScript()
Dim Row As Integer
Dim Col As Integer
'To store all the columns available in the current active sheet
Dim ColNames(100) As String
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = ActiveSheet.Cells(Row, Col)
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1
'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.")
Dim MaxRow As Integer
MaxRow = InputBox("Give the Maximum Row No.")
Dim keyCols As Integer
keyCols = InputBox("Give the Number of unique id columns.")
'File to save the generated insert statements
File = "c:\\UpdateCode.txt"
fHandle = FreeFile()
Open File For Output As fHandle
Dim CellColCount As Integer
Dim StringStore As String
Dim whereClause As String
Do While Row <= MaxRow
StringStore = ""
whereClause = ""
CellColCount = 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "update " + ActiveSheet.Name + " SET "
Do While CellColCount <= ColCount
If CellColCount>keyCols Then
StringStore = StringStore + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
End If
If CellColCount < keyCols-1 Then
whereClause = whereClause + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "' AND "
End If
If CellColCount = keyCols-1 Then
whereClause = whereClause + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
End If
If (CellColCount <> ColCount) AND (cellColCount > keyCols) Then
StringStore = StringStore + " , "
End If
CellColCount = CellColCount + 1
Loop
Print #fHandle, StringStore + " WHERE " + whereClause
Print #fHandle, " "
Row = Row + 1
Loop
Close #fHandle
MsgBox ("Successfully Done")
End Sub
-
My project tool will build SQL statements for you too.
The tool will also validate and send the data directly from Excel if you wish.
The app will generate Insert statements using a template and merge code system.
Here is an example template :
INSERT INTO Customers
(
"Short Name",
"Name",
"Contact First Name",
"Contact Surname",
"Address",
"Town",
"Zip/PostCode",
"Days Credit",
"FirstOrder"
)
VALUES
( '{XL-merge:Short Name}',
'{XL-merge:Name}',
'{XL-merge:Contact First Name}',
'{XL-merge:Contact Surname}',
'{XL-merge:Address}',
'{XL-merge:Town}',
'{XL-merge:Zip/PostCode}',
{XL-merge:Days Credit},
'{XL-merge:FirstOrder}')
Templates are auto generated after you select a destination table or procedure.
The application will validate all of your data by wrapping the operation in Transaction, and reporting any errors and the reason for the error against each row.
You can modify the template SQL as you wish - changing it to Update or conditional insert / update or execute stored procedure if you wish.
Its free for 60 days and could save you allot of time 😉
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Hi, i liked the insert macro, can you please let me know how to add delete statement with where condition (which is in in 1st (A1) cell. Appreciate your help.
Thanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply