Problems reading Excel dates

[jebenson]

Hello all,

Here's the setup: I have a spreadsheet that I am supposed to read several sheets, filter on a date field and send out email notices to the appropriate persons based on the filtered data. I have implemented the solution and it worked fine...until it didn't.

The problem is the date in Excel. Here's basically how I read the data:

1) I use OleDb to get all of the data from a spreadsheet via SQL.

2) Build an in-memory datatable to hold the appropriate data.

3) Loop through the data from step 1 and add it to the datatable from step 2. Unfortunately I have to do this because there is a header on each spreadsheet, so I can't just use the data as-is. I have to start reading the data at line 6 or so.

The problem occurs when I try to add the date from Excel to the date field in the in-memory datatable. It was working fine, but now when the attempt to add the date is made it fails with an "invalid date" error. I checked with a breakpoint and a watch, and the date from Excel is no longer being pulled in as a date, but the underlying numeric code that Excel uses to represent dates internally. For example, the date "5/31/2008" is represented as 39599 "behind the scenes", and it is this number that is being pulled by the SQL query, not the date. So when I try to add this to the datatable, it fails.

I have tried changing the format on the date in Excel, but if I change it to Text for example, it then just displays the 39599.

Any ideas? I can of course post code if necessary.




I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[PRPhx]

So, what happens when,a fter you've imported all your date, you format the cell as a DATE, in whatever date format you need? Excel SHOULD translate the text to date. It does so, at least if you type in 39599 in a text cell, then reformat it as a date. It displays as a proper date.

Hope that's clear???? LOL.

You might also try opening your excel spreadsheet in your app, and filter out the data that way. You can google for help.

Another way, you could import the data from your spreadsheet into SQL, then query for whatever you need, and populate a spreadsheet. Format the date field as a DATE. It should work. You may need to set up a data source within Excel to connect to SQL.

i know, a lot of work, but it DOES work.

 

[jebenson]

That's the problem...the field in Excel IS formatted as a Date. This used to work, but it just started failing and I can't determine why. I can't just filter out the data I need because of the header issue I mentioned in my original post. I can't open Excel in the app because this app resides and runs on a server that does not have Excel installed on it.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[gmmastros]

Do a little research on IMEX. [google]Excel Imex=1[/google]. Are you specifying the IMEX in the code you use to read the excel file?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jebenson]

gmmastros,

Yes, I am using IMEX=1.

Here's my connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\Path\File.xls"";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[jebenson]

OK, I feel really dumb right now.

It turns out the problem is...I had the Excel file open while trying to read it from my VB app. That's right, when I tried the program with the Excel file closed, it reads the dates correctly. When the file is open it reads the dates as the numeric code. Weird.

Anyway, thanks for your help folks. Sorry to "cry wolf", as it were.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!