<div>> Dave you are correct. Naming the range allowed the table to be seen and I can move forward now.</div>
<div>>Mark, yes, after naming the range the system DSN connection worked fine. I had to reboot the 2k3 server after I made the named range change.</div>
<div>>Zac - I did not try your suggestion "via access with a linked linked excel table" as I was not sure what you meant, but think it accomplishes the same thin as naming the range as Dave suggested.</div>
<div> </div>
<div>Thanks to all who responed so promptly. Unfortunately I was not able to try these suggestions until just now as my job (and a broken home AC unit) keeps getting in the way of what I want to be doing... </div>
<div> </div>
<div>Thanks again,</div>
<div>Dan</div>
<div><br><br> </div>
<div class="gmail_quote">On Wed, Jul 7, 2010 at 1:28 PM, Dave Wilson <span dir="ltr"><<a href="mailto:dave.wilson@autodesk.com">dave.wilson@autodesk.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">One step that I think is missing is that in the Excel sheet you plan to use as a table you should Name the area (full set of cells containing your data). The Name acts like a table name to the ODBC provider.<br>
<br>Regards,<br><font color="#888888">Dave<br></font>
<div>
<div></div>
<div class="h5"><br>-----Original Message-----<br>From: <a href="mailto:mapguide-users-bounces@lists.osgeo.org">mapguide-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:mapguide-users-bounces@lists.osgeo.org">mapguide-users-bounces@lists.osgeo.org</a>] On Behalf Of VOLZ Mark (FN)<br>
Sent: Sunday, July 04, 2010 11:52 PM<br>To: MapGuide Users Mail List<br>Subject: RE: [mapguide-users] Connecting to an Excel Database<br><br>Dan,<br><br>You first have to make an odbc connection to the excel file and then<br>
connect the ODBC FDO to that DSN (I have found through a lot of swearing<br>and going through the endless loops as it seems you are).<br><br>Steps to do this;<br><br>In the windows envoriment start > admin tools > data sources (ODBC) or<br>
control panel > admin tools > data sources<br>Select the system DSN tab<br>Click add<br>Select microsoft excel driver (there are a couple of xls connectors but<br>I have found this one works well)<br>Give it a source name (this is the source name you will select in FDO)<br>
A description (if you feel like it ... Not a bad idea for future<br>proofing)<br>Select the work book<br>Fill in any special options (row scan / read only /etc)<br>Click ok<br>And OK again<br>Stop/start the mapguide server service (or wait for about 10mins for the<br>
server to update itself)<br>Launch MGE 2010 studio (I'm assuming you are MGE user and not an open<br>source user)<br>And then add the ODBC FDO data source<br>Select "connect via a dsn on this machine"<br>Enter the source name<br>
And test the connection<br>You should receive a green tick (if you receive a rdbms error then the<br>file is proably open somewhere)<br>Click the show tables button<br>If all is working you should then be able to select a table (sheet) and<br>
the x and y fields assoc. with it<br>You can then create a layer using this data source, style and theme as<br>required.<br>Add the layer to a map<br>Add the map to a web layout<br>Launch the web layout<br>You should now have a web map showing your excel data.<br>
<br>I have found that creating a system DSN is preferrable then a user DSN<br>or using the odbc engine within the FDO connection (just seems to be a<br>bit more stable).<br><br>Hope this helps<br><br>Mark<br><br><br>-----Original Message-----<br>
From: <a href="mailto:mapguide-users-bounces@lists.osgeo.org">mapguide-users-bounces@lists.osgeo.org</a><br>[mailto:<a href="mailto:mapguide-users-bounces@lists.osgeo.org">mapguide-users-bounces@lists.osgeo.org</a>] On Behalf Of DGK<br>
Sent: Saturday, 3 July 2010 3:36 AM<br>To: <a href="mailto:mapguide-users@lists.osgeo.org">mapguide-users@lists.osgeo.org</a><br>Subject: [mapguide-users] Connecting to an Excel Database<br><br><br>The subject itself should describe my experience level... (extreme<br>
newbie)<br><br>The goal is simply to attach a point file and place symbols on a map. I<br>have been through the "Autodesk Official Traning Guide" for Mapguide<br>2010 and searched "Excel" under the users group to no avail. But for<br>
this simple task still I'm lost...<br><br>The point file is an Excel spreadsheet with these columns:<br>ID SITE LAT LON<br>1 NY-ROC001 43.1851967 -77.641065<br>
2...149<br><br>Tried a couple of Methods to Connect see below (no user credentials<br>required in any case).<br>The problem is the Excel file? - Used Excel 2003, except for DSN<br>connection<br>- then saved as Excel 5.0<br>
<br>Please advise - I've become the definition of insanity - doing the same<br>thing over and over and expecting a different result...<br>Dan<br><br><br>What follows are the specific steps taken for everything I can think<br>
of...<br><br>Attempt 1 Failure:<br>Type of Connection - OSGeo FDO Provide for ODBC "Connect to a file and<br>upload to the MapGuide Library"<br>New - Select file SyrRocLocations01.xls (on local machine, not a network<br>
drive)<br>Type of database - Microsoft Excel Driver (*.xls) Connection String -<br>Driver={Microsoft Excel Driver<br>(*.xls)};DriverId=790;Dbq=%MG_DATA_FILE_PATH%SyrRocLocations01.xls;Defau<br>ltDir=%MG_DATA_FILE_PATH%;<br>
Test Connection - Successful connection<br>Under Define geometry properties... Press "Show Tables" - nothing<br>appears Press Refresh - Failed to retrieve message for<br>"MgIndexOutOfRangeException".<br>
The value is invalid because it is too large.<br><br>Attempt 2 Failure:<br>Type of Connection - OSGeo FDO Provider for ODBC "Connect to an External<br>File"<br>File to connect to -<br>%MG_DATA_PATH_ALIAS[DATA_FILES]%/SyrRocLocations01.xls<br>
(alias is set up properly)<br>Type of Database - Microsoft Excel Driver (*.xls) Connection String -<br>Driver={Microsoft Excel Driver<br>(*.xls)};DriverId=790;Dbq=%MG_DATA_PATH_ALIAS[DATA_FILES]%/SyrRocLocatio<br>ns01.xls;DefaultDir=%MG_DATA_PATH_ALIAS[DATA_FILES]%;<br>
Test Connection - An exception occurred in FDO component. RDBMS: No<br>current open database.<br><br>Attempt 3 Failure:<br>Type of Connection -OSGeo FDO Provider for ODBC "Connect via a<br>DataDource Name (DSN) set up on the server"<br>
Create DSN on server, select connection name (made sure to save file as<br>Excel 5.0 and use like dsn driver) Test Connection - Successful<br>Connection Refresh - Failed to retrieve message for<br>"MgIndexOutOfRangeException". The value is invalid because it is too<br>
large.<br><br>Tried to Create a layer from each of the data sources:<br>Create Layer fron Excel Upload (1 above) <OR> Create Layer From External<br>file (2 above) <OR> Create Layer From DSN connection (3 above) <=><br>
<br>In all cases:<br>"The Feature Class is Invalid..."<br>--<br>View this message in context:<br><a href="http://osgeo-org.1803224.n2.nabble.com/Connecting-to-an-Excel-Database-tp5248617p5248617.html" target="_blank">http://osgeo-org.1803224.n2.nabble.com/Connecting-to-an-Excel-Database-t<br>
p5248617p5248617.html</a><br>Sent from the MapGuide Users mailing list archive at Nabble.com.<br>_______________________________________________<br>mapguide-users mailing list<br><a href="mailto:mapguide-users@lists.osgeo.org">mapguide-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/mapguide-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapguide-users</a><br><br><br><br><br><br><br><br><br><br>---------------------------------------------------------------------<br>
To report this email as spam, please forward to <a href="mailto:spam@mailcontrol.com">spam@mailcontrol.com</a><br>---------------------------------------------------------------------<br><br><br>*************************************************************************<br>
This e-mail (including any attachments) may contain confidential or<br>privileged information and is intended for the sole use of the person(s) to<br>whom it is addressed. If you are not the intended recipient, or the person<br>
responsible for delivering this message to the intended recipient, please<br>notify the sender of the message or send an e-mail to<br>mailto:<a href="mailto:help.desk@ergon.com.au">help.desk@ergon.com.au</a> immediately, and delete all copies. Any<br>
unauthorised review, use, alteration, disclosure or distribution of this<br>e-mail by an unintended recipient is prohibited. Ergon Energy accepts no<br>responsibility for the content of any e-mail sent by an employee which is of<br>
a personal nature.<br><br>Ergon Energy Corporation Limited ABN 50 087 646 062<br>*************************************************************************<br>_______________________________________________<br>mapguide-users mailing list<br>
<a href="mailto:mapguide-users@lists.osgeo.org">mapguide-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/mapguide-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapguide-users</a><br>
_______________________________________________<br>mapguide-users mailing list<br><a href="mailto:mapguide-users@lists.osgeo.org">mapguide-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/mapguide-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapguide-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>My goals are to become incredibly wealthy and remain completely anonymous. I'm halfway there...<br>