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();
}

Thisbee

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.

Azores

thanks Alex. Do you know any resources that covers the query language which is used by JET OLEDB? – Thisbee

AFAIK, JET OLEDB runs on SQL and for that purpose, I use a book on SQL for reference. (That's where I got the query from.) The book is SQL Fundamentals 3ed. by John J. Patrick. It's published by Prentice Hall. – Azores

Since JET is the engine behind Access, the queries for Excel should use the same syntax as Access. So Access reference docs could help. – Tamarind

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.

Amazon