[mapguide-users] Connecting to an Excel Database

Wed Jul 7 13:28:12 EDT 2010

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