Excel VBA to import into SQL Server 2008

[Biz]

Hi,

I am new to using Excel VBA to import into SQL Server 2008.

Currently I have 17 files to upload up to 1000 rows manually. Is there a VBA that can load an excel file directly into SQL?
It would be faster then loading 1 file at time in SQL import.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I was thinking having named range and having SharePoint addresses of xlsm /xlsx files and looping it through.
<o> </o>
Any suggestions would be greatly appreciated.

Biz

 

[dk]

Hi

I tried to reply to this yesterday but the board crashed just as I tried. Second time lucky


There are a number of different ways. The simplest would be just constructing INSERT statements and then using ADO to insert the data. It's not the most efficient way but 17,000 rows shouldn't take too long. Here is some example code (requires a reference to the Microsoft ActiveX Data Objects 2.x Library):

Sub UploadFromExcelToSQL()
 
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long
 
sConnString = "Provider=sqloledb;Server=servername;Database=NORTHWIND;User Id=xx;Password=password"
 
Set adoCN = CreateObject("ADODB.Connection")
 
'adoCN.Open sConnString
 
'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
'For this example we can assume that the data exists on Sheet1, with a header on row
'1 and data in rows 2-11
'Also assume that the fields are defined as character (e.g. varchar or char)
'Text values must be enclosed in apostrophes whereas numeric values should not.
 

For lRow = 2 To 11
 
    sSQL = "INSERT INTO YOUR_TABLE (FIELD1, FIELD2, FIELD3) " & _
            " VALUES (" & _
            "'" & Sheet1.Cells(lRow, 1) & "', " & _
            "'" & Sheet1.Cells(lRow, 2) & "', " & _
            "'" & Sheet1.Cells(lRow, 3) & "')"
        
    adoCN.Execute sSQL
    
        
Next lRow
 
adoCN.Close
 
Set adoCN = Nothing
 

End Sub

Note that this code does not contain any error handling (which you would almost certainly want in order to capture any insert errors). You might also consider using a transaction for either all of your files or for each file - this would allow you to have an entire file either load successfully or fail completely e.g. if you had 1 row that was invalid in your file then you roll back all inserts for that file, inform the user that they need to fix the file before trying again.

Anyway, have a play around with that code to see if you can get the basic code working and then post back if you get stuck.

Cheers
DK

 

[Biz]

Hi

I tried to reply to this yesterday but the board crashed just as I tried. Second time lucky


There are a number of different ways. The simplest would be just constructing INSERT statements and then using ADO to insert the data. It's not the most efficient way but 17,000 rows shouldn't take too long. Here is some example code (requires a reference to the Microsoft ActiveX Data Objects 2.x Library):

Sub UploadFromExcelToSQL()
 
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long
 
sConnString = "Provider=sqloledb;Server=servername;Database=NORTHWIND;User Id=xx;Password=password"
 
Set adoCN = CreateObject("ADODB.Connection")
 
'adoCN.Open sConnString
 
'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
'For this example we can assume that the data exists on Sheet1, with a header on row
'1 and data in rows 2-11
'Also assume that the fields are defined as character (e.g. varchar or char)
'Text values must be enclosed in apostrophes whereas numeric values should not.
 
 
For lRow = 2 To 11
 
    sSQL = "INSERT INTO YOUR_TABLE (FIELD1, FIELD2, FIELD3) " & _
            " VALUES (" & _
            "'" & Sheet1.Cells(lRow, 1) & "', " & _
            "'" & Sheet1.Cells(lRow, 2) & "', " & _
            "'" & Sheet1.Cells(lRow, 3) & "')"
 
    adoCN.Execute sSQL
 
 
Next lRow
 
adoCN.Close
 
Set adoCN = Nothing
 
 
End Sub

Note that this code does not contain any error handling (which you would almost certainly want in order to capture any insert errors). You might also consider using a transaction for either all of your files or for each file - this would allow you to have an entire file either load successfully or fail completely e.g. if you had 1 row that was invalid in your file then you roll back all inserts for that file, inform the user that they need to fix the file before trying again.

Anyway, have a play around with that code to see if you can get the basic code working and then post back if you get stuck.

Cheers
DK

Hi DK,

Thank you for the code. I will play around and let you know how I go.

Biz

 

[Fazza]

As another idea, please consider the SQL in post #5 of http://www.mrexcel.com/forum/showthread.php?t=332472 used in a late bound example to MS Access with the whole table being loaded in one step instead of looping through each record. With multiple uploads, better as dk has posted to use a single Connection object and then multiple .Execute instead of the Recordset per that other thread.

 

[dk]

As another idea, please consider the SQL in post #5 of http://www.mrexcel.com/forum/showthread.php?t=332472 used in a late bound example to MS Access with the whole table being loaded in one step instead of looping through each record. With multiple uploads, better as dk has posted to use a single Connection object and then multiple .Execute instead of the Recordset per that other thread.

Nice one! Never knew you could do that. When I have needed a quick upload what I had been doing previously was saving my Excel file as a CSV and then uploading using the SQL server DTSPackage library (basically automates the Data Transformation wizard). Will try your method as it certainly seems a lot simpler.

[edit]The one possible advantage that the looping might have is that you could pinpoint any invalid data and tell the user exactly where the error occurs e.g. Invalid data on row 216 of workbook xyz.xls - unless there is a way to capture this AND do the bulk insert?[/edit]

Cheers
DK

 

[Fazza]

FYI, I think I initially read about it at http://www.xtremevbtalk.com/showthread.php?t=217783

It was my reference point in early days of using ADO and very helpful.

I hope it works for you.

 

[dk]

Thanks for the link Fazza, very useful.

I tried your demo code using an Access database and it worked perfectly - 10000 records in 3.3 seconds!

However, when I tried the same thing into a SQL database I get an error. My thinking is that this "unusual" SQL statement is understood by the Jet provider but not the SQL Server provider.

INSERT INTO MYTABLE2 SELECT * FROM [Sheet1$] IN 'H:\temp\Book4.xls' 'Excel 8.0;'

The code I am using is as follows. The error is "Incorrect syntax near the keyword 'IN'."

Sub demo()
Dim objRS As ADODB.Recordset
Dim sConn As String
Dim sSQL As String
  
  
Set objRS = CreateObject("ADODB.Recordset")
 

sConn = "Provider=sqloledb;Server=xxx;Database=NORTHWIND;User Id=xxx;Password=xxx"

'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "H:\Sun\Sun Tables.mdb"


sSQL = "INSERT INTO MYTABLE2 SELECT * FROM [Sheet1$] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'"

objRS.Open sSQL, sConn
 
Set objRS = Nothing
  
  
  
End Sub

Any ideas?

Cheers
DK

 

[Fazza]

Maybe this helps, http://www.mrexcel.com/forum/showthread.php?t=378209 ? Maybe not! Untested.

Idea being to set the different connection properties in the SQL specific to the item.

 

[dk]

Maybe this helps, http://www.mrexcel.com/forum/showthread.php?t=378209 ? Maybe not! Untested.

Idea being to set the different connection properties in the SQL specific to the item.

Cheers - will have a look later. I think it works in that case because the Jet provider can read text, Excel and Access "databases" whereas I imagine that the sqloledb provider can only connect to SQL server databases, although will post back once I've had a look.

Cheers
DK

 

[Fazza]

Yes, I understand that from your earlier post. It sounds right. regards