Is there a way to force Microsoft.Jet.OLEDB to get date Columns in MM/DD/YYYY format from Excel?

Question

I am having a problem with reading DateColumns from an excel sheet.

Sometimes people use different date Formats and this brings a problem. Let's say when I expect 07/26/2010 from an Excel column I get 26-Jul-2010 because the user has changed its date format.

I use Microsoft.Jet.OLEDB for reading the xls sheet into a DataTable.

Can I somehow force OleDb reader whatever the DateFormat is set on XLS, to convert all the dates into MM/DD/YYYY format?

I use this piece of code to read the Excel file:

string strConn;
strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + uploadedFileInfo.FullName + ";" +
          @"Extended Properties=""Excel 8.0;HDR=NO;""";


using (OleDbConnection connToExcel = new OleDbConnection(strConn))
{
    //You must use the $ after the object you reference in the spreadsheet
    connToExcel.Open();

    string firstSheetName = ExcelUploadedFileReaderBuilder
                            .GetFirstExcelSheetName(connToExcel);

    OleDbDataAdapter myCommand 
    = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", firstSheetName), connToExcel);

    DataSet myDataSet = new DataSet();

    myCommand.Fill(myDataSet, "uploadedExcelTable");

    DataTable dtUploadedExcel = myDataSet.Tables["uploadedExcelTable"];

    lineCount = GetLineNumberWhereNULLRowOccured(dtUploadedExcel) + 1;

    connToExcel.Close();
}

Answer 1

You don't have to loop through the dataset as suggested. You can have your query do all the formatting for you by specifying in your query the format you want.
An example is as follows:

OleDbDataAdapter myCommand = 
  new OleDbDataAdapter("SELECT FORMAT([DateCol], 'MM/dd/yyyy') as [DateCol] FROM [SheetName]", connToExcel);

This is guaranteed to work as long as you're using JET OLEDB or the Ace OLEDB data provider. I cannot guarantee it'll work with other data providers but you can always check.