Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create INSERT statements from Excel


Create INSERT statements from Excel

Author
Message
mail.arun2005
mail.arun2005
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 176
Comments posted to this topic are about the item Create INSERT statements from Excel
Sophie Gravier
Sophie Gravier
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
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
erwin oosterhoorn
erwin oosterhoorn
SSC Eights!
SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)

Group: General Forum Members
Points: 882 Visits: 364
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.
Ed Klein-385786
Ed Klein-385786
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 467
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
Sriram-288748
Sriram-288748
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 340
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
Sophie Gravier
Sophie Gravier
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
Thank you to all those that replied. The single quote twice works the best in my particular environment. Perfect!!

Thanks again!
Sophie
David Jackson
David Jackson
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1877
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





http://glossopian.co.uk/
"I don't know what I don't know."
Luxgilby
Luxgilby
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 175
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 String
FilePath = 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 Variant
SheetObj = 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?
damir.sovic
damir.sovic
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 17
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"
dan trate
dan trate
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 98
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search