Python- need help with a code

[TheoFaull]

Hi all,

I am new at a company and I'm investigating a problem with a python script we have. The script basically extracts 1000s of rows of property data from a database (QL) and by using X Y data stored in this database, it creates a shapefile which we then use throughout the company.

The script was authored by a python expert who doesn't work here anymore. The script worked fine for him, and all the software was on 10.0 at the time. After an upgrade to 10.3 and 10.4, the script doesn't work for us now and throws up an ERROR 000210: Cannot create output K:\ blah blah..   Failed to execute (FeatureClassToFeatureClass). As I'm not that experienced with python scripting and the author of the script isn't around to help, I thought I'd ask here.

Here is the original code that has returned an error, only after upgrading our software from 10.0.

# ---------------------------------------------------------------------------

# All_Curo_Properties.py

# Created on: 2013-03-19 09:26:01.00000

#   (generated by ArcGIS/ModelBuilder)

# Description:

# ---------------------------------------------------------------------------

# Import arcpy module

import arcpy

# Overwrite pre-existing files

arcpy.geoprocessing.env.overwriteOutput = True

# Local variables:

Sheet1_ = "K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$"

QL_PropertyData = "K:\\GIS2007\\Data\\Somer\\PropertyData\\QL_PropertyData"

Sheet1___2_ = "K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$"

QL_PropertyData__2_ = "K:\\GIS2007\\Data\\Somer\\PropertyData\\QL_PropertyData"

Sheet1__Layer = "Sheet1$_Layer"

# Process: Make XY Event Layer

arcpy.MakeXYEventLayer_management(Sheet1___2_, "easting", "northing", Sheet1__Layer, "PROJCS['British_National_Grid',GEOGCS['GCS_OSGB_1936',DATUM['D_OSGB_1936',SPHEROID['Airy_1830',6377563.396,299.3249646]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Transverse_Mercator'],PARAMETER['False_Easting',400000.0],PARAMETER['False_Northing',-100000.0],PARAMETER['Central_Meridian',-2.0],PARAMETER['Scale_Factor',0.9996012717],PARAMETER['Latitude_Of_Origin',49.0],UNIT['Meter',1.0]];-5220400 -15524400 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision", "")

# Process: Feature Class to Feature Class

arcpy.FeatureClassToFeatureClass_conversion(Sheet1__Layer, QL_PropertyData__2_, "CuroProperties_All.shp", "", "prty_id \"prty_id\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,prty_id,-1,-1;alt_prty_i \"alt_prty_i\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,alt_prty_id,-1,-1;Title \"Title\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Title,-1,-1;Forename \"Forename\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Forename,-1,-1;Surname \"Surname\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Surname,-1,-1;fmly_mems \"fmly_mems\" true true false 8 Double 6 15 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,fmly_mems,-1,-1;add_1 \"add_1\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,add_1,-1,-1;add_2 \"add_2\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,add_2,-1,-1;add_3 \"add_3\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,add_3,-1,-1;add_4 \"add_4\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,add_4,-1,-1;add_5 \"add_5\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,add_5,-1,-1;post_code \"post_code\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,post_code,-1,-1;fulladdr \"fulladdr\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,fulladdr,-1,-1;PropertyTy \"PropertyTy\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,PropertyType,-1,-1;sh_size_de \"sh_size_de\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,sh_size_desn,-1,-1;ShortSizeN \"ShortSizeN\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,ShortSizeName,-1,-1;bed_nos \"bed_nos\" true true false 8 Double 6 15 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,bed_nos,-1,-1;OwnershipT \"OwnershipT\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,OwnershipType,-1,-1;Landlord \"Landlord\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Landlord,-1,-1;Landlord1 \"Landlord1\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Landlord1,-1,-1;landlord3 \"landlord3\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,landlord3,-1,-1;cur_tse_no \"cur_tse_no\" true true false 8 Double 6 15 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,cur_tse_no,-1,-1;tency_st_d \"tency_st_d\" true true false 8 Date 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,tency_st_dt,-1,-1;NeedsDesc \"NeedsDesc\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,NeedsDesc,-1,-1;Source \"Source\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Source,-1,-1;easting \"easting\" true true false 8 Double 6 15 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,easting,-1,-1;northing \"northing\" true true false 8 Double 6 15 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,northing,-1,-1;status \"status\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,status,-1,-1;let_yn \"let_yn\" true true false 2 Short 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,let_yn,-1,-1;hb_ref \"hb_ref\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,hb_ref,-1,-1;rent_acc_n \"rent_acc_n\" true true false 8 Double 6 15 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,rent_acc_no,-1,-1;loc_auth \"loc_auth\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,loc_auth,-1,-1;Arrears_ \"Arrears_\" true true false 255 Text 0 0 ,First,#,K:\\GIS2007\\scratchdata\\PropertyUpdate\\GIS Listing.xlsx\\Sheet1$,Arrears?,-1,-1", "")

print "SMaRT Update Complete"

And this is the error:

We can't experiment and run modified scripts until the evening when no one is working in the office and using the data. So in the meantime, I'd love some of your expert tips!

Thanks.

[DanPatterson_Retired]

moved to Python​ (use the 'share' option rather than double-posting)

Digesting that output could be assisted by looking for keywords that aren't allowed in sql queries, that error message is associated with it and the inability to create an output.  If the script worked in the past and doesn't work now, then trackdown internally what the difference in inputs or outputs might have been between runs.

[TheoFaull]

Thanks for moving it. I tried the share option but I got an error!: 'Error sharing content to a place. Could not create share of content in the specified place.'

Anyway, so the python error message only relates to dodgy keywords? From some basic research on the ESRI help site, I wondered whether I needed to define an outLocation and an outFeatureClass under the # Local variables section of the script?

I'll investigate changes between runs too...

[DanPatterson_Retired]

you have to specify a path to the outputs and inputs or set your working environment  arcpy.env = .....

Current Workspace (Environment setting)—Help | ArcGIS for Desktop

env—Help | ArcGIS for Desktop

[TheoFaull]

Which the code has neither of?

[DanPatterson_Retired]

Make XY Event Layer—Help | ArcGIS for Desktop perhaps...I haven't looked at the mess in detail

the output is just a name... did you try adding and it shouldn't have a $ in it to be on the safe side

arcpy.env.workspace = "specify your workspace here"

into the code

and you have confirmed that the field names are indeed lower case etc etc.

The list of potential sources of error is far from complete

especially the output filename in this doesn't have a workspace path to go to since it is going to a shapefile and not into a geodatabase

Feature Class to Feature Class—Help | ArcGIS for Desktop , so

arcpy.FeatureClassToFeatureClass_conversion(Sheet1__Layer, QL_PropertyData__2_, "CuroProperties_All.shp

[TheoFaull]

thanks for the tips Dan. We actually managed to run the script today and although the error still appeared, the data still popped up in our ArcMap session. As an XY layer, not a shapefile. So we simply right click the layer, Data>Export Data... choose shapefile. And we have what we were after.

[DanPatterson_Retired]

Ok that is probably the quickest, but you can finish it off with the featureclasstofeatureclass, but it probably isn't worth the extra effort if you are going to be monitoring the outputs in the first place.

[WesMiller]

The script was created in model builder, with the two tools below. It would not be difficult to recreate in model builder and export again. You would be able to validate all the variables in model builder before you exported.

Make XY Event Layer—Help | ArcGIS for Desktop

Feature Class to Feature Class—Help | ArcGIS for Desktop