Excel to SQL Server (SqlBulkCopy method)

[marcbruun]

Dear members,

Does anybody have a method writing (in bulk) a named range or sheet to SQL server from Excel 2010?

I have 100000 to 300000 records I want to write to SQL Server from an Excel model and looping through each record loaded into an array is too slow.

Im suspecting I need to use a SqlBulkCopy method from Excel or similar but how?

I can do most things in VBA but SQL is not my strong side.

BR

Marc Bruun

[Gurberly]

Hi

Is this a one off import to a new table in SQL or something that will be run regulary adding data to an existing table?

Also will the data require validation before being added to the SQL table?

Reason for asking, that it would probaly be easier to use the Data Transformation package/tool in SQL Server to import the data to the SQL table rather than code it from the VBA side.

If it's going to be a regular job then you can schedule it too.

[marcbruun]

Hi Gurberly,

Thanks for fast reply.

It is not a one off, but an advanced Excel app where the user will submit data several time a week/day by pressing a button in Excel.

The data will not require validation, but Im sure I will be able to implement that later on if needed. Right now, I need the core transfer technology.

It is a must that the base (VBA) code is in Excel and executed from here by the user.

BR
Marc

[Brady]

I tried importing it as Excel 97/2003 .xls format (instead of 2007) and it is failing.

Also I tried to follow as per the below link for SQL 2008 and found the below error after pressing the "Test Connection" button
"Test Connection failed because of an error in initializing provider. Unrecognized database format 'c:\XXX\test.xlsx'

Below problem what I am facing with my normal strategy

1. In the first wizard ("Choose a Data Source") selected data source as "Microsoft Excel"
2. Selected the .xlsx path which has many sheets that needs to be imported into respective tables
3. Excel version selected as 2007
4. In the second wizard ("Choose a Destination") selected destination as "Microsoft OLE DB Provider for SQL Server"
5. Server name selected
6. Authentication - Selected SQL server authentication
7. Selected the database where i want to import data
8. In the third wizard ("Specify table copy or query), I have selected Copy data.....
9. In the fourth wizard ("Select source tables and views") I have selected list of all the tables and mapped them to the corresponding tables
10. In the fifth wizard ("Review Data Type Mapping") I am finding below error message

[mohanvijay]

did you try add records from excel to SQL through ADODB
i have added 100000 records from excel to Access through ADODB
that took approx 4 minutes

[Norie]

You can run an SQL Server query in ADODB that will do this.

This is an old example, but it did work.

Option Explicit
Sub ADOXLtoSQLSRV()
Dim cn As ADODB.Connection
Dim strConn As String
Dim strSQL As String
Dim strXLSource As String
Dim lngRecsAff As Long
    strConn = strConn & "Provider=SQLOLEDB;Data Source=BIG-TOSH;"
    strConn = strConn & "Initial Catolog=Customers;Trusted_Connection=YES"
    Set cn = New ADODB.Connection
    cn.Open strConn
    strXLSource = "C:\\AccountNos.xls;Extended Properties=Excel 12.0"
    strSQL = " INSERT INTO Customers.dbo.XLImport "
    strSQL = strSQL & " ([Account]) "
    strSQL = strSQL & " SELECT [Account] "
    strSQL = strSQL & " FROM "
    strSQL = strSQL & " OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=" & strXLSource & "')...[tblAccounts$] "
 
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
    
    cn.Close
    
End Sub