Import data from excel 2007 to sql server 2005

Question

Post reply

Import data from excel 2007 to sql server 2005

ekonagu

SSChasing Mays

Points: 627
More actions
August 3, 2009 at 11:47 pm

#132588

hello guys
i am trying to import data from excel 2007 to sql server 2005 as i have selected the provider microsoft office 12.0 access .... in import export wizard.but when i am testing that connection the following error comes
test connection failed because of an error in initializing provider.cannot start ur aplication.the workgroup information file is missing or opened exclusivly by another user
wht is this error means
i googlesd but didnt get any proper one
pls help

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

Hi

Above the Office 12 connection there is one specifically for Excel. I use this one and it works very well.

You should also have an option once your in the wizard to select Office 2007

Rgds

JL

- Win. SSCertifiable Points: 6790 More actions · Answer 2

Use Micrsoft Excel as a Data Source for Importing the data from Excel, give the location of the file path and try.

Check if the file is not opened or used when you use the file to import.

-

Win.

Cheers,
- Win.

" Have a great day "

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

This forum post although referencing importing Excel to 2008, I believe will also be applicable to 2005.

It supplies in great detail, and a complete script which may provide you with the information you are seeking. Be sure to read he entire post as the script is the next to last entry.

http://www.sqlservercentral.com/Forums/Topic763265-392-1.aspx

Let us know if this has helped you or what you modified in the script to accomplish your task

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

starunit SSCommitted Points: 1817 More actions · Answer 4

Thanks for the link, bitbucket, I'll check it out, soon.

Up until recently I had no problems using OPENROWSET to work with EXCEL (as recently as 2 months ago was using it...

Microsoft.Jet.OLEDB.4.0','Excel 8.0; IMEX=1;Database=theExcelPath)

I don't know if due to a recent hotfix, mucrosoft update, or what but the last few weeks I haven't been able to connect... (Vista Workstation/SQL2005 Developer/Office 2007)

I've downloaded the Office support drivers, set registry settings, changed the SQL Server service account, et al: no joy (yet)..

I'll keep trying and watching and hoping...

Cheers,

Mark Starr

Mark
Just a cog in the wheel.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Starunit

When you do get it figured out, and make it work, think about posting your code here so as to help others with similiar problems.

If you are still having problems post the error number, and description and maybe some one can help.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

starunit SSCommitted Points: 1817 More actions · Answer 6

bitbucket (8/5/2009)
Starunit
When you do get it figured out, and make it work, think about posting your code here so as to help others with similiar problems.
If you are still having problems post the error number, and description and maybe some one can help.

Bitbucket:

It finally starting working again with the Exel 8 / OLEDB.4.0 connection parameters. Wish I knew exactly what the solution was...

I tried a lot of things last week for both OLEDB.4.0 *and* ACE.OLEDB.12.0: no joy on either, including the Driver downloads, registry settings, service account permissions, etc. Gave up on it.

Yesterday I tried moving the file to a path with no dots or spaces, and reinstalled the drivers from http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en. The OLDB 4.0 started working again (yes, I know the drivers are for ACE.OLEDB.12.0 - but i reasoned it couldn't hurt); also I incrementally moved the file to paths that had dots, then spaces, then both, and could not get it to fail.

The only thing I can surmise is that last week I was not 'diligent' about forcing new connections afer each change (I wonder if using a ": Connect servername" at the top would force a new connection each time?).

As for the error:

Msg 7399, Level 16, State 1, Line 79

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 79

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".not surprising; over the years I've grown accustomed to this type of non-informational message.

I'll try the ACE.OLEDB.12.0 connection later this week or next: will post if I find anything.

Cheers,

Mark
Just a cog in the wheel.

ekonagu SSChasing Mays Points: 627 More actions · Answer 7

hello guys

i have got the solution

i have reregistred the msexcl40.dll in registry.now every thing working fine

🙂

ekonagu SSChasing Mays Points: 627 More actions · Answer 8

hello guys

i have got the solution

i have reregistred the msexcl40.dll in registry.now every thing working fine

🙂

starunit SSCommitted Points: 1817 More actions · Answer 9

starunit (8/6/2009)

bitbucket (8/5/2009)
Starunit
When you do get it figured out, and make it work, think about posting your code here so as to help others with similiar problems.
I'll try the ACE.OLEDB.12.0 connection later this week or next: will post if I find anything.
Cheers,

So, after jacking around with OLEDB 12.0 and not getting it to work, went back to check my 4.0, and now it's stopped working again.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 79

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

This is a really sorry state of affairs. Guess I'll just go back to using Access to do my linking and ad-hoc importing. (I really don't like the 2007 version of Access...).

