title: .NET DataTable is mangling date field from an Excel file
body: <p>I'm writing a lot of Extract Transform Load code right now for work. To make it easier to read in data from user-generated Excel spreadsheets from within SSIS, I've written a little library that reads the column header layout from an Excel file and dumps the data into a table in a MS SQL database that has the same schema as the Excel file. It's working great so far, but I'm having a couple of odd hiccups.</p>
  
  <p>(Yes, I know that SSIS supports stuff like this natively. There are technical reasons for the approach I've chosen, and explaining them would go beyond the scope of this post.)</p>
  
  <p>The problem is that with some input files, any columns with date information get mangled. For instance, when I perform the import, the date string <code>12/09/2013</code> gets mangled into <code>41617</code>; <code>Dec 9, 2013 9:19:58 AM</code> becomes <code>41617.388866</code>. I've checking to see if it's trying to treat <code>12/09/2013</code> as a mathematical equation, but <code>(12/09)/2013 = 6.62361</code>. Also, I've checked the cell formatting on this spreadsheet as well as a few others that are importing date fields without errors, and it's the same everywhere - Custom, mm/dd/yyyy.</p>
  
  <p>The biggest problem here is that, due to the nature of the process I've written, everything hinges on my not knowing the structure of the file at runtime. I can transform the data on the fly, but I have no idea how to work backwards from <code>41617</code> to get to <code>12/09/2013</code>.</p>
  
  <p>DateTime.TryParse is also unhelpful:</p>
  
  <pre><code>DateTime dt;
  DateTime.TryParse("41617", out dt);
  Console.WriteLine(dt.ToString());
  </code></pre>
  
  <p>outputs</p>
  
  <pre><code>1/1/0001 12:00:00 AM
  </code></pre>
  
  <p>Here's a link to a stripped, de-identified copy of the spreadsheet that I'm having trouble with: <a href="https://drive.google.com/file/d/0B-0ZHwApBp-OT0UxTF9SaG1PZjA/edit?usp=sharing" rel="nofollow">View / Download from Google Drive</a></p>
  
  <p>I am using an OLE DB connection string to read the data from the Excel (2007) file:</p>
  
  <pre><code>"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties = \"Excel 12.0 XML; HDR=YES; IMEX=1\";"
  </code></pre>
  
  <p>Using the Interop classes is not an option. They're  not set up on the server, and as a contractor, it's not likely that I'll be able to get IT to set it up.</p>
  
  <p>Then, I'm using a <code>OleDbCommand()</code> that just runs <code>SELECT *</code> against the Excel sheet, and filling a DataSet with the result.</p>
  
  <p>This has me really stumped, and I would greatly appreciate any assistance.</p>