import Excel worksheet to SQL Server table
in [Programming]
|
Prev: When recording
Next: Help in VBA code
From: DennisB on 4 May 2010 18:41 Thank you. I got the code to work for deleting the records. however, I have a 16 column and 5,000 record table in Excel that I want to append to an existing table. I have a range variable for the whole table and I want to append the range to the SQL table. The SQL table has the same field names and datatypes. Is there a quick way to do this or do I have to loop through each range value (16 per row) and then loop through the rows to update my SQL table? "ryguy7272" wrote: > I know I just posted here; not seeing it now though so I'll try once more. > Sub Rectangle1_Click() > 'TRUSTED CONNECTION > On Error GoTo errH > > Dim con As New ADODB.Connection > Dim rs As New ADODB.Recordset > Dim strPath As String > Dim intImportRow As Integer > Dim strFirstName, strLastName As String > > Dim server, username, password, table, database As String > > > With Sheets("Sheet1") > > server = .TextBox1.Text > table = .TextBox4.Text > database = .TextBox5.Text > > > If con.State <> 1 Then > > con.Open "Provider=SQLOLEDB;Data Source=" & server & > ";Initial Catalog=" & database & ";Integrated Security=SSPI;" > 'con.Open > > End If > 'this is the TRUSTED connection string > > Set rs.ActiveConnection = con > > 'delete all records first if checkbox checked > If .CheckBox1 Then > con.Execute "delete from tbl_demo" > End If > > 'set first row with records to import > 'you could also just loop thru a range if you want. > intImportRow = 10 > > Do Until .Cells(intImportRow, 1) = "" > strFirstName = .Cells(intImportRow, 1) > strLastName = .Cells(intImportRow, 2) > > 'insert row into database > con.Execute "insert into tbl_demo (firstname, lastname) > values ('" & strFirstName & "', '" & strLastName & "')" > > intImportRow = intImportRow + 1 > Loop > > MsgBox "Done importing", vbInformation > > con.Close > Set con = Nothing > > End With > > Exit Sub > > errH: > MsgBox Err.Description > End Sub > > > Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There > are many ways to do this. Inputs can come from cells, or be hardcoded, > whatever you prefer. > > Ryan-- > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''. > > > "AB" wrote: > > > You can try reading the data in excel and pushing the data into the > > server either as APPEND queries (sql statements) (via > > connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out > > and see if you gain any speed advantage. I would not be that surprised > > if Server Management Studios native solution is faster than any 'home > > made' but give it a shot - who knows. > > I'm always pushing the data via the two above methods (append query or > > recordset.addnew) so I wouldn't have a clue how it compares to the > > wizard. > > > > > > On May 1, 4:22 pm, DennisB <Denn...(a)discussions.microsoft.com> wrote: > > > I have an ADO connection to SQL Server 2005 and I use SQL Server Management > > > Studio's import wizard to import Excel sheets to tables. This is very time > > > consuming and I was wondering what would be the best way to import the files > > > to SQL using VBA from Excel. I currently call various stored procedures from > > > VBA. > > > > > > Any code snippets or help? > > > > > > DennisB > > > > . > >
From: DennisB on 4 May 2010 20:48 ryguy7272, I used your code got it to work. However, now I'm getting an error everywhere in my application that I have never seen before. "Code execution has been interrupted" How can I get rid of this. I have closed my file and the sql server, and reopened the excel file and started to run my app. Now I'm getting this error constantly. what can I do? Dennis "DennisB" wrote: > Thank you. I got the code to work for deleting the records. however, I have > a 16 column and 5,000 record table in Excel that I want to append to an > existing table. I have a range variable for the whole table and I want to > append the range to the SQL table. The SQL table has the same field names > and datatypes. > > Is there a quick way to do this or do I have to loop through each range > value (16 per row) and then loop through the rows to update my SQL table? > > > > > > "ryguy7272" wrote: > > > I know I just posted here; not seeing it now though so I'll try once more. > > Sub Rectangle1_Click() > > 'TRUSTED CONNECTION > > On Error GoTo errH > > > > Dim con As New ADODB.Connection > > Dim rs As New ADODB.Recordset > > Dim strPath As String > > Dim intImportRow As Integer > > Dim strFirstName, strLastName As String > > > > Dim server, username, password, table, database As String > > > > > > With Sheets("Sheet1") > > > > server = .TextBox1.Text > > table = .TextBox4.Text > > database = .TextBox5.Text > > > > > > If con.State <> 1 Then > > > > con.Open "Provider=SQLOLEDB;Data Source=" & server & > > ";Initial Catalog=" & database & ";Integrated Security=SSPI;" > > 'con.Open > > > > End If > > 'this is the TRUSTED connection string > > > > Set rs.ActiveConnection = con > > > > 'delete all records first if checkbox checked > > If .CheckBox1 Then > > con.Execute "delete from tbl_demo" > > End If > > > > 'set first row with records to import > > 'you could also just loop thru a range if you want. > > intImportRow = 10 > > > > Do Until .Cells(intImportRow, 1) = "" > > strFirstName = .Cells(intImportRow, 1) > > strLastName = .Cells(intImportRow, 2) > > > > 'insert row into database > > con.Execute "insert into tbl_demo (firstname, lastname) > > values ('" & strFirstName & "', '" & strLastName & "')" > > > > intImportRow = intImportRow + 1 > > Loop > > > > MsgBox "Done importing", vbInformation > > > > con.Close > > Set con = Nothing > > > > End With > > > > Exit Sub > > > > errH: > > MsgBox Err.Description > > End Sub > > > > > > Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There > > are many ways to do this. Inputs can come from cells, or be hardcoded, > > whatever you prefer. > > > > Ryan-- > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > "AB" wrote: > > > > > You can try reading the data in excel and pushing the data into the > > > server either as APPEND queries (sql statements) (via > > > connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out > > > and see if you gain any speed advantage. I would not be that surprised > > > if Server Management Studios native solution is faster than any 'home > > > made' but give it a shot - who knows. > > > I'm always pushing the data via the two above methods (append query or > > > recordset.addnew) so I wouldn't have a clue how it compares to the > > > wizard. > > > > > > > > > On May 1, 4:22 pm, DennisB <Denn...(a)discussions.microsoft.com> wrote: > > > > I have an ADO connection to SQL Server 2005 and I use SQL Server Management > > > > Studio's import wizard to import Excel sheets to tables. This is very time > > > > consuming and I was wondering what would be the best way to import the files > > > > to SQL using VBA from Excel. I currently call various stored procedures from > > > > VBA. > > > > > > > > Any code snippets or help? > > > > > > > > DennisB > > > > > > . > > >
From: ryguy7272 on 6 May 2010 00:58
Ok, after a lot of independent research, and after speaking with a friend of mine, who knows this stuff like no one's business, I think I have to conclude that this can't be done b/w Excel and SQL Server. I think that it's easy to concatenate a string, but when you pass that string to a SQL Server table, SQL Server can't parse that string into the appropriate fields, so it goes back to it's original form. There seems to be a way to do this using C# and SQL Server, but I don't know for sure how to do that, and that topic is getting pretty far way from the original topic. If anyone can prove me wrong, I'd love to see a solution. Finally, I'm not sure about the error. I've run this code many times and never had a single problem. Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "DennisB" wrote: > ryguy7272, > > I used your code got it to work. However, now I'm getting an error > everywhere in my application that I have never seen before. > > "Code execution has been interrupted" > > How can I get rid of this. I have closed my file and the sql server, and > reopened the excel file and started to run my app. Now I'm getting this > error constantly. > > what can I do? > > Dennis > > "DennisB" wrote: > > > Thank you. I got the code to work for deleting the records. however, I have > > a 16 column and 5,000 record table in Excel that I want to append to an > > existing table. I have a range variable for the whole table and I want to > > append the range to the SQL table. The SQL table has the same field names > > and datatypes. > > > > Is there a quick way to do this or do I have to loop through each range > > value (16 per row) and then loop through the rows to update my SQL table? > > > > > > > > > > > > "ryguy7272" wrote: > > > > > I know I just posted here; not seeing it now though so I'll try once more. > > > Sub Rectangle1_Click() > > > 'TRUSTED CONNECTION > > > On Error GoTo errH > > > > > > Dim con As New ADODB.Connection > > > Dim rs As New ADODB.Recordset > > > Dim strPath As String > > > Dim intImportRow As Integer > > > Dim strFirstName, strLastName As String > > > > > > Dim server, username, password, table, database As String > > > > > > > > > With Sheets("Sheet1") > > > > > > server = .TextBox1.Text > > > table = .TextBox4.Text > > > database = .TextBox5.Text > > > > > > > > > If con.State <> 1 Then > > > > > > con.Open "Provider=SQLOLEDB;Data Source=" & server & > > > ";Initial Catalog=" & database & ";Integrated Security=SSPI;" > > > 'con.Open > > > > > > End If > > > 'this is the TRUSTED connection string > > > > > > Set rs.ActiveConnection = con > > > > > > 'delete all records first if checkbox checked > > > If .CheckBox1 Then > > > con.Execute "delete from tbl_demo" > > > End If > > > > > > 'set first row with records to import > > > 'you could also just loop thru a range if you want. > > > intImportRow = 10 > > > > > > Do Until .Cells(intImportRow, 1) = "" > > > strFirstName = .Cells(intImportRow, 1) > > > strLastName = .Cells(intImportRow, 2) > > > > > > 'insert row into database > > > con.Execute "insert into tbl_demo (firstname, lastname) > > > values ('" & strFirstName & "', '" & strLastName & "')" > > > > > > intImportRow = intImportRow + 1 > > > Loop > > > > > > MsgBox "Done importing", vbInformation > > > > > > con.Close > > > Set con = Nothing > > > > > > End With > > > > > > Exit Sub > > > > > > errH: > > > MsgBox Err.Description > > > End Sub > > > > > > > > > Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There > > > are many ways to do this. Inputs can come from cells, or be hardcoded, > > > whatever you prefer. > > > > > > Ryan-- > > > > > > -- > > > Ryan--- > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > "AB" wrote: > > > > > > > You can try reading the data in excel and pushing the data into the > > > > server either as APPEND queries (sql statements) (via > > > > connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out > > > > and see if you gain any speed advantage. I would not be that surprised > > > > if Server Management Studios native solution is faster than any 'home > > > > made' but give it a shot - who knows. > > > > I'm always pushing the data via the two above methods (append query or > > > > recordset.addnew) so I wouldn't have a clue how it compares to the > > > > wizard. > > > > > > > > > > > > On May 1, 4:22 pm, DennisB <Denn...(a)discussions.microsoft.com> wrote: > > > > > I have an ADO connection to SQL Server 2005 and I use SQL Server Management > > > > > Studio's import wizard to import Excel sheets to tables. This is very time > > > > > consuming and I was wondering what would be the best way to import the files > > > > > to SQL using VBA from Excel. I currently call various stored procedures from > > > > > VBA. > > > > > > > > > > Any code snippets or help? > > > > > > > > > > DennisB > > > > > > > > . > > > > |