|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
reading data from an excel spreadsheet - is it supposed to be easy?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 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 Show quote "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message news:eyiJltpKIHA.1212@TK2MSFTNGP05.phx.gbl... > 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 > > 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 Show quote "W.G. Ryan" <WilliamRyan@nospam.gmail.com> wrote in message news:eKIxGZrKIHA.2064@TK2MSFTNGP06.phx.gbl... > 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" <ajf***@blueyonder.co.uk> wrote in message > news:eyiJltpKIHA.1212@TK2MSFTNGP05.phx.gbl... >> 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 >> >> > > 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. Show quote "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message news:uBO6yprKIHA.1184@TK2MSFTNGP04.phx.gbl... > 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" <WilliamRyan@nospam.gmail.com> wrote in message > news:eKIxGZrKIHA.2064@TK2MSFTNGP06.phx.gbl... >> 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" <ajf***@blueyonder.co.uk> wrote in message >> news:eyiJltpKIHA.1212@TK2MSFTNGP05.phx.gbl... >>> 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 >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics