Link to home
Start Free TrialLog in
Avatar of DrkLight
DrkLight

asked on

cell in Excel with Date format is read by ADO.NET as NULL

My problem is the title itself.

I have tried to read an excel file using ADO.NET (as opposed to automation, which forces me to create an instance of an excel process).

Using a data adapter, I call the Fill() method and pass a datatable (most examples on the internet feature passing a dataset).

In the datatable, I am able to read the cells whose values has a string format.
But somehow, the cells with date format are null when I try to read it.

Did I miss some setting?
Should I set something in the datatable variable before using it?
Or do I really have to use a dataset instead? This should not matter, right?
Avatar of RonaldBiemans
RonaldBiemans

Hi DrkLight,

Strange, I don't seem to have this problem. Could you show your code (including your connection string).

are you sure the date in your excel in in the format of your regional settings
Avatar of DrkLight

ASKER

Thanks for the reply.

I've also thought of this.

I checked the style of the date cell formatting and nothing seems to be strange.
In fact, I even downloaded from the internet a different program that does almost the same thing (reads excel files).  That program is able to read the date cell on another file that it creates.
But when I use it to read my excel file, it also fails.
I have tried changing the date cell to various formats availabale for a date, but it still returns null.

I have tried printing out the contents of the read excel file (whether by datatable or by datareader).
Either way, the date cells are returned as null. =(

There was one cell though where the date was shown.
This cell shows the date in the style with the asterisk (I am using WinXP J-OS so its kind of hard to explain)
Thanks RonaldBiemans, but I found the solution to my problem.

As advised by this link,
http://support.microsoft.com/?id=194124
I added the "IMEX=1" extended property and was able to retrieve my data already...

My excel data is kind of awry (not the standard table fromat) so this works fine for me.
Great, then get a refund for your points at comminity support
ASKER CERTIFIED SOLUTION
Avatar of modulo
modulo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial