excel datareader issue

Question

1.00/5 (1 vote)

See more:

I am working with excel datareader, the excel format is .xlsx, How can I convert OA time to .net datetime while importing data into dataset?Any help would be appreciated.

C#

FileStream stream = File.Open(strTempPath, FileMode.Open, FileAccess.Read);

if (Path.GetExtension(strTempPath).Equals(".xls"))
    // Reading from a binary Excel file ('97-2003 format; *.xls)
    excelReader = ExcelReaderFactory.CreateBinaryReader(stream); 
else
    //Reading from a OpenXml Excel file (2007 format; *.xlsx)
    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); 

excelReader.IsFirstRowAsColumnNames = true;

DataTable result = new DataTable();

result = excelReader.AsDataSet(true).Tables[0];

Posted 20-Sep-12 1:49am

PBGuy

Updated 20-Sep-12 2:48am

Tim Corey

v3

Add a Solution

Comments

[no name] 20-Sep-12 7:53am

Reading the documentation for the DateTime data type might be a good place to start.

[no name] 20-Sep-12 7:55am

Thank you,,,,BUt,Its a dynamic data, I can't just find which column is of type date time......

Richard MacCutchan 20-Sep-12 8:19am

If you do not know which column it is, how can anyone else guess?

[no name] 20-Sep-12 8:29am

the excel sheet has data which is uploaded by client.. how could i guess too.

Vani Kulkarni 20-Sep-12 9:45am

Why dont you inform your client, to send data in a specified format? Provide your client with template of the excel sheet which states the data format expected to process the data.

[no name] 20-Sep-12 9:53am

There's no specific type of data, a column can have any type of data,either string, int,date time. the libraries i use to convert xls/xlsx data to datatable are unable to format data in date type column of excel sheet.Please share dll names to solve this issue.

Rajesh Kuramdasu 20-Sep-12 13:35pm

Actually whats going wrong with the data?

[no name] 21-Sep-12 0:28am

What I came to know is,every column is taken as string, I don't have problem with integer type, but If the data was of type, date time, the ole automation time is displayed instead of actual string, I have been using open office..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2012-09-20T07:50:00

Solution 1

Use try catch block around :

C#

DateTime.FromOADate(double d)

C#

try
{  
 double d = double.Parse(b);

 // Get the converted date from the OLE automation date.
 DateTime conv = DateTime.FromOADate(d);
}
catch{}

http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate(v=vs.90).aspx[^]

Posted 20-Sep-12 7:50am

Kuthuparakkal

Comments

[no name] 21-Sep-12 0:26am

It's time eating process to trace date time column and go on converting into .net date time format. What to do with some lakhs of records...?

Kuthuparakkal 21-Sep-12 5:16am

Change your data acquisition strategy. Create some quality standards for input files, drive standardization... What else!?