VBA Data Export from Excel to T-SQL looses rows

[chris11115]

Hello everyone!

I'd like to create a macro that exports all the data from an Excel sheet to SQL Server 2012.
Generally it is already working, however, now I used this macro on a sheet with about 70.000 rows where only 4497 rows are imported.

I tried copying the data as values to new sheets, only importing single columns, but always the same result.. When I delete columns around column 4500, the number of imported columns changes a bit, but always around 4500. When I use the Import Wizard from T-SQL, all the data is imported. I also tried to multiply the columns with 1, then about 65.000 rows are imported, but still not all of them. There seems to be a problem with the data, but what is it?

Other Excel tables with more than 70.000 columns could be imported without any problems, I have not idea how to solve this.

My macro is based on the following code from https://support.microsoft.com/en-us/kb/321686#bookmark-4f :

<code>

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=
].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing</user></database></server></code>

Openrowset and Opendatasource are no options due to security reasons.
Is anyone able to help me or knows of a different approach?

Thanks!

 

[chris11115]

I could solve the problem by changing the Provider to Microsoft.ACE.OLEDB.12.0, didn't even need Openrowset or Opendatasource.
Whenever the import wizard uses Jet instead of Microsoft.ACE.OLEDB.12.0 (which is weird, is there a logic when the wizard uses which method?) the same problem occurs. There seems to be a bug in the Jet engine...

Thanks for the replies!
<code> </code>