Click here to Skip to main content
14,933,981 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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);
            //fill the DataTable

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
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

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.
Obaid Ahmed 14-Mar-11 3:26am
yup! you are rite :)

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900