CODEWITHSUNDEEP
c#exceldate
rlm96
rlm96

Reputation: 193

Random date format read from Excel

I'm using OleDb to read data from an Excel file. The piece of code that reads the data is the following:

OleDbCommand oleDbCommand = new OleDbCommand(selectCommandText, oleDbConnection);

using (OleDbDataReader dr = oleDbCommand.ExecuteReader())
{
    DataTable dt = new DataTable();
    dt.Load(dr);
    return dt;
}

The problem is that the data readed appears randomly as a string sometimes (for example "16.02.1995") or like a number - timestamp (41187), something like this convert Excel Date Serial Number to Regular Date.

Is there any way to solve this? I would like to read the data always as a format, don't matter if it's a number or a string.

Edit: I found that when I have the Excel file opened the date readed is in number format (date serial number) and when I don't have the file opened the date is in string format. Does somebody know why?

Edit2: The personalized format used in the date cell

Personalized format

Upvotes: 5

Views: 509

Answers (1)

Maytham Fahmi
Maytham Fahmi

Reputation: 33437

To convert date number or date string to c# you need two different methods.

One to convert string and the other one to convert number to date format.

So, regarding converting string to date, there are TryParse method in c#, and regarding the number conversation to date there are already answer on that in SO.

Putting that together we can do some thing like:

public static DateTime? GetDateTime(object o)
{
    DateTime? date;
    try
    {
        date = FromStringToDate(o.ToString());
        if (date == DateTime.MinValue)
        {
            date = FromExcelSerialDate((int)o);
        }
    }
    catch (Exception e)
    {
        //log your exception
        date = null;
    }

    return date;
}

private static DateTime FromExcelSerialDate(int serialDate)
{
    if (serialDate > 59) serialDate -= 1; //Excel/Lotus 2/29/1900 bug   
    return new DateTime(1899, 12, 31).AddDays(serialDate);
}

private static DateTime FromStringToDate(string stringDate)
{
    DateTime.TryParse(stringDate, out DateTime result);
    return result;
}

To put that in use, in your main method for testing you can do some thing like:

List<object> excelData = new List<object>()
{
    "16.02.1995",
    41187,
    13131.3242,
    "",
    null
};

foreach (object o in excelData)
{
    var dateTime = GetDateTime(o);
    if (dateTime != null)
    {
        Console.WriteLine(dateTime);
    }
}

The output will be:

16-02-1995 00:00:00    
05-10-2012 00:00:00

I have testing it i excel as well.

enter image description here

Note: This is just example, you might improve the methods, change the order, adding more protective lines so it does not break, for example if date is null, empty or wrong format in excel to fit you business logic.

Upvotes: 5

Related Questions