import Excel worksheet to SQL Server table

[DennisB]

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

 

[AB]

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.

 

[ryguy7272]

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

 

[ryguy7272]

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.

 

[Madiya]

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







- 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

 

[DennisB]

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?

 

[DennisB]

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

 

[ryguy7272]

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