How to read a date column in excel using C#

I have a column in excel storing information as date like "1-Jan-2010". I want to read this date and store it in database.
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.
An alternative is to treat it as string by appending " ' " (i.e. to treat it as string and then do string manipulations) to all rows before i actually read it, but i dont want to do this.

Please suggest.
LVL 2
RakeshBhandariAsked:
Who is Participating?
 
AnilKumarSharmaCommented:
Your first code is fail as excel method to store data for date field is different that what it displayed to user.
Your second method is bound to fail due to two three reason, first maybe if the range you selected doesnt contain all fields with the date format.
Solution->open the excel file in excel, highlight the column of cells that should be of a "date" type, then goto format-->cells, and set it in excel to treat the cells as only a date type. save the file.

May be date format are there for available dates and not for empty or other cell that lie in the range your specified in the code. second you are converting the stored value (as excel stored it internally) directly into string. This value appear correctly if used in excel as excel can manupulate this value but after covnerting to string it is no longer a date type of excel. Finally if some field is emtpy then?
DateTime excelDate = (DateTime)my_range.get_Value(Type.Missing);
DateTime excelDateTime = Convert.ToDateTime(dataset.Tables[0].Rows[0]["DateTimeExcelFieldName"].ToString();

Verify the column is set as date type of some cells before we can conclude something else.
 
some code that may help you,
Range myRange = workSheet.get_Range("Y" + rowindex, "Y" + rowindex).;
object excelDate = myRange.get_value(Type.Missing);
DateTime goodDate = ConvertExcelDateToDate(excelDate);
 DateTime ConvertExcelDateToDate(object excelDate)
        {
            DateTime date = (DateTime)excelDate;
                   return date;
        }
 Finally check below link that may help you a bit,
http://msdn.microsoft.com/en-us/library/1ad4d8d6.aspx
0
 
ajitha75Commented:
Are  you using DateTime.Parse("1-Jan-2010"). It should return 1/1/2010.

Thanks
Ajitha
0
 
ghoshsaikat83Commented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sbsnewbieCommented:
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);

Open in new window

0
 
sbsnewbieCommented:
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.
0
 
RakeshBhandariAuthor Commented:
@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..
0
 
ajitha75Commented:
You can paste your code here and the result you are getting?

Thanks
Ajitha
0
 
RakeshBhandariAuthor Commented:
@ajitha75
I am getting an format exception....
String was not recognized as a valid DateTime.
0
 
ajitha75Commented:
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
0
 
RakeshBhandariAuthor Commented:
After using DateTime.Parse(strInput)  I  geting strInput  like  "41727" and  exception "String was not recognized as a valid DateTime."
0
 
FayazCommented:
0
 
AnilKumarSharmaCommented:
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.

0
 
ajitha75Commented:
Can you attach the excel sheet that you are using.

-Ajitha
0
 
RakeshBhandariAuthor Commented:
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());                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.