Enterprise Library January 2006
Visual Studio 2005
Lanugage: C#
I am attempting to use the Enterprise Library to read data from an
Excel spreadsheet file. I got the following connection string from
connectionstrings.com:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended
Properties="Excel 8.0;HDR=Yes;IMEX=1"
But when I use this with the Enterprise Library, it generates an
exception that the Provider keyword is not valid. Here is the text of
the exception:
System.ArgumentException
Keyword not supported: 'provider'.: at
System.Data.Common.DbConnectionOptions.ParseIntern al(Hashtable
parsetable, String connectionString, Boolean buildChain, Hashtable
synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(Strin g
connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(St ring
connectionString)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnectionOptions(String
connectionString, DbConnectionOptions previous)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnectionPoolGroup(String
connectionString, DbConnectionPoolGroupOptions poolOptions,
DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionStri ng_Set(String
value)
at System.Data.SqlClient.SqlConnection.set_Connection String(String
value)
at
Microsoft.Practices.EnterpriseLibrary.Data.Databas e.CreateConnection()
at
Microsoft.Practices.EnterpriseLibrary.Data.Databas e.OpenConnection()
at
Microsoft.Practices.EnterpriseLibrary.Data.Databas e.ExecuteReader(DbCommand
command)
Can anyone provide me with some guidance in using the Enterprise
Library to read an Excel file correctly? I can use it no problem with
SQL Server.
The Excel file in question has only 5 columns, all integer type. I
wish to read the data from this file and insert it into a sql table.
Import won't work as this will have to be done on a regular basis and
there is some simple calculations that need to be performed.
Any help is appreciated.
Thanks,
Chris