import Excel worksheet to SQL Server table
 

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

import Excel worksheet to SQL Server table
 

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 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

import Excel worksheet to SQL Server table
 

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 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

.

import Excel worksheet to SQL Server table
 

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 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

.

import Excel worksheet to SQL Server table
 

On May 4, 12:49*am, ryguy7272
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.

import Excel worksheet to SQL Server table
 

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 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

.

import Excel worksheet to SQL Server table
 

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 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

.

import Excel worksheet to SQL Server table
 

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 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

.