SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import: Excel -> SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kaem
Starting Member

4 Posts

Posted - 07/17/2012 :  07:04:56  Show Profile  Reply with Quote
Hi,

I'm trying to import data from Excel workbook using the code:

INSERT INTO [KM].[dbo].Database SELECT * FROM DATA...sheet1$

("DATA" is the linked server)

The sheet1 worksheet contains around 100 000 rows (17 columns).

The problem is that the above code results in creating only
38 499 rows. The same happens when I use the exact data range
(...sheet1$A1:Q100000).

When I try to run the code again, specyfing a different range
(lets say A40000:Q90000) I receive the error saying that the
Microsoft Jet can not find the object A40000:Q90000 and the
following:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].


However, if I try much smaller range (A40000:Q41000) it feeds
the data in...but for some ranges it doesn't. I can't find any
rule for that.

The Excel file is MS Office 2010, the SQL server is 2000.
The file doesn't have any holes or breaks.

If you have any ideas what can be the problem, please share it : )

Thank you.


PS. The same applies when I use the syntax:

INSERT INTO [KM].[dbo].Database SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;
Database=C:\Documents and Settings\Krzysiek\Pulpit\database.xlsx',
'SELECT * FROM [sheet1$A60000:Q90000]')

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/17/2012 :  09:40:40  Show Profile  Reply with Quote
can you give little more info on what excel contains? like column datatype,actual data etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Kaem
Starting Member

4 Posts

Posted - 07/18/2012 :  01:07:01  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

can you give little more info on what excel contains? like column datatype,actual data etc



Basically this is accounting data and contains the history of
accounting entries. The columns are: (I also included my description in some cases)

Lp nvarchar(50) (number)
Konto nvarchar(255) (account nr)
[Nr dziennika] nvarchar(255)
[Nr dziennika2] nvarchar(255)
[Data ksiegowania] datetime (date)
[Id# ksiegowy] nvarchar(255)
Dokument nvarchar(255)
Opis nvarchar(255) (description)
[Nazwa podmiotu] nvarchar(255) (comp. name)
[Konto przeciw#] nvarchar(255)
[Kwota Wn] float (amount)
[Kwota Ma] float (amount)
[Kwota Wn w walucie] float (amount)
[Kwota Ma w walucie] float (amount)
Waluta nvarchar(255) (currency)
Month nvarchar(255)
Year nvarchar(255)


Actual data reflects the content of each column, nothing
extraordinary.

Is there any other information which would be helpful?

Thank you for your help,

KM.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17434 Posts

Posted - 07/18/2012 :  02:13:19  Show Profile  Reply with Quote
Just a guess, it could be Microsoft.Jet.OLEDB.4.0 cannot handle more than 64K rows. Try using Microsoft.ACE.OLEDB.12.0


KH
Time is always against us

Go to Top of Page

Kaem
Starting Member

4 Posts

Posted - 07/18/2012 :  02:46:59  Show Profile  Reply with Quote
Hmmm...it seems to work better now. This is how the testing of Microsoft.ACE.OLEDB.12.0 went:

1. Importing 90 000 rows - the same old error message:

[OLE/DB provider returned message: Microsoft Access data base can not find the object "sheet1$A2:Q90000" - [my translation, this part was in polish]
OLE DB error trace [OLE/DB Provider 'Microsoft.ACE.OLEDB.12.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' reported an error.

2. Importing 60 000 rows (basically below 65K):

The import was OK!

However, while testing I got several (different) error messages even if the number
of rows was below 65K. It seems like the import process is sensitive
to the other tasks I do on the PC during the import.
When I left the computer to let it make the import it finally went OK.


Event if the method you proposed is still limited to 65K I can live with
it - I'll have just to join two tables afterwards. Of course, an import
without limitations would be nice : )

Thanks for help,

KM.

Edited by - Kaem on 07/18/2012 06:00:03
Go to Top of Page

Kaem
Starting Member

4 Posts

Posted - 07/18/2012 :  07:06:13  Show Profile  Reply with Quote
After some more testing I can confirm that using Microsoft.ACE.OLEDB.12.0 instead of Microsoft.Jet.OLEDB.4.0 solved the problem.

Thank you khtan for your guess : )

KM.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/18/2012 :  09:45:59  Show Profile  Reply with Quote
wow...thts was a nice guess Tan

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Mericon
Starting Member

2 Posts

Posted - 07/12/2013 :  03:41:06  Show Profile  Reply with Quote
Wow, amazing man! Good Job.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000