0

I'm working on an existing C# code reading Excel file with OleDbDataReader. But I can't have the content for the cells in two specific columns.

This is the connection code:

connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
                + pathExcel + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";");
connection.Open();

And to access the content of the default sheet:

tables = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
sheetsNames = from DataRow row in tables.Rows select row["TABLE_NAME"].ToString();
sql = "SELECT * FROM [" + sheetsNames.FirstOrDefault() + "];";
ocmd = new OleDbCommand(sql, connection);
reader = ocmd.ExecuteReader(); //OleDbDataReader

So, finally I read all the content, but for some columns I can't access cells content (reader["mycolumn"]). So, I tried this:

while (reader.Read()){
   // Test code, I tried different ways to read cell content
   // It's working
   string colName = reader.GetName(26);
   string val1 = reader[colName].ToString();
   string val2 = reader.GetValue(26).ToString();

   // Same code, changing index 26 to 27
   ... // always empty values. Bug ??
}

If I evaluate expressions "reader.GetValue(26)" it returns the expected value, but when it's "reader.GetValue(27)" it's returns an exception ("This expression causes side effects and will not be evaluated"), in particular it's like an index out of range exception. But I can read data from next columns (29, 30...).

Do you have any idea about the cause ?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Rififi
  • 121
  • 1
  • 11
  • are there first few cells in the columns which throw exceptions empty? –  May 11 '15 at 15:27
  • I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders May 11 '15 at 15:37
  • Try checking [`OleDbDataReader.IsDBNull`](https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.isdbnull%28v=vs.110%29.aspx). – dbc May 11 '15 at 15:37
  • no. the first cell contains the column title and other cells are empty or contain "X". – Rififi May 11 '15 at 15:37
  • @Rififi - possibly a duplicate of http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data or http://stackoverflow.com/questions/12279258/datareader-does-not-see-data-in-excel-cell-if-previous-cell-in-the-column-are-em. Try "TypeGuessRows=0;ImportMixedTypes=Text". – dbc May 12 '15 at 21:59

0 Answers0