title: Reading excel data when the file is open
body: <p>Few days back I asked a <a href="https://stackoverflow.com/questions/12858310/reading-excel-fetchig-date-in-general-format#comment17403185_12858310">question</a> but could not find enough answers. I analyzed the problem a bit more and decided to create a new thread. If I should have not, let me know or close the question.</p>
  
  <p>I have an <strong>OPEN</strong> excel file with a column in Date format with some values like 6/22/2006, 6/22/2006. I am trying to read the excel using OleDbDataReader. The code is like this:</p>
  
  <pre><code>string sql = string.Format("SELECT * FROM [{0}]", excelSheetName);
  internal OleDbCommand command = new OleDbCommand();
  command.CommandText = sql;
  OleDbDataReader oleRdr = command.ExecuteReader();
  
  DataTable dataTable = new DataTable();
  datatable.TableName = excelSheetName;
  dataTable.Load(oleRdr);
  </code></pre>
  
  <p>I am using the connection string as:</p>
  
  <pre><code>string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ExcelData\sample.xls;Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
  </code></pre>
  
  <p>The problem is I am not able to read the date values in correct (<code>DateTime</code>) format. I believe everything is treated as numeric format. So my result is</p>
  
  <p><code>6/22/2006 as 38890</code></p>
  
  <p>After reading some articles on internet, I understood my column is having multiple datatypes and then I changed my connection string as:</p>
  
  <pre><code>string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ExcelData\sample.xls;Extended Properties="Excel 8.0;HDR=No;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0"";
  </code></pre>
  
  <p>But still I am not able to read the excel in correct format. Any suggestions on this. It looks a weird problem to me. Please let me know if I to need to provide some more information.</p>
  
  <p>Note: Also make a note that when my excel file is closed both the connection strings give me the correct data. Also for new doc formats<code>.xlsx</code> we are using <code>Microsoft.ACE.OLEDB.12.0</code>.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I tried editing the registry as well....but no relief.
  But when I changed the value <code>IMEX = 0</code> or <code>IMEX = 1</code> it is working fine again. Can any one tell the exactly what is happening?</p>
  
  <p><strong>EDIT 2</strong>
  When I changed <code>HDR=Yes</code> still everything worked good. Any help please? I could not find enough help on internet.</p>