locked
OleDbDataAdapter missing out collumns RRS feed

  • Question

  • I'm using the OleDbDataAdapter to read an excel file, however I've noticed that it misses out every 14th column from the data set (i.e. that column [0], [13], [27] etc all contain null values) every other column however is properly returned

    heres the code i use to get the dataset:

    var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
    var adapter = new OleDbDataAdapter("SELECT * FROM [sheet$]", connectionString);
    var ds = new DataSet();
    adapter.Fill(ds, "anyNameHere");
    
    Thanks

    Tuesday, October 12, 2010 12:22 PM

Answers

  • Correct your connection string like this.

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;\"";
    
    IMEX=1 will force scanning for all columns for data, sometime this will get ignored i guess that is the issue which you are facing.
    • Proposed as answer by Alan_chen Thursday, October 14, 2010 6:52 AM
    • Marked as answer by Alan_chen Monday, October 18, 2010 2:34 AM
    Tuesday, October 12, 2010 1:11 PM

All replies

  • How do you know that you are missing these columns?

    How do you retrieve / use the values from the adapter?

     

    Noam B.



    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...
    Tuesday, October 12, 2010 12:50 PM
  • Correct your connection string like this.

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;\"";
    
    IMEX=1 will force scanning for all columns for data, sometime this will get ignored i guess that is the issue which you are facing.
    • Proposed as answer by Alan_chen Thursday, October 14, 2010 6:52 AM
    • Marked as answer by Alan_chen Monday, October 18, 2010 2:34 AM
    Tuesday, October 12, 2010 1:11 PM