Importing Date columns of an excel file

  • Thread starter Thread starter Anton Sommer
  • Start date Start date
A

Anton Sommer

Hello folks,


I am importing excel file Data into .NET, and it works fine for one
exception.

My connection string:
..Connection = "provider=microsoft.jet.oledb.4.0;"
..ConnectionString += "data source=" & strTextPath & ";"
..ConnectionString += "Extended Properties = ""Excel 8.0;IMEX=1;HDR=no"";"

My excel file (its structure is 100% known) contains as well a few columns
with date values. Some of those columns import as I expect a date value to
import i.e. "14.7.1988" (as a string) but others import as a number like
"38072" or "38072,600694" what is that number? Can I convert to a date as
well? I can't tell what is the difference between those excel files that
work and those that don't work.

Any suggestions


Anton
 
Anton Sommer said:
My excel file (its structure is 100% known) contains as well a few columns
with date values. Some of those columns import as I expect a date value to
import i.e. "14.7.1988" (as a string) but others import as a number like
"38072" or "38072,600694" what is that number?
Click to expand...

I believe that's the number of days since 1 January 1900. The fractional
part of the number is the time-of-day (0.6007 is approximately 14:25).

38072 equals 28 March 2004, is that correct?
Can I convert to a date as well?
Click to expand...

Sure, try using,

double days = 38072.600694;
DateTime convertedDt = new DateTime( 1900, 1, 1).AddDays( days);
I can't tell what is the difference between those excel files that
work and those that don't work.
Click to expand...

Open the spreadsheet in Excel, and select each of the date-formatted
cells individually and press Control - 1 (that's the digit, '1') to check each
of their formats. They're probably being formatted differently. If these
cells have identical formatting, I think they should generate consistent
output for you.


Derek Harmon
 
¤ Hello folks,
¤
¤
¤ I am importing excel file Data into .NET, and it works fine for one
¤ exception.
¤
¤ My connection string:
¤ .Connection = "provider=microsoft.jet.oledb.4.0;"
¤ .ConnectionString += "data source=" & strTextPath & ";"
¤ .ConnectionString += "Extended Properties = ""Excel 8.0;IMEX=1;HDR=no"";"
¤
¤ My excel file (its structure is 100% known) contains as well a few columns
¤ with date values. Some of those columns import as I expect a date value to
¤ import i.e. "14.7.1988" (as a string) but others import as a number like
¤ "38072" or "38072,600694" what is that number? Can I convert to a date as
¤ well? I can't tell what is the difference between those excel files that
¤ work and those that don't work.
¤

Sounds to me like you have some values in those columns which are not dates. Can you confirm this by
looking at the Worksheet in Excel and identifying those rows?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top