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]

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]

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]

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

[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]

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]

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.

[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]

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

[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

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