Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to read a date column in excel using C#

Posted on 2010-01-05
14
Medium Priority
?
8,461 Views
Last Modified: 2012-05-08
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.
0
Comment
Question by:RakeshBhandari
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 14

Expert Comment

by:ajitha75
ID: 26180124
Are  you using DateTime.Parse("1-Jan-2010"). It should return 1/1/2010.

Thanks
Ajitha
0
 
LVL 5

Expert Comment

by:ghoshsaikat83
ID: 26180140
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
 

Expert Comment

by:sbsnewbie
ID: 26183089
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Expert Comment

by:sbsnewbie
ID: 26183131
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
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 26187527
@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
 
LVL 14

Expert Comment

by:ajitha75
ID: 26188055
You can paste your code here and the result you are getting?

Thanks
Ajitha
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 26189097
@ajitha75
I am getting an format exception....
String was not recognized as a valid DateTime.
0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26189111
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
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 26197210
After using DateTime.Parse(strInput)  I  geting strInput  like  "41727" and  exception "String was not recognized as a valid DateTime."
0
 
LVL 10

Expert Comment

by:Fayaz
ID: 26280621
0
 
LVL 9

Expert Comment

by:AnilKumarSharma
ID: 26284643
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
 
LVL 14

Expert Comment

by:ajitha75
ID: 26285249
Can you attach the excel sheet that you are using.

-Ajitha
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 26311051
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
 
LVL 9

Accepted Solution

by:
AnilKumarSharma earned 2000 total points
ID: 26311209
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question