none
Excel 2.0 into OleDB DataSet RRS feed

  • Question

  •  

    I have some code for filling a dataset from Excel, however, it will not work with an Excel 2.0 file.

    My data string is:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='"Excel 8.0;HDR=Yes;IMEX=1"'


    I tried changing

    Extended Properties="Excel 2.0 ..."

    But it wasn't recognized.

    Is there a way to read an Excel 2.0 sheet into an OleDB DataSet
    Friday, September 5, 2008 10:13 PM

Answers

  • There is no support for version 2.0. There is only 5.0 and 8.0 if you're using the Jet OLEDB provider. I would suggest updating the file.

     

    Monday, September 15, 2008 1:33 PM

All replies

  • This is code that I have used to open a Excel 2007 xls file

     

    DataTable excelDataTable = new DataTable();

    OleDbConnection objConn = null;

    try

    {

    objConn = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + excelFilePath + "\";Extended Properties=\"Excel 12.0;HDR=Yes;\";" );

    objConn.Open();

    OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [(SHEETNAMEHERE)]", objConn);

    OleDbDataAdapter daCSV = new OleDbDataAdapter();

    daCSV.SelectCommand = cmdSelect;

    daCSV.Fill(excelDataTable);

    daCSV = null;

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.ToString());

    }

     

    I believe your problem is creating an oledb connection, so I am going to move this post to an excel forum.

     

    Thanks

    Chris Robinson

    Software Developer in Test- DataSet

    Wednesday, September 10, 2008 11:24 PM
  • I cannot find the proper Office Forum to refer your post to, please let me know if the above post helps. Also if you are still obtaining the error can you specify the version of office this documet is. I am unsure what version 2.0 is. Is it Excel XP, 2003, or 2007, or an earlier version?

     

    Thanks

    Chris Robinson

    Software Developer in Test- DataSet

     

    Wednesday, September 10, 2008 11:32 PM
  •  

    yes, the error is in creating the OleDbConnection.

     

    The file is actually Excel 2.1 -- I don't know what that translates to in product name.

     

    When I try to do the open (using the same code as you show) I get a

     

    Table is not in specified format

     

    I have to read the 2.1 worksheet into Excel automation object, and save it as a 2003 file and then I can read it.

     

    I would rather not do this as I'm writing an app designed to run as a scheduled task and I get problems using the Excel object -- it stays in memory.

     

    Is there some "batch" way to convert the file w/o using Excel Interop per se?

     

    Or do you think there's a way to make Ole able to read a 2.1 spreadsheet.

     

    If you want to repeat my example, save your spreadsheet as Excel 2.1 and try and read it with your code.

     

    Wednesday, September 10, 2008 11:40 PM
  • There is no support for version 2.0. There is only 5.0 and 8.0 if you're using the Jet OLEDB provider. I would suggest updating the file.

     

    Monday, September 15, 2008 1:33 PM