nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Encuentra más publicaciones etiquetadas con

Accepted answers

All comments

NathanGardiner

I'm a bit late to the party, but I have been trying to iterate from an active XLS sheet to my shapefile. I have had no luck trolling through ESRI help sites etc, which simply astounds me (i.e. that I cannot update an Excel sheet and simply update the shapefile to read in the changes).

I don't do well with scripting, also it doesn't resolve my dilemma of having to reformat my point data style each time, which is an additional tedious step.

Has there been any progress on this function? It would seem to me to be a trivial function, but I've had no luck cracking it without introducing multiple complexities.

Thanks.

Ayesha_

how can we add layer into TOC or map document i have tried by doing

arcpy.MakeXYEventLayer_management(xls, 'EASTING', 'NORTHING', layer, spa_ref)

arcpy.SetParameter(0,"control_points")

pmxd = arcpy.mapping.MapDocument("CURRENT")

pdf = arcpy.mapping.ListDataFrames(pmxd)[0]

arcpy.mapping.AddLayer(pdf,)

arcpy.CopyFeatures_management(layer, output)

print 'Feature Class Created'

but its not working

RafiqBasaria

So much for that...gave me this error...

Failed to execute. Parameters are not valid.
ERROR 000732: XY Table: Dataset C:\Documents\DatabaseData\LHSBM.csv does not exist or is not supported
WARNING 000725: Layer Name or Table View: Dataset LongHornSBM already exists.
Failed to execute (MakeXYEventLayer).

RafiqBasaria

Ill also add that I have done this process manually using this table. I may have converted it to a CSV, but I dont remember at this point. Ive never had so many issues with excel tables being brought into GDBs as I have here. I guess I can try converting the table to a CSV and see if that works.

RafiqBasaria

Got this error with the table to table...

Failed to execute. Parameters are not valid.
ERROR 000732: Output Location: Dataset C:\Documents\ArcGIS\DardenMaster.gdb does not exist or is not supported
Failed to execute (TableToTable).

As far as spaces in the excel sheet...I had issues with this with the original table, which is why I copied everything to its own table. Ill go back through and double check, but everything should be good.

Anonymous User

Interesting, I do not see any problems with your code. I was able to get this to work with exactly the same code, just substituting the variables. Maybe try this instead of copy features:

arcpy.TableToTable_conversion (layer, outfold, 'LongHornSBM')

If this doesn't work either, perhaps the file has been corrupted along the way. My only other idea as to what could be causing errors would be if maybe there is a space in one of the field names in the excel sheet?

RafiqBasaria

I can post the code, but Im not allowed to post the excel table.

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

arcpy.env.workspace = ws = r'C:\Documents\DatabaseData'
arcpy.env.overwriteOutput = True
arcpy.env.outputCoordinateSystem = arcpy.SpatialReference('WGS 1984')

# Variables
xls = r'Y:\Strategy by Market Reports and Data\Darden Master SBM Table.xlsx\LHGIS$'
layer = 'LongHornSBM'
outfold = r'C:\Documents\ArcGIS\DardenMaster.gdb'
output = p.join(outfold, 'LongHornSBM')

try:

    arcpy.MakeXYEventLayer_management(xls, 'LONGITUDE', 'LATITUDE', layer)
    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

Anonymous User

Can you post your full code and a screenshot of the excel table?

RafiqBasaria

I just copied the values of the fields to a new sheet, and that fixed that particular issue.

I just got an error that it failed to execute the copyfeatures function.

Anonymous User

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.

The 'control_points' in my script was just the name for the output file I chose so it is not important. You can use copy features or TableToTable_conversion.

As for your lat/long fields, it will not work unless those values are actually directly in the sheet you are referencing when you create the XYEvent layer. In order for this to work you would need to have those fields in the sheet you are pointing to. It can only access one sheet at a time. I would suggest you use the sheet that has the actual lat/long fields and use that to create the XYEvent layer and then use AddJoin_management to join the other sheets to that event layer and then copy the table to the geodatabase.

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.

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

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

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.

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

Unfortunately, I do need the Excel functions.

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

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.

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/004t00000001000000/

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

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.