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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
is your excel data consistent, targets as numbers or variants, sufficient space in the table to import the whole field without truncating
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Can you do the import succesfully from SQL Server?
 

chris11115

New Member
Joined
Oct 30, 2015
Messages
6
Thanks for the replies!

As far as I can tell the data should be consistent. It doesn't look any different to me than any other datasets where the import work. When I try to import a column that definitely only has numbers in it from this particular table I get the same number of rows as when I try to import one with text. I am wondering whether Excel somehow saved these cells differently than it usually does, even after pasting the values of the colums somewhere else. The fact that about 65000 rows are imported after multiplying a whole column with 1 is also quite strange to me, since there are a lot more rows than 70.000 with the value 0 after this process.

I'm not sure what programme was first used to create this output, if I had to guess I'd say Access.

T-SQL usually saves the numbers as float and text as nvarchar, which hasn't been a problem for me so far. Space limitations shouldn't be a problem either. The table doesn't exist before importing and I've imported more rows into single tables with other Excel files.


Yes, I can successfully import the data into the SQL Server with the Import Wizard.<code></code>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you import to SQL Server successfully without using the Import Wizard?
 

chris11115

New Member
Joined
Oct 30, 2015
Messages
6
I don't know of any other (easy) way. What do you think of? Something like the csv flatfile import?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What I was thinking was to use ADO to execute a query/stored procedure in SQL Server to do the import.

So kind of do things the other way round.
 

chris11115

New Member
Joined
Oct 30, 2015
Messages
6
Unfortunately I know of no T-SQL code to do an import from Excel without using Openrowset or Opendatasource.
Do you know of a possibility? Is it possible to control the T-SQL import wizard in the code?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Is there a problem using Openrowset or Opendatasource?

Have you tried running a query in SQL Server to do the import?

If you can do that manually then you should be able to execute the query via ADO.
 

chris11115

New Member
Joined
Oct 30, 2015
Messages
6
I don't have the rights to use Openrowset or Opendatasource, I don't know how to do such a query without them.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,087
Messages
5,640,028
Members
417,122
Latest member
kirk5370

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