OleDbDataAdapter not reading all values RRS feed

  • Question

  • I am trying to read excel values using OleDbDataAdapter it reads values but misses some of them. My code is as follows

     public void ResultDetails(string fileName)
                DataTable dtExcel = new DataTable();
                    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'");
                    if (conn.State != ConnectionState.Open)
                               OleDbDataAdapter result = new OleDbDataAdapter("Select * from [Result$]", conn);
    } catch{} 
    It misses values at point when my excel sheet has only one cell filled after multiple blank cells.

    Saurabh Kumar

    Tuesday, January 27, 2015 11:07 AM


All replies

  • You can start by adding the IMEX argument to your connection string. See the below link for an example and the other changes that may be required:

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by saurabhKumar Thursday, January 29, 2015 9:39 AM
    Tuesday, January 27, 2015 1:25 PM
  • Thanks Paul I have tried using IMEX but nothing changes. Please refer my code again I have updated it. 

    Saurabh Kumar

    Tuesday, January 27, 2015 2:23 PM
  • Did you try the Registry settings as discussed in the forum thread?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, January 27, 2015 6:27 PM
  • Hi Paul I tried TypeGuessRows to 0. But nothing changed. But when i marked excel columns that were not getting read to 'Normal' type. It started reading values. So what can now be done to code? 

    Saurabh Kumar

    Wednesday, January 28, 2015 8:21 AM
  • How about ImportMixedTypes? Did that change anything?

    Not sure what you mean by "marked excel columns that were not getting read to 'Normal' type".

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 28, 2015 5:44 PM
  • I meant the excel sheet that I was trying to import using OleDbDataAdapter got import when I changed 'Cell Styles' of the sheet to 'Normal'.

    Saurabh Kumar

    Thursday, January 29, 2015 3:43 AM
  • Hello saurabhKumar,

    Could you please share information as what the version of Excel you are using and also share some data with us?

    >> when I changed 'Cell Styles' of the sheet to 'Normal'.

    According to your description and code, after making a test, the result is that the data in the excel file are all shown in the dataset even I marked some of them with a non-normal Cell Studyles. Could you please tell is which styles you set the cell to be?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 29, 2015 9:10 AM
  • Thanks Paul. I am using Excel 2007. How do i provide you excel sheet in this forum is their anyway actually i am new to forum.

    Saurabh Kumar

    Thursday, January 29, 2015 9:40 AM