Infrastructure Map Server General Discussion

Infrastructure Map Server General Discussion

Reply
Active Member
DEVR1361
Posts: 6
Registered: ‎01-05-2005
Message 1 of 6 (447 Views)

Updating Excel file after making a data source in MGE 2011

447 Views, 5 Replies
05-11-2011 02:09 PM

I'm a bit embarassed to admit that Excel is the data source, but it is not a matter of choice.  We keep the status of work at lat/long points in an Excel 2007/2010 sheet.  Once the sheet is connected as a data source, it is locked for editing.  Even after a bit of convoluted logic that got the data updated, the changes did not flow up to the layers and maps.

 

Is there a way to update existing data in an Excel file and have the changes reflected in MGE 2011 without recreating everything from scratch or restarting the server?

 

In the Studio help file (search for "upload", select "About ODBC Data Source" I found this line:

"If your data is in Microsoft Access or Excel format, you can upload the source files to the site server from any location in your file system. You can also download the files from the server to your file system for update and replacement."

 

Can anyone explain how this is done?

 

Thanks,

Dan

Valued Contributor
pg002a
Posts: 58
Registered: ‎08-03-2010
Message 2 of 6 (440 Views)

Re: Updating Excel file after making a data source in MGE 2011

05-11-2011 11:47 PM in reply to: DEVR1361

You must use FDO Excel ODBC dataconnection instead uploading  it to MGE server,  then all changes made in excel are reflected  in MGE and you dont need to do more.

Active Member
DEVR1361
Posts: 6
Registered: ‎01-05-2005
Message 3 of 6 (436 Views)

Re: Updating Excel file after making a data source in MGE 2011

05-12-2011 04:36 AM in reply to: pg002a

When I make that connection and then try to open the Excel file I get the message "This file is in use."

 

What I did:

>Selected OSGeo FDO Provider for ODBC

>Connect to an external file

>Select the file

>This connection string is created:

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=%MG_DATA_PATH_ALIAS[MGEData]%WarwickRI_Data/Warwick_RI_ODAS.xlsx;

>Define te Geometry and the data points preview nicely.

 

However, I cannot edit the excel file - Excel responds: "Enter a new name or close the file that's open in another program."

 

If I make a DSN connection or connect to a database (using the above connection string), the file is still locked...

 

Should I be connecting to the Excel file differently? Am I using the wrong FDO?

 

 

Valued Contributor
pg002a
Posts: 58
Registered: ‎08-03-2010
Message 4 of 6 (427 Views)

Re: Updating Excel file after making a data source in MGE 2011

05-12-2011 05:37 AM in reply to: DEVR1361

I found same problem after I configured connection over ODBC, but I did restart to PC and then it was able to edit excel table and if you have Studio opened then it was no possible to edit excel but after closing it was  possible to edit excel again. But I think smarter way to convert Excel data  to mdb, with it you dont have data editing problem.

 

Kajar

 

 

 

Active Member
DEVR1361
Posts: 6
Registered: ‎01-05-2005
Message 5 of 6 (422 Views)

Re: Updating Excel file after making a data source in MGE 2011

05-12-2011 06:19 AM in reply to: pg002a

I will give that a try - closing studio while viewing the web page and editing the Excel file.  I agree that mdb (or any other database) would be better, but that is not an option.  I appreciate you "reading between the lines" understanding what I was doing (wrong) and making an excellent observation and suggestion.  Many thanks.

 

Dan

Active Member
DEVR1361
Posts: 6
Registered: ‎01-05-2005
Message 6 of 6 (418 Views)

Re: Updating Excel file after making a data source in MGE 2011

05-12-2011 10:14 AM in reply to: DEVR1361

If the MGE server is running, the Excel file cannot be accessed. 

My MGE server is a virtual machine (2008 r2) on my 64 bit Win7 Pro machine.  The Excel file is on the server, accessed by the Win7 host machine via a mapped drive letter. I have installed AccessDatabaseEngine_X64.exe on the server, although the Excel 2010 file is 32 bit. 

Any other suggestions?

 

Dan

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.
Need installation help?

Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.