Problem importing Excel data into SSMS

Hi,

When I try to use the Import and Export Wizard to import a good .xls file I keep getting this error:

"Could not find installable ISAM. (Microsoft JET Database Engine)"

It occurs in both SSMS or SSIS. It also occurs with other flat files like .csv.

The OS is XP, w/ office 2003.

here are the full error details:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

I don't understand why OleDb connection is involved to the Jet when this is SQL2K5 full version.

Please anyone help.

Carl

http://databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html for all the issues to resolve.|||

Thanks Satya,

This link is dead. I had previously found this resource and chased down the Google cached version

http://72.14.253.104/search?q=cache:Vyh8_PKhHrwJ:databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html+how-do-i-solve-could-not-find-installable-isam-errors.html&hl=en&lr=&strip=1

All it says is to reinstall the MDAC. Well, when I try to do that the system (XP) says there is no need for it as a more recent version is included as part of the OS.

This article describes registry changes to the Jet engine.

http://support.microsoft.com/default.aspx/kb/283881

This machine's registry does not have these keys at all, even though both Excel 2k3 and Access 2k3 work just fine. The fix is to reinstall or repair the Office Pro installation.

I have poor experiences with reinstalling Office 2003. Many customized settings are kept in the registry and wiped out in the reinstall/repair. Before I embark on this journey I don't understand why I even need Office for the operation I am trying to do?

BTW the same error occurred when I tried to import a csv file too, not just an .xls

I wrote a little c# class to convert the file to xml and then standard DAL to "import" the data into SQL Server db. Worked like a charm, but in the future I would still like to be able to do it in SSMS. Most likely on a remote server there would not even be any installation of Office.

Thanks again

Carl

|||See this blog entry too http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx and I appreciate your feedback.|||

Satya,

great tip here http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx

They talk about extended properties in a connection string.

If I were not doing the connection programmatically but using the SSMS Object explorer, where should I find the connection for the (local) server instance?

Databases -> MyDatabase ->Security ->Users ->dbo
Here there is a link in the lower left to view the connection properties but no extended properties field.

The only place I could find such a field is in
Databases -> System Databases -> master ->Security ->Users -> SERVERNAME\SQLServer2005MSFTEUser$SERVERNAME$MSSQLSERVER

That however is not the db I want to import to. I added an extended property anyway "Excel 8.0;IMEX=1" just for kicks but it didn't do any good.

Any more ideas?

Carl

|||

You can use SP_ADDEXTENDEDPROPERTY as per this http://msdn2.microsoft.com/en-us/library/ms190243.aspx link.

|||

My question was not how to do it programmatically but how to do it using the SSMS GUIs.

I guess the pros do not like using the GUIs too much, and I am starting to see why.

Thanks

Carl