How to read a date column in excel using C#

Are  you using DateTime.Parse("1-Jan-2010"). It should return 1/1/2010.

Thanks
Ajitha

If the data type of the column is datetime that you can use the following formula while reading the excel file...

=TEXT(A1,"yyyy-mm-dd hh:mm:ss.ms")

This data will be treated in your database as a datetime format.

Joydip

I would pull the data from Excel using OLEDB. Instead of changing your data inside Excel, try adding the IMEX=1 to your connection string. This will treat EVERYTHING as text/string. Sure you have to parse your numbers again later, but it prevents any weird auto-formatting issues with Excel.

The attached code will read an entire sheet from Excel 2003 and put the data as strings into rawData. Then you can quickly parse it inside of your application.

This is much faster, performance wise and development time, than using VSTO by trying to open an Excel instance and use Workbook, Worksheet, etc. objects.

string con_string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

OleDbConnection con = new OleDbConnection(con_string);
OleDbDataAdapter data = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);

DataTable rawData = new DataTable();
data.Fill(rawData);

Select allOpen in new window

Small note if you aren't familiar with connecting to Excel with oledb:

HDR=YES treats the first row as column headers
HDR=NO treats the first row as data

IMEX=1 treats all data as text/varchar/string

If you are using Excel 2007, there is a different connection string. The provider and extended properties are different.

@ajitha75 and @ghoshsaikat83, i have tried that option, but its not working
@sbsnewbie getting this error:
Selected collating sequence not supported by the operating system.

Further help needed..

You can paste your code here and the result you are getting?

Thanks
Ajitha

@ajitha75
I am getting an format exception....
String was not recognized as a valid DateTime.

Also, I need what the input data is..

Say, you are using DateTime.Parse(strInput)... what is the value of strInput....

Also, do a Trim on the string that you are inputting to the Parse function..

Thanks
Ajitha

After using DateTime.Parse(strInput)  I  geting strInput  like  "41727" and  exception "String was not recognized as a valid DateTime."

I have no much idea, but based on your qn;
http://support.microsoft.com/kb/316934
http://support.microsoft.com/kb/257819 

As per your comment
"I am done with reading the excel, facing problem specifically for date field. While reading the date it comes returns a number, which is not at all linked to the date."

Can you please show up the code where the problem is? How you are reading it?

I am assuming that the column in the excel is formatted for date field.

Can you attach the excel sheet that you are using.

-Ajitha

Below i giving Date format  available in excelsheet and my coding
29-Mar-14     - DateFormat Available excel Sheet.

C# coding

string strValidityDate = "";
 strValidityDate = workSheet.get_Range("Y" + rowindex, "Y" + rowindex).Value2.ToString();

And i tried

DateTime ValidityDate       
 ValidityDate =  DateTime.Parse(workSheet.get_Range("Y" + rowindex, "Y" + rowindex).Value2.ToString());