title: Quirky SELECT from Excel file via OleDbDataAdapter method (C#)
body: <p>I have got an Excel file in this form :</p>
  
  <pre><code>Column 1    Column 2    Column 3  
   data1        data2    
   data1        data2  
   data1        data2  
   data1        data2  
   data1        data2       data3  
  </code></pre>
  
  <p>That is, the whole Column 3 is empty except for the last row.
  I am accessing the Excel file via OleDbDataAdapter, returning a DataTable: here's the code.  </p>
  
  <pre><code>query = "SELECT * FROM [" + query + "]";
  objDT = new DataTable();
  objCmdSQL = this.GetCommand();
  objCmdSQL.CommandText = query;
  objSQLDad = new OleDbDataAdapter(objCmdSQL);
  objSQLDad.Fill(objDT);
  return objDT;
  </code></pre>
  
  <p>The point is, in this scenario my code returns a DataTable with just Column 1 and Column 2.<br>
  My guess is that JET engine tries to infer column type by the type of the very first cell in every column; being the first value null, the whole column is ignored.<br>
  I tried to fill in zeros and this code is actually returning all three columns; this is obviously the least preferable solution because I have to process large numbers of small files.<br>
  Inverting the selection range (from, i.e. "A1:C5" to "C5:A1" ) doesn't work either.
  I'm looking for something more elegant.<br>
  I have already found a couple of posts discussing type mismatch (varchar cells in int columns and vice versa) but actually haven't found anything related to this one.<br>
  Thanks for reading!</p>
  
  <p><em>edit</em> </p>
  
  <p>Weird behavior again. I have to work on mostly Excel 2003 .xls files, but since this question has been answered I thought I could test my code against Excel 2007 .xslx files.
  The connection string is the following:</p>
  
  <pre><code>string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + _fileName.Trim() + @";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;""";
  </code></pre>
  
  <p>I get the "External table is not in the expected format" exception which I reckon is the standard exception when there is a version mismatch between ACE/JET and the file being opened.</p>
  
  <p>The string </p>
  
  <pre><code>Provider=Microsoft.ACE.OLEDB.12.0 
  </code></pre>
  
  <p>means that I am using the most recent version of OLEDB, I took a quick peek around and this version is used everywhere there is need of connecting to .xlsx files.<br>
  I have tried with just a vanilla provider ( just Excel 12.0, without IMEX nor HDR ) but I get the same exception.<br>
  I am on .NET 2.0.50727 SP2, maybe time to upgrade?</p>