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!

 

[mole999]

is your excel data consistent, targets as numbers or variants, sufficient space in the table to import the whole field without truncating

 

[Norie]

Can you do the import succesfully from SQL Server?

 

[chris11115]

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]

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

 

[chris11115]

I don't know of any other (easy) way. What do you think of? Something like the csv flatfile import?

 

[Norie]

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]

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]

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]

I don't have the rights to use Openrowset or Opendatasource, I don't know how to do such a query without them.