[mapguide-users] Connecting to an Excel Database

Thu Jul 8 12:29:21 EDT 2010

> Dave you are correct.  Naming the range allowed the table to be seen and I
can move forward now.
>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.
>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.

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...

Thanks again,
Dan

On Wed, Jul 7, 2010 at 1:28 PM, Dave Wilson <dave.wilson at autodesk.com>wrote:

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

-- 
My goals are to become incredibly wealthy and remain completely anonymous.
 I'm halfway there...
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapguide-users/attachments/20100708/1f907e86/attachment.html