Have a good weekend, I'm out.

Mark
Just a cog in the wheel.

Warren Peace SSCertifiable Points: 5881 More actions · Answer 10

In SQL 2005 SP3, if you installed it, it unchecks the "Enable OPENROWSET and OPENDATASOURCE support".

You will need to put a check in the box. Open the "SQL Server Surface Area Configuration" then click on the link "Surface Area Configuration for Features". You'll see the check box.

starunit SSCommitted Points: 1817 More actions · Answer 11

Warren Peace (8/7/2009)
In SQL 2005 SP3, if you installed it, it unchecks the "Enable OPENROWSET and OPENDATASOURCE support".
You will need to put a check in the box. Open the "SQL Server Surface Area Configuration" then click on the link "Surface Area Configuration for Features". You'll see the check box.

Hi Warren,

Thanks for the note. I had enabled the option a while back and just looked again, thinking SP3 reset the config: no, it was still checked.

The Jet 4.0 was working, then when I tried to get 12.0 to connect, neither it nor 4.0 would work. Then, a few days ago the 4.0 started working again, but I, again, tried to get 12.0 to work, and, again, both started failing.

I have been through numerous posts about it, trying various ways of structuring the connection string.

I'm down to thinking that there is a conflict between the drivers, and that trying one messes with using the other (I know: not logical, just a feeling). The Excel 12 driver *does* appear in the ODBC connection dialog, so I know(?) it's registered.

My connection string, now (and unchanged from what worked before) is:

FROM OPENROWSET(N'Microsoft.Jet.OLEDB.4.0','Excel 8.0; IMEX=1;Database=C:\Users\mstarr.mydomain\Desktop\Various Projects\CorporateRFP\Ratefinding\RateFinder2010\Specs_WorkCopies\FTS_2010_Working.xls', 'select * from Hotels') a

and am getting this as an error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 79

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Which tells me that it cannot find a *valid* file...

But on Wednesday it was working, and I have not changed the file location, SQLServer service account, updates, patches, or any thing else. The only thing I did was to try to connect to the file with the ACE 12.0 driver.

Ah well, I guess I just don't know enough about the inner guts of it to be able to diagnose the problem.

Cheers, though, and I'll be watching for other comments and ideas.

Mark

Mark
Just a cog in the wheel.

Warren Peace SSCertifiable Points: 5881 More actions · Answer 12

A last ditch effort would be to export all yout spreadsheets to a comma seperated file, or whatever yout choice, as individual text file then import those.

Unless there's a specific reason it has to stay in Excell.

starunit SSCommitted Points: 1817 More actions · Answer 13

There is an interesting post at :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=11

by Peso:

If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls',
'SELECT * FROM [Sheet1$]')
Now most error desriptions are output. When you have resolved the error, switch back to Jet again.

So, doing that I get:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] System resource exceeded.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

System resource exceeded? What the... I just booted the system. Hmm...

Keep looking

Update: 13:32pm

Sorry: I do know this thread was really for Excel 2007, and I diverged to to the earier version because my JET driver stopped working too. This'll be my last entry on this thread.

JET.OLEDB.4.0 is working again...

Read a lot of posts on various sites, looking for clues.

One person said that after an error occurred, he had ro restart the server to clear it, otherwise the error would happen no matter what he'd do. Others said to change MSDASQL to 'Allow in Process'. I had already and repeatedly done that..

Here's what I did:

- uninstall the Office 2007 drivers that I previously installed from http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

- reinstall MDAC http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en

- worked through as many of the steps as possible as outlined by "GK8778" at:

http://social.technet.microsoft.com/Forums/es-ES/sqldataaccess/thread/05aaccd6-2a38-4784-b310-99e34b8d1a35

- rebooted my machine.

Now it works. Huh.

here's a couple more reference links:

http://support.microsoft.com/kb/321686

http://support.microsoft.com/kb/306397

one more important url:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/26fcf042-bb86-43d2-af18-d7b57437c7db/

from Seradex:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.
Close the Sql-Management-Studio, open RegEdit and search for the specific key. If you have more than one instance, use the right internal name (MSSQL.1 / MSSQL.2 etc.).
Change the value to 0
Restart your MS-SqlServer. Using Reconfigure doesn't help

Mark
Just a cog in the wheel.

ekonagu SSChasing Mays Points: 627 More actions · Answer 14

if u r using office 2007 u should use oledb 12.0 not jet 4.0