none
Problem with reading Excel file with OleDB RRS feed

  • Question

  • Hello,

    In a program i develop for a sport club, I have got a problem.
    I have to read differents Excel files (xls) with almost the same format. There is documents which listing members and their points and results.

    I decided to use OleDB. So, after i opened my file, i get back a sheet. To do that i do a request:

    OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + listname + "$]", con);

    It works but only in certain cases. In fact, "listname" contains the name of the sheet i previously placed in a comboBox like that:

    DataTable myDataTable = new DataTable();
    myDataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    con.Close();
    cb_Categ.Items.Clear();
    for (int i = 0; i < myDataTable.Rows.Count; i++)
    {
    DataRow dr = myDataTable.Rows[i];
    String tabname = dr["TABLE_NAME"].ToString();
    String cat = dr.ItemArray[2].ToString();
    String[] catTmp = cat.Split('$');
    cb_Categ.Items.Add(catTmp[0]);
    cb_Categ.Items.Add(tabname);
    }

    cb_Categ.SelectedIndex = 0;

    Then, i get back the wanted sheet name:

    String categTmp = cb_Categ.SelectedItem.ToString();

    My problem is that somethime, the name of the sheet that i got back is not accepted by the OleDB request.
    I get the following error:

    " ... Invalid bracketing of name ' SD$' ..."
    or

    "... the JET database engine couldn't find the object 'SD $$' ..."

    I can't fin a logic in this bug.

    A way to resolve the broblem could be to get back the sheets by index, but i don't know how to do it with OleDB.

    Thanks for your help,

    Léo Lejeune.

    Thursday, April 7, 2011 4:10 PM

Answers

  • I don't believe you need to append the '$' to the returned Worksheet name - it should be included when using GetOleDbSchemaTable. Double check this because I believe some methods for returning schema return both versions (with and without the $) of the Worksheet name.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Léo Lejeune Saturday, April 9, 2011 12:54 PM
    Thursday, April 7, 2011 4:26 PM

All replies

  • I don't believe you need to append the '$' to the returned Worksheet name - it should be included when using GetOleDbSchemaTable. Double check this because I believe some methods for returning schema return both versions (with and without the $) of the Worksheet name.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Léo Lejeune Saturday, April 9, 2011 12:54 PM
    Thursday, April 7, 2011 4:26 PM
  • Correct answer.

    It was aparently correct. The GetOleDbSchemaTable function return the sheets names in two versions.

    Thanks you for your help,

    Léo Lejeune.

    Saturday, April 9, 2011 12:54 PM