reading data from an excel spreadsheet - is it supposed to be easy?

[Andy Fish]

ok, so after a few dead ends I have got this far:

1. using visual studio 2005, create a new blank windows forms project and
add a data source

2. create a new connection, select data source as "other" then select ".net
framework data provider for oledb"

3. select "microsoft jet 4.0 ole db provider"

4. enter file name, then under advanced, enter Excel 8.0;HDR=YES

5. click to test connection - works ok

6. ignore suggestion about coping the data file to the project (if I try to
do it, it just gives "unexpected error" afer 30 seconds of disk grinding)

7. back on the data source configuration wizard, I get as far as "choose
your database objects" and the screen just shows the message:

an error occurred while retrieving the information from the database
operation is not supported for this type of object

the xls file itself was created with excel 2003 and contains the data:

id, name, address
1, fred, reading
2, john, london

anyone got any clues? I thought i'd start off doing the simplest thing
possible then work up to something useful but I can't get seem to get off of
the start line at the moment.

TIA

Andy

 

[W.G. Ryan]

Andy:

I can't really speak to doing this with the designer b/c I'm not a big
designer guy. There are many articles out there on the subject, but here's
one I wrote a while ago that shows how to do it using pure code:

http://www.knowdotnet.com/articles/exceldatasource.html

If you use this approach, can open the connection and can fill the
datatable, then you should be able to accomplish what you need. If you have
to use the designer, then I'm not entirely sure what the answer is but I
will look some more. If you are so inclined though, would you be willing to
just take a look at this approach and give it a try? It's pretty simple. If
you do try it and have any problems, please let me know and I'll be more
than happy to give it a try and get it working for you - I may need a copy
of your sheet to actually do that, in which case I'd ask that you dummy up
the data so that we don't breach anyon'es privacy directly or otherwise.

Cheers,
bill

 

[Andy Fish]

Thanks bill,

I have abandoned the visual designer thing and done it in code and it all
works fine

still interested if anyone can tell me why I couldn't get it to work with
the visual tools though

Andy

 

[W.G. Ryan]

Glad to hear you got it working Andy.

I've been looking to try to get you a specific answer about the designer and
haven't found one. I know that in many cases, if you want the tools like
the Adapter Configuration Wizard or CommandBuilders to work, you need a
clearly identifiable key for them to work their magic. Similarly, in
Enterprise Manager and other visual tools, if you don't have a key, you
can't do inline updating. This is pure conjecture on my part, but my
suspicion is that it has something to do with this. Then again, you can
certainly view tables that don't have keys so that would seem to contradict
my theory. I don't know but it's a darn good question and i'll keep looking
around to hopefully find something.