Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Fix for Exception when connecting to an Excel .xls file using .NET.?

I am having difficulty connecting to an Excel  .xls file (used as a DB source) in .NET.

When I try to connect using .NET and the following connection string I get an exception:

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "C:\data.xls; Extended Properties=""Excel 8.0; HDR=YES; """

The exception says that the Microsoft.Jet.OLDDB.4.0 provider is not registered.

I am using VS 2005,  and Excel from Microsoft Office 2003.

Does anyone have a fix for this or an alternative solution?

 I need to input the data in an Excel file, make changes and then output to an SQL Server 2005 table.

Thanks,
TALJr
0
TALJr
Asked:
TALJr
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assume that you are using it like this:
OleDbConnection con = new OleDbConnection(strConn);


0
 
TALJrAuthor Commented:
Here's the code:
OleDbConnection Conn = new OleDbConnection;
Conn.ConnectionString = strConn;
Conn.Open()

Thanks,
TALJr
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you already check out this support note:
http://support.microsoft.com/kb/278604
0
 
DhaestCommented:
Can you give this a try:
        /// <summary>
        /// Function to load a XLS-file into a datatable
        /// </summary>
        /// <param name="strFilename">FileName we want to load into a datatable</param>
        /// <param name="bHasHeader">Bool to say if there are column-names</param>
        /// <returns>Datatable containing the contents of the xls-file</returns>
        public static DataTable LoadXLSFile(string strFilename, bool bHasHeader)
        {
            try
            {
                if (System.IO.File.Exists(strFilename) == false)
                    throw new Exception("ERROR: File does not exist (or not enough rights) on the given location!");

                string path = System.IO.Path.GetDirectoryName(strFilename);
                string file = System.IO.Path.GetFileName(strFilename);

                string strConn = "";
                if (bHasHeader)
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilename + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0;\"";
                else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilename + ";Extended Properties=\"text;HDR=No;IMEX=0;FMT=Delimited\"";
                //strConn = "DRIVER=Microsoft Excel Driver (*.xls);DriverId=790;DefaultDir=C:\\;DBQ=" + path;
                DataTable dt = new DataTable();

                using (OleDbConnection con = new OleDbConnection(strConn))
                {
                    con.Open();
                    // Get the name of the first worksheet:
                    DataTable dbSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dbSchema == null || dbSchema.Rows.Count < 1)
                    {
                        throw new Exception("ERROR: Could not determine the name of the first worksheet.");
                    }
                    string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

                    using (OleDbCommand com = new OleDbCommand("select * from [" + firstSheetName + "]", con))
                    {
                        using (OleDbDataAdapter da = new OleDbDataAdapter(com))
                        {
                            try
                            {
                                da.Fill(dt);
                                con.Close();
                                com.Dispose();
                                con.Dispose();
                            }
                            catch (Exception ex)
                            {
                                throw ex;
                            }
                            finally
                            {
                                if (con.State == ConnectionState.Open)
                                    con.Close();
                            }
                        }
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now