dynamic link between excel table and geodatabase

[RafiqBasaria]

Hello every one. Ive been searching around all week to see if something like this is possible with not much luck.

Im trying to determine if there is a way to create a dynamic link between an excel table and a feature class within a geodatabase. Basically, I have a point file that gets updates on a daily basis. Id like to avoid having to import the Lat/Longs into ArcMap and then exporting as a shapefile to import into a GDB. Ideally, Id like there to be a dynamic link between the excel file and the GDB so that the feature class updates as I update the excel table.

I guess the other option would be to write a script that would run the process of importing into ArcMap, displaying XY data, exporting as a shapefile, then importing into a GDB, but Im not skilled enough with scripting to get that done.

Thank you all for any help.

[Anonymous User]

I have a few ideas.  My first is you could look into setting up Relationship classes between your point file and a stand alone table in your geodatabase.  You could write a simple script that runs the Table To Table conversion tool every night to copy the excel file into the geodatabase for the Relationship class.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Relationships_and_ArcGIS/004t000000010...

My second suggestion is you do exactly what you said and use a script to go from excel file to XY event layer, then export that into the geodatabase.  You can also set this up as a scheduled task to run every day.  Here is a simple example script showing how to do that:

import arcpy, os, sys, traceback
from os import path as p

arcpy.env.workspace = ws = r'C:\testing'

# Variables
xls = r'C:\testing\Cedar_Network.xlsx\Sheet1$'
spa_ref = r'C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD 1983 StatePlane Iowa South FIPS 1402 (US Feet).prj'
layer = 'XY_Event'
outfold = r'C:\testing\test.gdb'
output = p.join(outfold, 'control_points')

try:

    arcpy.MakeXYEventLayer_management(xls, 'EASTING', 'NORTHING', layer, spa_ref)
    arcpy.CopyFeatures_management(layer, output)
    print 'Feature Class Created'

except:
   
    
    # Get the traceback object
    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]

    # Concatenate information together concerning the error into a message string
    pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(sys.exc_info()[1])
    msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"

    # Return python error messages for use in script tool or Python Window
    arcpy.AddError(pymsg)
    arcpy.AddError(msgs)

    # Print Python error messages for use in Python / Python Window
    print pymsg + "\n"
    print msgs

[RafiqBasaria]

Thanks for that!! Im reading about relationship classes, but that may be kinda pointless since Ive only got ArcView licenses to work with. Im going to try to play with the scripting option. My boss at my old job wanted to set something up like that, but we never got around to it. Im not really good at understanding the basics of scripting languages, so I have a hard time writing things, but if I need to run a simple process, I can usually get that done.

[MarcoBoeringa]

I don't know if you have any experience with Microsoft Access, but if so, you could consider importing the Excel in Access (very easy), and even consider maintaining it in there, instead of a spreadsheet if you don't need Excel specific functions.

You could than set up a Windows ODBC source / link to the table in Access, that is accessible from within ArcGIS using the "Add OLE DB Connection" option in ArcCatalog. If you subsequently add the table to ArcMap, you can join it to a pre-existing Feature Class, or use X,Y coordinates in the table to dynamically show it as a X,Y point event layer using the tools for that in ArcMap (Make XY Event Layer (Data Management)).

Once setup, any changes made in Access, will be dynamically and automatically reflected in ArcMap (at least each time to you re-open the *.MXD document).

If you are not acquainted with ODBC sources / links, ask an IT administrator at your organization, I am sure they will be able to help you out with that, it is not difficult, you just need to see it once.

[RafiqBasaria]

Unfortunately, I do need the Excel functions.

[Anonymous User]

Thanks for that!! Im reading about relationship classes, but that may be kinda pointless since Ive only got ArcView licenses to work with. Im going to try to play with the scripting option. My boss at my old job wanted to set something up like that, but we never got around to it. Im not really good at understanding the basics of scripting languages, so I have a hard time writing things, but if I need to run a simple process, I can usually get that done.

The script I provided would be all you would need to use to accomplish this.  You would just need to change the variables (full path to the excel spreadsheet, path to output Geodatabase, path to Spatial Reference, and the Lat and Long fields.

However, if you do not feel comfortable scripting, I would suggest you try Marco's idea.  I think that would work well if you have Microsoft Access.

[RafiqBasaria]

Cool. Thanks a lot guys. Im going to try it through scripting. I think, overall, that would be the easiest solution. Im competent enough to change paths and things like that...hopefully haha. I mean, Ive done it before.

[RafiqBasaria]

Did the coordinate system .prj files move somewhere else for 10.1? I cant seem to find them. Also, will this work if 1. there are files in the GDB with the same name as the ones I want to put in (ie, can it replace existing files?), and 2. if the Excel table is not on my local drive? Thank you!!

[Anonymous User]

Did the coordinate system .prj files move somewhere else for 10.1? I cant seem to find them. Also, will this work if 1. there are files in the GDB with the same name as the ones I want to put in (ie, can it replace existing files?), and 2. if the Excel table is not on my local drive? Thank you!!

Yes, the Projections and Coordinate Systems folder has been moved for 10.1.  Now you can point to a spaitial reference by providing the name or factory code which can be looked up from one of these PDFs (PCS NAME OR WKID).

http://resources.arcgis.com/en/help/main/10.1/018z/pdf/projected_coordinate_systems.pdf
http://resources.arcgis.com/en/help/main/10.1/018z/pdf/geographic_coordinate_systems.pdf

for example:

sr = arcpy.SpatialReference(26776)

# OR (I think)
sr = arcpy.SpatialReference('NAD 1927 StatePlane Iowa South FIPS 1402 (US FEET)')

Although I should mention, I haven't done this in a while so this may not work, and unfortunately I cannot test this right now because I could not make it to work today (have 10.1 there) because of near blizzard like conditions.  I only have version 10.0 at home.

EDIT: I had originally posted the spatial reference wrong, It needs to be a string without underscores.  Also I just saw you can set the output coordinate system as an environment setting:

arcpy.env.outputCoordinateSystem = arcpy.SpatialReference('NAD 1927 StatePlane Iowa South FIPS 1402 (US FEET)')

As for replacing the existing file, yes this can be done easily by one of the following ways:

# set the environment setting to overwrite output
arcpy.env.overwriteOutput = True

# OR
table = r'G:\Some\Path\Table'
if arcpy.Exists(table):
    arcpy.Delete_management(table)
# Copy table to geodatabase after deleting original here

And finally, yes it does not matter where the excel file is located, as long as you provide the full path (down to the sheet).

[RafiqBasaria]

Ok great. Thank you again.

What is 'control_points' in the output variable?

I tried running and it seems to have failed to read the lat/longs...

try:

    arcpy.MakeXYEventLayer_management(xls, 'LONGITUDE', 'LATITUDE', layer)
    arcpy.CopyFeatures_management(layer, output)
    print 'Feature Class Created'

EDIT: I think I figured out why it cant read the XY data. My guess is one of 2 things, or maybe both.

1. The table is setup as all VLOOKUP values in excel. It just pulls values from other sheets within the file.

2. Since it is setup as VLOOKUPs, there is a reference number field for row 1, and then the field names in row 2.

Im guessing one of those things is causing it to be unable to plot those values.