locked
[.xls && .xlsc]External table is not in the expected format RRS feed

  • Question

  • gud day,

    i'm developing a application that will open the excel and display its fields into my datagrid, while developing my application i do get an error "External table is not in the expected format."

    below is my code on how i develop my application.
            private void btnExtract_Click(object sender, EventArgs e)
            {
                OleDbConnection connect = new OleDbConnection(connectionString(txtOpenExcel.Text));
                OleDbDataAdapter oleda = new OleDbDataAdapter("Select * from [bugs$]", connect);
                DataSet ds = new DataSet();
                oleda.Fill(ds, "ExcelInfo");
                dataGridView1.DataSource = ds.Tables["ExcelInfo"].DefaultView;
                dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            }
    
    
            public string connectionString(string path)
            {
                string conn = "";
                string extension;
                extension = Path.GetExtension(path);
                if (extension == ".xls")
                {
                    conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                }
                else if (extension == ".xlsc")
                {
                    conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
                }
                return conn;
            }    

    any comments and suggestion is much appreciated.
    Sunday, February 28, 2010 3:43 AM

All replies

  • gud day,

    i haved if i removed \" in my connection string i get "cannot find installable isam" which code is correct?? 

    if so, how do i get over the "cannot find installable isam???

    tnx
    Sunday, February 28, 2010 5:45 AM
  • gud day,

    i think cannot find installable isam is occuring becuase i do not have oledb connections in my data link dialog box. how can i import oledb connections in the data link dialog box. i have already installed "AccessDatabaseEngine" into my system. but oledb is still not availabe in my data link dialog box.

    tnx
    Sunday, February 28, 2010 6:00 AM
  • gud day, 

    i would correct my above reply. i do have oledb in my connections which allow you access  MSAccessdatabase. but i do not have jet4.0 oledb connections. where can i download it??
    Sunday, February 28, 2010 6:06 AM
  • gud day,

    i'am bit confused already. i do have msjet40.dll in my system. but i cannot find jet4.0 in my data link properties:(( i badly need your help guys
    Sunday, February 28, 2010 6:19 AM
  • i was able to find jet 4.0 in my oledb. able to set the connection. but when i'm trying to test the connection in returns. "External tables are not in the expected format"
    Sunday, February 28, 2010 6:45 AM
  • Hi

    There is nothing wrong with your code. Are you getting the error before binding the data to gridView or while binding the data?
    Please mark the post as answered if my post is the answer for your question, and mark other helpful posts as helpful. Thanks Vijay
    Sunday, February 28, 2010 6:52 AM
  • gud day,

    ive already identified what causes my problem. it is because the excel file that i'm trying to import is an HTML table which means i needed to change the my connection string to:

    conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=HTML Import"

    i were able to overcome the external table error. but this time i do get 

    The Microsoft Jet database engine could not find the object 'bugs$'.  Make sure the object exists and that you spell its name and the path name correctly.

    the filename of the excle that i'm trying to import is bugs, its sheet named also bugs. when i try to change the into Sheet1. it also returns the same error.

    do you any ideas on how to workaround into this error.

    i badly need your help guys. 

    Sunday, February 28, 2010 9:30 AM
  • Try removing the '$' character from the Worksheet name to see if that works.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 1, 2010 1:44 PM
  • i have tried removing the $ but still it doesnt work.
    Friday, March 5, 2010 1:57 AM
  • I not aware of any issue using the name "bugs" but have you tried using a different sheet name?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, March 5, 2010 12:43 PM
  • Dear MVP,

    This problem goes back to 2006! I've seen a post from 21/07/10 in other forums too.

    I also have this problem. I have a webpage file with an ".xls" extension (Excel 97 - 2003).

    So, my connection string has an extended properties which equals to "HTML Import". The problem is "talking" to the sheet which contains the data.

    I am programming in Excel VBA (VB 6.5).

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    ' Connection String - works fine:

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Documents and Settings\Avi_Se\My Documents\Projects\Tall Tree\I.A.A\Logistics\Correspondence\Reports\Files Reports\Report Generator\Worklanes_Milestones.XLS;" & _
    "Extended Properties=""HTML Import;HDR=YES"";"

    ' Recordset - doesn't work:

    rst.Open "SELECT * FROM [Sheet1$]", cnn, adOpenStatic, adLockReadOnly

    The message I get:

    Run-time error '-2147217865 (80040e37)':

    The Microsoft Jet database engine could not find the object 'sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

    Tried:

    with / without the "$", with / without the "HDR", change the sheet name to a different name from the file name.

    I don't want to open the file and save it as an Excel format, because it is to big, and that's an overriding of the problem.

    The Href of the file to the sheet is "sheet001", that name doesn't do the job either.

    Any suggestion? (which were tried and worked properly)

    Best Regards,

    Avi Serour

    Friday, August 13, 2010 8:03 PM