Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create INSERT statements from Excel Expand / Collapse
Author
Message
Posted Sunday, February 22, 2009 8:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:25 PM
Points: 15, Visits: 175
Comments posted to this topic are about the item Create INSERT statements from Excel
Post #662333
Posted Tuesday, March 31, 2009 5:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:31 AM
Points: 6, Visits: 18
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
Post #686886
Posted Tuesday, March 31, 2009 8:56 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 3:32 AM
Points: 609, Visits: 237
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.
Post #687123
Posted Tuesday, March 31, 2009 9:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:39 AM
Points: 88, Visits: 366
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

Post #687128
Posted Tuesday, March 31, 2009 9:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
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

Post #687136
Posted Tuesday, March 31, 2009 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:31 AM
Points: 6, Visits: 18
Thank you to all those that replied. The single quote twice works the best in my particular environment. Perfect!!

Thanks again!
Sophie
Post #687142
Posted Wednesday, April 1, 2009 6:12 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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."
Post #687812
Posted Monday, May 2, 2011 4:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 6:33 AM
Points: 1,114, Visits: 152
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?
Post #1101490
Posted Monday, May 2, 2011 4:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 3:53 AM
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"
Post #1101497
Posted Monday, May 2, 2011 4:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 8:51 AM
Points: 97, Visits: 83
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.
Post #1101997
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse