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.

Answer 2

Loop through the dataset. For each date, covert it to a date (in case the cell has been modified to be text), and then format the date as a string of form "MM/DD/YYYY".

The following code is an example to get you started:

string date1 = "07/26/2010";
string date2 = "26-Jul-2010";

DateTime dt1 = Convert.ToDateTime(date1);
DateTime dt2 = Convert.ToDateTime(date2);

string date1B = dt1.ToString("MM/DD/YYYY");
string date2B = dt2.ToString("MM/DD/YYYY");

date1B and date2B will be equal and in the format that you desire.

There is a bit of back and forth conversion going on and that is because you'll need to validate user input since it is coming from Excel and it is very easy for a user to enter data that won't conform with you database model.

It will be a good idea to put everything in a Try-Catch block in case some of the entered dates are not valid dates themselves.