none
Cannot read xls file - microsoft jet database engine could not find the object error RRS feed

  • Question

  • Hi there,

    I'm trying to read .xls file using OleDb connection. I have two .xls files generated from SAP (actually these are Unicode text files saved as .xls) with two different names. When I execute my code I can only open one file, the second one gives an error:

    System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'sheetName$'. Make sure the object exists and that you spell its name and the path name correctly.

    Both files contains only one sheet which has the same name as the file itself. I've checked the spelling several times and even changed it manually but it doesn't work on one file. Even when I saved the file as proper Excel file.  Here's the code I'm using:

    //opens file and fills the DataGridView with data from file
                        //
                        string fileNameWithExt;
                        string fileNameWithoutExt;
                        int fileNameLength = 0;
                        int cutExtension = 4;
                        fileNameWithExt = openFileDialog1.SafeFileName;
                        fileNameLength = fileNameWithExt.Length;
                        fileNameWithoutExt = fileNameWithExt.Remove(fileNameLength - cutExtension);
    
    OleDbConnection aConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\" + fileNameWithExt + ";Extended Properties=Excel 8.0");
                            aConnection.Open();
    
                            DataTable sampleDataTable = new DataTable();
    
                            //clears data table and dataGridView before creating a new one
                            dataGridView1.DataSource = null;
                            sampleDataTable.Clear();
                            sampleDataTable.Columns.Clear();
    
                        
                            OleDbDataAdapter oleDbCommand = new OleDbDataAdapter("Select * from ["+fileNameWithoutExt+"$]", aConnection);
                            oleDbCommand.Fill(sampleDataTable);
    I hope somebody can point me in the right direction as I checked plenty of posts and know that something is wrong with the sheet name but can find the issue.

    Regards,
    Saturday, April 25, 2009 8:08 PM

All replies

  • If files are text files with xls extension then you should read them as text files, not as Excel 8.0 files. When you specify Excel 8.0 Jet provider relies on internal format of the file, not on extension to read data from it. When you open file in Excel it should you the spreadsheet name, but in reality it does not exist because text files do not contain any spreadhseet names. Excel just shows filename for the spreadsheet name. If you really want to query spreadsheets then you need to save data into the actual xls file format.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, April 27, 2009 10:20 AM
    Moderator
  • Your SELECT statement refers to a file name when it should be referencing a Worksheet name in the Workbook.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, April 27, 2009 2:33 PM
  • Note you can enumerate the sheet names using this method (http://www.codeproject.com/KB/office/ExcelToDataset.aspx?display=Print)

            DataTable schemaTable = conn.GetOleDbSchemaTable(
              OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            foreach (DataRow schemaRow in schemaTable.Rows) {
                string sheet = schemaRow["TABLE_NAME"].ToString();

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                cmd.CommandType = CommandType.Text;

                DataTable outputTable = new DataTable(sheet);
                output.Tables.Add(outputTable);
                new OleDbDataAdapter(cmd).Fill(outputTable);
            }

    Monday, May 4, 2009 1:49 AM
    Moderator