import Excel worksheet to SQL Server table
in [Programming]
|
Prev: When recording
Next: Help in VBA code
From: DennisB on 1 May 2010 11:22 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: AB on 1 May 2010 15:56 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 3 May 2010 15:44 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 3 May 2010 15:49 Take a look at this and post back with the outcome: 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! There are MANY ways to do this. The code I posted takes inputs from 5 TextBoxes (actually using 3 of the 5). You can store the variables in cells, or hard-code too. In any event, try that and see how you get along. -- 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: Madiya on 4 May 2010 05:40
On May 4, 12:49 am, ryguy7272 <ryguy7...(a)discussions.microsoft.com> wrote: > Take a look at this and post back with the outcome: > > 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! There are MANY ways to do this. The code I posted takes inputs > from 5 TextBoxes (actually using 3 of the 5). You can store the variables in > cells, or hard-code too. In any event, try that and see how you get along. > > -- > 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 > > > .- Hide quoted text - > > - Show quoted text - Hi Ryan, Is there anyway in VBA to loop thru all user tables in a database? I am using SQL2000 Regards, Madiya |