VBA Data Export from Excel to T-SQL looses rows

chris11115

New Member
Joined
Oct 30, 2015
Messages
6
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>
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

New Member
Joined
Oct 30, 2015
Messages
6
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>
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,127,558
Messages
5,625,503
Members
416,114
Latest member
Ayush_123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top