none
Problem of acessing excel files using Jet OLEDB 4.0 RRS feed

  • Question

  • I am using Jet OLEDB 4.0 to access excel files. It seems that it will not work if the worksheet name of a excel file starts with a numeric but works fine otherwise. For example if the worksheet name is 1Sheet1, it will not work but if the worksheet name is Sheet1, it will work fine.

     

     The code is as follows:

     

    //Start of Code

    CDataSource _datasource;

    CSession      _session;

    CColumns*   _columns

     

    _datasource.OpenFromInitializationString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Test.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2';"", false);

     

    _session.Open(_datasource);

     

    _columns->Open(_session, NULL, NULL, "Sheet1$", NULL);

    //_columns->Open(_session, NULL, NULL, "1Sheet1$", NULL);

     

    HRESULT hr = _columns->MoveFirst();

     

    //End of code

     

    For the code shown above, if i use

    _columns->Open(_session, NULL, NULL, "Sheet1$", NULL);, the _columns->MoveFirst(); will return S_OK.

     

    But if i use _columns->Open(_session, NULL, NULL, "1Sheet1$", NULL);, the _columns->MoveFirst(); will return DB_S_ENDOFROWSET even though i have a worksheet named 1Sheet1 in Test.xls

     

    This also happens if the worksheet name has a "-" (eg Sheet-1). Does anybody know what is the problem?

    Wednesday, February 20, 2008 8:45 AM

All replies

  • Try to put spreadsheet name into square brackets, like [1Sheet1$]. In a case if table name (spreadsheet name) and column name contains any characters that produce non-standard identifier, you need to put it into square brackets to make it work properly.

    Wednesday, February 20, 2008 10:35 AM
    Moderator
  • do u mean _columns->Open(_session, NULL, NULL, "[1Sheet1$]", NULL); or u mean the put [] over the worksheet name at Test.xls?

     

    Wednesday, February 20, 2008 10:41 AM
  • Yes, use     _columns->Open(_session, NULL, NULL, "[1Sheet1$]", NULL);

     

    or something like

     

    _columns->Open(_session, NULL, NULL, "SELECT * FROM [1Sheet1$]", NULL);

     

    Wednesday, February 20, 2008 10:52 AM
    Moderator
  •  VMazur wrote:

    Yes, use     _columns->Open(_session, NULL, NULL, "[1Sheet1$]", NULL);

     

    or something like

     

    _columns->Open(_session, NULL, NULL, "SELECT * FROM [1Sheet1$]", NULL);

     

     

    I tried _columns->Open(_session, NULL, NULL, "[1Sheet1$]", NULL);  but it does not work.

     

    Somebody from another forum suggested enclosing "1Sheet1$" inside single quotes, as in " '1Sheet1$' ".

    I tried _columns->Open(_session, NULL, NULL, "'[1Sheet1$]'", NULL); and it works.

     

    However enclosing "Sheet1$" in single quotes does not work. Eg _columns->Open(_session, NULL, NULL, "'[Sheet1$]'",  NULL); does not work.

     

    Does anyone know why? As the worksheet name is not hard coded the single quotes does not help to solve my problem.

    Thursday, February 21, 2008 7:38 AM
  • I think to use just a spreadsheet name as a command you need to specify specific type of the command. What happens if you use SELECT * FROM [1Sheet1$] instead?

    Thursday, February 21, 2008 11:05 AM
    Moderator
  •  VMazur wrote:

    I think to use just a spreadsheet name as a command you need to specify specific type of the command. What happens if you use SELECT * FROM [1Sheet1$] instead?

     

     

    Sorry my mistake, there should be no square brackets.

     _columns->Open(_session, NULL, NULL, "[1Sheet1$]", NULL);  does not work.

    _columns->Open(_session, NULL, NULL, "'[Sheet1$]'", NULL); does not work

     

     

    In Summary:

     

    enclosing "1Sheet1$" inside single quotes, as in " '1Sheet1$' ". works, meaning

    _columns->Open(_session, NULL, NULL, "'1Sheet1$'", NULL); works

     

    but enclosing "Sheet1$" inside single quotes, as in " 'Sheet1$' " does not work, meaning

    _columns->Open(_session, NULL, NULL, "'Sheet1$'", NULL); does not works

     

    No single quotes works for "Sheet1$" but not for "1Sheet1$". Meaning :

    _columns->Open(_session, NULL, NULL, "1Sheet1$", NULL); does not work but

    _columns->Open(_session, NULL, NULL, "Sheet1$", NULL); works.

     

    Therefore "1Sheet1$" needs single quotes and "Sheet1$" cannot have single quotes.

     

    I tried both _columns->Open(_session, NULL, NULL, SELECT * FROM [1Sheet1$]", NULL);  and  _columns->Open(_session, NULL, NULL, SELECT * FROM 1Sheet1$", NULL); It does not work and both will return DB_S_ENDOFROWSET when _columns->MoveFirst(); is called

    Friday, February 22, 2008 3:57 AM