fetch datetime from a Excel sheet through OleDbDataAdapter

Question

4.00/5 (1 vote)

See more:

i am facing a problem when i fetch datetime column from Excel containing CSV format values then its show null because the value on datetime column is like 50:48:12 (HH:MM:SS), but when the value is under datetime range like 23:12:32 then its working perfect...

i want to fetch the Excel column with any datetime value e.g if the value is 50:48:12 then i got the same value on Query result

my snap code is below my OleDbDataAdapter connection is

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + "\\\";" + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

My query is

string query = "SELECT * FROM " + file;

OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
        try
        {
            //fill the DataTable
            dAdapter.Fill(dTable);
        }

Values In Excel Sheet is 104:24:54 22:12:34 720:01:00
and if i open dataTable then
1. value in dTable.Tables[0].Rows[0] is null
2. value in dTable.Tables[0].Rows[1] is 10:12:34 PM
3. value in dTable.Tables[0].Rows[2] is null
please give me any solution

Posted 9-Mar-11 21:48pm

Obaid Ahmed

Add a Solution

Comments

Ryan Zahra 10-Mar-11 3:54am

How do you expect the DateTime variable to store those times? The hour variable cannot be greater than 24 hours because that would make a whole day. Thus those time values are incorrect. Anyway, what are you trying to do with those "times"? You should convert your "hours" into days in order to have that value. Or if you still want to have those "time" values, consider storing them in a string.

Obaid Ahmed 10-Mar-11 4:33am

yes you are rite but actually the value of date is set by another software, and i have to export that table through Excel sheet. i knew that this format is not write, but i have to fetch the date

Ryan Zahra 10-Mar-11 4:41am

You should get those dates as strings and then convert it to a date. You should then split the date to get the hours, divide that by 24 and you'll have the days, hours minutes and seconds.

Obaid Ahmed 10-Mar-11 4:45am

i can't :(
because that column's type is set datetime from another software

Ryan Zahra 10-Mar-11 4:48am

Can you change the column type to string at least until you fetch the dates from the database and then change it back to DateTime?

Obaid Ahmed 10-Mar-11 11:40am

yes there is no way if that column wouldn't change

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)

Robert Rohde · Accepted Answer · 2011-03-12T09:48:00

Solution 1

Try adding IMEX=1 to your connection string:

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + "\\\";" + "Extended Properties=\"text;HDR=No;IMEX=1;FMT=Delimited\"";

This should tell Excel to treat all columns as strings. You can than parse everything by yourself.

Posted 12-Mar-11 9:48am

Robert Rohde

Comments

Obaid Ahmed 14-Mar-11 3:26am

yup! you are rite :)
thanks