SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Arun Mallick / Create INSERT statements from Excel / Latest Posts

RE: Create INSERT statements from Excel

C# Gnu — Thu, 02 Aug 2012 03:45:39 GMT

My [url=http://leansoftware.net/en-us/productsdownloads/exceltosqlserver.aspx]project tool[/url] 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 :[code]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}')[/code]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 ;)

RE: Create INSERT statements from Excel

travbowls — Wed, 16 Nov 2011 16:35:39 GMT

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 ThenStringStore = 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 IfIf (CellColCount <> ColCount) AND (cellColCount > keyCols) Then StringStore = StringStore + " , " End IfCellColCount = CellColCount + 1 Loop Print #fHandle, StringStore + " WHERE " + whereClause Print #fHandle, " " Row = Row + 1 Loop Close #fHandle MsgBox ("Successfully Done") End Sub

RE: Create INSERT statements from Excel

damir.sovic — Tue, 03 May 2011 14:01:03 GMT

[quote][b]suslikovich (5/3/2011)[/b][hr]I don't see the script in the article. Am I missing anything?Stan[/quote]Actualy, the script we are talking about is in the article at:http://www.sqlservercentral.com/scripts/insert/66001/few lines after conclusion.

RE: Create INSERT statements from Excel

suslikovich — Tue, 03 May 2011 13:01:20 GMT

I don't see the script in the article. Am I missing anything?Stan

RE: Create INSERT statements from Excel

dan trate — Mon, 02 May 2011 16:01:57 GMT

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.

RE: Create INSERT statements from Excel

damir.sovic — Mon, 02 May 2011 04:33:26 GMT

Use array, as Join function helps with concatenation. Consider this macro:Dim FieldCount As IntegerSub makeInsert()Dim r As RangeDim BeginOfCommand As StringDim RestOfCommand As StringDim Command As StringFieldCount = 0File = "c:\temp\InsertCode.txt" 'Associate file fHandle = FreeFile() ' Clean it Open File For Output As fHandle 'and open it for writingFor 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 IfNextClose #fHandleEnd SubFunction 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 FunctionHere 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"

RE: Create INSERT statements from Excel

Luxgilby — Mon, 02 May 2011 04:04:33 GMT

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:[code="other"] Dim FieldName As String FilePath = ActiveWorkbook.Path FieldName = ActiveSheet.Name Open FilePath & "\" & TableName & ".sql" For Output As #1[/code]And to avoid to manually specify the size (end row and column), use the spreadsheet size:[code="other"] Dim SheetObj As Variant SheetObj = Sheets(1).UsedRange.Value[/code]The VBA function UBound(SheetObj, 1) gives the last row number and UBound(SheetObj, 2) the last column usedNote: 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?

RE: Create INSERT statements from Excel

David Jackson — Wed, 01 Apr 2009 06:12:32 GMT

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 ArunHTHDave Jackson[code]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[/code]

RE: Create INSERT statements from Excel

Sophie Gravier — Tue, 31 Mar 2009 09:10:46 GMT

Thank you to all those that replied. The single quote twice works the best in my particular environment. Perfect!!Thanks again!Sophie

RE: Create INSERT statements from Excel

Sriram-288748 — Tue, 31 Mar 2009 09:05:04 GMT

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

RE: Create INSERT statements from Excel

Ed Klein-385786 — Tue, 31 Mar 2009 09:01:37 GMT

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 [code]INSERT INTO [BASE_TABLE]([C1]) VALUES('Joe''s Bar')[/code]will work. Add code to the macro to replace ' with '' as you build the insert statement and you should be back in business

RE: Create INSERT statements from Excel

erwin.oosterhoorn — Tue, 31 Mar 2009 08:56:38 GMT

Adding :[code]File = InputBox("Enter file destination like C:\\filename.sql", "Filename", "c:\\InsertScript.sql")[/code]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.

RE: Create INSERT statements from Excel

Sophie Gravier — Tue, 31 Mar 2009 05:26:14 GMT

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

Create INSERT statements from Excel

mail.arun2005 — Sun, 22 Feb 2009 20:22:37 GMT

Comments posted to this topic are about the item [B]Create INSERT statements from Excel[/B]