Import: Excel -> SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import: Excel -> SQL

Author

Topic

Kaem
Starting Member

4 Posts

Posted - 2012-07-17 : 07:04:56

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

52326 Posts

Posted - 2012-07-17 : 09:40:40

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

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

Kaem
Starting Member

4 Posts

Posted - 2012-07-18 : 01:07:01

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-18 : 02:13:19

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
[spoiler]Time is always against us[/spoiler]

Kaem
Starting Member

4 Posts

Posted - 2012-07-18 : 02:46:59

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.

Kaem
Starting Member

4 Posts

Posted - 2012-07-18 : 07:06:13

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 09:45:59

wow...thts was a nice guess Tan

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

Mericon
Starting Member

2 Posts

Posted - 2013-07-12 : 03:41:06

Wow, amazing man! Good Job.

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources