GeoProcessing tool returning Table to Excel error: <empty>

[JustinGleeson1]

I have a GeoProcessing tool that develops a Census profile based on a user defined study area - the final output using Table to Excel.

The model operates perfectly in ArcGIS and produces the output that i require. It also publishes to Server without any errors or warnings. However, when I run the tool on ArcGIS Server I get an error saying that the .xls is <empty>.

Any ideas what could be happening here?

Image below of model and then error when I run the model

Thanks

Justin

[DanPatterson_Retired]

Do you want to move this to https://community.esri.com/community/gis/enterprise-gis/arcgis-for-server?sr=search&searchId=f1521c5...‌ Justin?  When you enter the server realm, you have a whole different set of issues to content with.  Or use the Share option at least.

[JonathanQuinn]

Do you want the user running the GP service to be able to download the .xls file?  What is the output parameter file type?  Can you try to set it to File if it isn't and republish the service?  Can you also provide a screenshot of the error, or the final JSON response when monitoring the job status using Fiddler or the Dev tools?

[JustinGleeson1]

Hi Jonathan,

Yes, I want to be able to download an Excel file. This is set to 'file' so no problem there.

I have had a look at the logs on Server Manager and it seems that the problem may be with the parameter that I am taking from the DataPrep python script tool. The final table that I need to link to Table to Excel (essentially GeoProfile, the output parameter from the DataPrep script) seems to be inaccessible when I run on Server.

This is the error log that I get on Server Manager:

Error executing tool. Model2 Job ID: j91533738840043b48bf730482226e32e : ERROR 000732: Input Table: Dataset c:\arcgisserver\directories\arcgisjobs\geoprocessing_dev\airogeoprofile_roi_gpserver\j91533738840043b48bf730482226e32e\scratch\scratch.gdb\GeoProfile does not exist or is not supported Failed to execute (Table To Excel). Failed to execute (Model2). Failed to execute (GeoProfile_RoI_2). 

This is a snippet of the last code in the script tool. There are a number of different things happening here such as joining other tables, deleting fields etc. The final table, in the order that I need is then called GeoProfile. I then tried to set this as the output parameter in the script tool so I can connect it to the Table to Excel tool within the main model.

"

arcpy.JoinField_management("ThemesIndicators_1", "IndCode", "PopSummaryS", "Indicators", ["IndCode","Census2011"])
arcpy.JoinField_management("ThemesIndicators_1", "Indicator", "ThemesIndicators_Comps_1", "Indicator", "State;Carlow;DCC;SDCC;Fingal;DLR;Kildare;Kilkenny;Laois;Longford;Louth;Meath;Offaly;Westmeath;Wexford;Wicklow;Clare;Cork_ City;Cork_Co;Kerry;Limerick;Tipperary;Waterford;Galway_City;Galway_Co;Leitrim;Mayo;Roscommon;Sligo;Cavan;Donegal;Monaghan")
arcpy.DeleteField_management("ThemesIndicators_1", ["IndCode"])
arcpy.Copy_management("ThemesIndicators_1", "GeoProfile")
arcpy.SetParameterAsText(0, "GeoProfile")

except: 

pass

"

You may notice something incorrect here

Thanks for your help

Justin

[JonathanQuinn]

So "Geoprofile" is the Excel table?

c:\arcgisserver\directories\arcgisjobs\geoprocessing_dev\airogeoprofile_roi_gpserver\j91533738840043b48bf730482226e32e\scratch\scratch.gdb\GeoProfile

It's trying to write a to the scratch geodatabase, which won't work.  I suggest you re-write the output location of the Excel file using the scratch folder:

Eg.

outTable = os.path.join(arcpy.env.scratchFolder,"GeoProfile.xls")

That way, you ensure the Excel table will be written to a folder.

[JustinGleeson1]

Hi Jonathan,

No, not quite.

GeoProfile is the final table that I want to export to an Excel file. GeoProfile is set as an output parameter in the script and I then connect GeoProfile to the Table to Excel tool in the main model. It seems that when operating on Server the Table to Excel tool is not finding 'GeoProfile' and then can't export the final Excel file.

Thanks

Justin

[JonathanQuinn]

I see, and where is GeoProfile going?  What scratch workspace are you using?

arcpy.Copy_management("ThemesIndicators_1", "GeoProfile")

Do you have ArcMap on the Server machine, or can you reach the arcgisjobs directory using ArcMap ArcCatalog?  You can check in the scratch geodatabase to see what's actually being written there.  Making sure you use a "dynamic" scratch workspace for your outputs might be a good idea if you're not already so you know exactly where it's supposed to go.  For example, construct the path to any data you want use arcpy.env.scratchFolder and arcpy.env.scratchGDB, (see my above post).  There's also a chance that the published script, (the one in the arcgisinput file), has been modified during publishing time in a way that invalidates an output location.  This was particularly true in earlier version of ArcGIS Server.

[JustinGleeson1]

HI Jonathan,

As it stands GeoProfile is being written to the main .gdb where all of the other data is. I set this at the start of the Python script tool:

env.workspace = r"C:\Projects\GeoProcessingTools\GeoProfile_RoICensus\GeoProfile_RoI.gdb"

I've traced this down and it seems that the file that I require (GeoProfile) to be linked to the Table to Excel is not appearing the the Jobs Scratch directory at all when run on server. In fact, it seems that my python script tool is not actually editing and making permanent changes when it runs on server at all however it does this perfectly when run on my local machine.

How would I set 'GeoProfile' to be set to a scratch gdb?

[JonathanQuinn]

There are two things you can do:

1)  Set the scratch workspace environment variable to in_memory, and specifically set the path when it shouldn't go to "in_memory".  This can be done by adding the follwoing line to your script:

arcpy.env.scratchWorkspace = "in_memory"

Whenever a GP tool runs, (and it's valid to write to in_memory), simply specify the name of the output and it'll write to the in_memory workspace.  In the situation where writing to in_memory is not appropriate, (for example creating an .xls file), construct the path to that output using arcpy.env.scratchFolder:

Eg.

outTable = os.path.join(arcpy.env.scratchFolder,"GeoProfile.xls")

2) Construct the path for every output using arcpy.env.scratchFolder or arcpy.env.scratchGDB:

outGeoProfile = os.path.join(arcpy.env.scratchGDB,"GeoProfile")

By using arcpy.env.scratchGDB, arcpy.env.scratchFolder, and setting arcpy.env.scratchWorkspace to "in_memory", you ensure that regardless of the script running in ArcMap, or Server, you're writing to consistent locations specific to where you run it:

arcpy.env.scratchGDB

  - Desktop - C:\Users\<user>\Documents\ArcGIS\scratch.gdb 

  - Server - <jobs_directory>/<jobID>/scratch.gdb

arcpy.env.scratchFolder

  - Desktop - C:\Users\<user>\Documents\ArcGIS\scratch

  - Server - <jobs_directory>/<jobID>/scratch

"in_memory" can be treated the same as a file geodatabase.

[JustinGleeson1]

Hi Jonathan,

Thanks for your comments on this! I've tried your suggestions but can't seem to get this to work.

My script tool makes a series of adjustments to "ThemesIndicators_1" with the last bit being DeleteField. At this point my table is fully processed and ready to be exported to excel in the way that I want it (if possible). I guess what I want to do is save ThemesIndicators_1 as ThemesIndicators_2 and make this available as an output parameter in my main model so I can proceed with the Table to Excel function.

This is the last part of my script:

arcpy.DeleteField_management("ThemesIndicators_1", ["IndCode"])
OutTable = os.path.join(arcpy.env.scratchGDB,"ThemesIndicators_2")
arcpy.CopyFeatures_management(ThemesIndicators_1, OutTable)
arcpy.SetParameterAsText(0, "ThemesIndicators_2")

After many different attempts I still can't get this to run on the server as ThemesIndicators_2 can't be found in the scratch gdb for it to work. Error here:

c:\arcgisserver\directories\arcgisjobs\geoprocessing_dev\airogeoprofile_roi_gpserver\j225cda15bd37482c85f1f67aaddb3270\scratch\scratch.gdb\ThemeIndicators_2 does not exist or is not supported Failed to execute (Table To Excel). Failed to execute (Model2). Failed to execute (GeoProfile_RoI_2).