Date Field Conflict w Feature Class to Feature Class Conversion via Arcpy

[GeorgeHaskett]

I recently built a model in model builder that converts a feature layer joined to a table into a new feature class.

While everything appears to work smoothly when ran the model via the model builder, I get the following error when I run a similar python script via the Python IDLE tool.  Note that I exported my working model from the model builder environment into a (.py) Python script, and used that as a reference when I wrote my new script.  This portion of the code is used in the optional "Field_Mapping" variable of the arcpy.FeatureClassToFeatureClass_conversion().

Below is the code line in question as exported from the model builder:

LOSSDATE \"LOSS DATE\" true true false 8 Date 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.LOSSDATE,-1,-1

The error message states:

[INDENT]ARCPY ERRORS:
ERROR 000278: Field mapping error: field type invalid for output field LOSSDATE and input field claimsTemp.LOSSDATE
Failed to execute (FeatureClassToFeatureClass).


PYTHON ERRORS:
Traceback Info:
  File "C:\arcgisserver\gisData\services\claimsMap\toolsNscripts\runClaimsMap.py", line 75, in <module>
    arcpy.FeatureClassToFeatureClass_conversion("gcPolicies_lyr", claims_gdb, "claimsPersTemp", "", fieldsFC)

Error Info:
     <class 'Queue.Empty'>: [/INDENT]

Thanks for any assistance,

Haskett
ArcGIS 10.2

Here is the complete code:

# Import arcpy module
import arcpy,sys,traceback
from arcpy import env

print "...establishing variables and parameters"

# Set Geoprocessing environments
arcpy.env.scratchWorkspace = "C:/arcgisserver/gisData/services/claimsMap/data/claims_1.gdb"
arcpy.env.workspace = "C:/arcgisserver/gisData/services/claimsMap/data/claims_1.gdb"

# Set overwrite option
arcpy.env.overwriteOutput = True

#########################################################
##### Local variables #####
inputData = "C:/arcgisserver/gisData/services/claimsMap/data/CatastropheDetails.xlsx"
claimsTemp = "C:/arcgisserver/gisData/services/claimsMap/data/claims_1.gdb/claimsTemp"
gcPolicies = "C:/arcgisserver/gisData/services/pcMap/data/actuarial.gdb/gcPoliciesDB"
claims_gdb = "C:/arcgisserver/gisData/services/claimsMap/data/claims_1.gdb"
locator = "D:/arcgisserver/gisData/data/streetmapData/Locators/USA"

fieldsLyr = "KEY KEY VISIBLE NONE;INSURED INSURED HIDDEN NONE;LINE LINE VISIBLE NONE;LATITUDE LATITUDE HIDDEN NONE;LONGITUDE LONGITUDE HIDDEN NONE; \
    CONFIDENCE CONFIDENCE HIDDEN NONE;SOURCE SOURCE HIDDEN NONE;NOTES NOTES HIDDEN NONE;PHYSADDRESS PHYSADDRESS HIDDEN NONE;PHYSCITY PHYSCITY HIDDEN NONE; \
    BLDGTYPE BLDGTYPE HIDDEN NONE;CSTR CSTR HIDDEN NONE;CSTRYR CSTRYR HIDDEN NONE;SIZE SIZE HIDDEN NONE;EXPDATE EXPDATE HIDDEN NONE;AGCY AGCY HIDDEN NONE; \
    AGT AGT HIDDEN NONE;CIS CIS HIDDEN NONE;FORMAE FORMAE HIDDEN NONE;FORMC FORMC HIDDEN NONE;VALAE VALAE HIDDEN NONE;VALC VALC HIDDEN NONE; \
    DED DED HIDDEN NONE;LIM LIM HIDDEN NONE;PC PC HIDDEN NONE;CORRECTPC CORRECTPC HIDDEN NONE;ANOMALIES ANOMALIES HIDDEN NONE;DISRKEY DISRKEY VISIBLE NONE"

fieldsFC = "KEY \"KEY\" true true false 26 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,gcPoliciesDB.KEY,-1,-1; \
    LINE \"LINE\" true true false 4 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,gcPoliciesDB.LINE,-1,-1; \
    DISRKEY \"DISRKEY\" true true false 20 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,gcPoliciesDB.DISRKEY,-1,-1; \
    CATEGORY \"CATEGORY\" true true false 4 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CATEGORY,-1,-1; \
    CLAIMGROUP \"CLAIM GROUP\" true true false 9 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CLAIMGROUP,-1,-1; \
    ADJUSTER \"ADJUSTER\" true true false 25 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.ADJUSTER,-1,-1; \
    AGENT \"AGENT\" true true false 25 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.AGENT,-1,-1; \
    POLICYNUMBER \"POLICY NO\" true true false 15 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.POLICYNUMBER,-1,-1; \
    CLAIMNUMBER \"CLAIM NO\" true true false 25 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CLAIMNUMBER,-1,-1; \
    LOB \"LOB\" true true false 25 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.LOB,-1,-1; \
    LOSSDATE \"LOSS DATE\" true true false 8 Date 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.LOSSDATE,-1,-1; \
    LOSSTYPE \"LOSS TYPE\" true true false 25 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.LOSSTYPE,-1,-1; \
    LOSSCAUSE \"LOSS CAUSE\" true true false 25 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.LOSSCAUSE,-1,-1; \
    CLAIMSTATUS \"CLAIM STATUS\" true true false 6 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CLAIMSTATUS,-1,-1; \
    DESCRIPTION \"DESCRIPTION\" true true false 255 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.DESCRIPTION,-1,-1; \
    ITEMNUMBER \"ITEM NO\" true true false 3 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.ITEMNUMBER,-1,-1; \
    ITEMPART \"ITEM PART\" true true false 50 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.ITEMPART,-1,-1; \
    ADDRESSLINE1 \"ADDRESS\" true true false 100 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.ADDRESSLINE1,-1,-1; \
    CITY \"CITY\" true true false 50 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CITY,-1,-1; \
    STATENM \"STATE\" true true false 5 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.STATENM,-1,-1; \
    CLAIMSTRATEGY \"CLAIM STRATEGY\" true true false 15 Text 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CLAIMSTRATEGY,-1,-1; \
    CLOSEDATE \"CLOSE DATE\" true true false 8 Date 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.CLOSEDATE,-1,-1; \
    RESERVE \"RESERVE\" true true false 4 Long 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.RESERVE,-1,-1; \
    RECOVERY \"RECOVERY\" true true false 4 Long 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.RECOVERY,-1,-1; \
    PAYMENT \"PAYMENT\" true true false 4 Long 0 0 ,First,#,C:\\arcgisserver\\gisData\\services\\pcMap\\data\\actuarial.gdb\\gcPoliciesDB,claimsTemp.PAYMENT,-1,-1"


try:


    # Process: Excel To Table
    print "...converting spreadsheet"
    arcpy.ExcelToTable_conversion(inputData, claimsTemp, "Page1_1")

    # Process: Make Feature Layer
    print "...making feature lyr"
    queryLyr = '"LINE" = \'Pers\''
    arcpy.MakeFeatureLayer_management(gcPolicies, "gcPolicies_lyr", queryLyr, "",  fieldsLyr)

    # Process: Add Join
    print "...joining tables"
    arcpy.AddJoin_management("gcPolicies_lyr", "DISRKEY", claimsTemp, "DISRKEY", "KEEP_COMMON")

    # Process: Feature Class to Feature Class
    print "...creating primary FC copy"
    arcpy.FeatureClassToFeatureClass_conversion("gcPolicies_lyr", claims_gdb, "claimsPersTemp", "", fieldsFC)

    # Process: Remove Join
    print "...removing joined tables"
    arcpy.RemoveJoin_management("gcPolicies_lyr", "")

    # Process: Copy Features
    print "...copying features"
    arcpy.CopyFeatures_management("claimsPersTemp","claimsPERS")

    # Process: Geocode Addresses
    print "...GeoCoding policies"
    arcpy.GeocodeAddresses_geocoding(claimsTemp, locator, "Address ADDRESSLINE1 VISIBLE NONE;City City VISIBLE NONE;Region STATENM VISIBLE NONE;Postal '' VISIBLE NONE", "claimsGeoCoded", "STATIC")
    print ""


except:

    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
    pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n     " + str(sys.exc_type) + ": " + str(sys.exc_value) + "\n"
    msgs = "ARCPY ERRORS:\n" + arcpy.GetMessages(2) + "\n"
    print msgs
    print pymsg
    print arcpy.GetMessages(1)

[IanMurray]

Are you actually using the fieldmapping at all?  If not you can just close your feature class to feature class function parentheses after all the required input.

arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)

[IanMurray]

If you aren't actually using the field mapping for anything, you could just not include it in the feature class to feature class conversion, since it is optional.

The other thing I was thinking is it is crashing on a joined field from the look of things, perhaps it would be best to create a new field, and hard code those joined values in, instead of using the joined field.  It says it is a field type error, so that might be what the problem is.

[GeorgeHaskett]

If you aren't actually using the field mapping for anything, you could just not include it in the feature class to feature class conversion, since it is optional.

The other thing I was thinking is it is crashing on a joined field from the look of things, perhaps it would be best to create a new field, and hard code those joined values in, instead of using the joined field.  It says it is a field type error, so that might be what the problem is.

Thanks Ian,

I am using the Field Mapping to remove unwanted fields and rename both the field names and aliases.

When I remove the two Date type fields, then everything runs correctly within the script.

Why do you think it is crashing on the Join field when using date formatted fields, but not crashing when the fields in question are removed?

Also, are you suggesting that I add two new Date fields and then use the field calculator to populate them prior to the feature to feature class conversion?

[IanMurray]

That was my suggestion yes, but now looking at it again, I'm doubting it will do anything.  It seems to be hanging up on the fact that it is a date field.  I really have no experience using them, so not sure how best to handle it.  Would it be possible to create a new text field to store the date data in during the feature class to feature class, then afterwards convert it back to a date field?  I know that is a bit of a workaround, but I'm not sure why date fields won't work with it.

Edit:  I tried a fc to fc conversion with field mapping on a test gdb feature class with a date field, and ran into no errors, both in ArcGIS and as a stand-alone script.

[GeorgeHaskett]

That was my suggestion yes, but now looking at it again, I'm doubting it will do anything.  It seems to be hanging up on the fact that it is a date field.  I really have no experience using them, so not sure how best to handle it.  Would it be possible to create a new text field to store the date data in during the feature class to feature class, then afterwards convert it back to a date field?  I know that is a bit of a workaround, but I'm not sure why date fields won't work with it.

Edit:  I tried a fc to fc conversion with field mapping on a test gdb feature class with a date field, and ran into no errors, both in ArcGIS and as a stand-alone script.

How did your field mapping code look for the date field in question?  I posted my separately in my first post.

Did it follow a join?

It is interesting that I can run it in a model, and in a stand alone script after removing the date fields. 

If I add a Copy FC prior to the FC to FC conversion then it works with the date fields, however I seem to loose the ability to change the specs on the new fields when I use this work around...

[markdenil]

I always distrust line continuation flags (especially as they are deprecated in python...)

When I have a long string argument, I prefer to build it from a list
Just because it is easier for me to see and debug,
and because it gets re-assembled as I expect.

for example

fieldsLyrList = [
        "KEY KEY VISIBLE NONE;",
        "INSURED INSURED HIDDEN NONE;",
        "LINE LINE VISIBLE NONE;",
        "LATITUDE LATITUDE HIDDEN NONE;",
        "LONGITUDE LONGITUDE HIDDEN NONE;",
        "CONFIDENCE CONFIDENCE HIDDEN NONE;",
        "SOURCE SOURCE HIDDEN NONE;",
        "NOTES NOTES HIDDEN NONE;",
        "PHYSADDRESS PHYSADDRESS HIDDEN NONE;",
        "PHYSCITY PHYSCITY HIDDEN NONE;",
        "BLDGTYPE BLDGTYPE HIDDEN NONE;",
        "CSTR CSTR HIDDEN NONE;",
        "CSTRYR CSTRYR HIDDEN NONE;",
        "SIZE SIZE HIDDEN NONE;",
        "EXPDATE EXPDATE HIDDEN NONE;",
        "AGCY AGCY HIDDEN NONE;",
        "AGT AGT HIDDEN NONE;",
        "CIS CIS HIDDEN NONE;",
        "FORMAE FORMAE HIDDEN NONE;",
        "FORMC FORMC HIDDEN NONE;",
        "VALAE VALAE HIDDEN NONE;",
        "VALC VALC HIDDEN NONE;",
        "DED DED HIDDEN NONE;",
        "LIM LIM HIDDEN NONE;",
        "PC PC HIDDEN NONE;",
        "CORRECTPC CORRECTPC HIDDEN NONE;",
        "ANOMALIES ANOMALIES HIDDEN NONE;",
        "DISRKEY DISRKEY VISIBLE NONE"
        ]

fieldsLyr = ''.join(fieldsLyrList)

[GeorgeHaskett]

I always distrust line continuation flags (especially as they are deprecated in python...)

When I have a long string argument, I prefer to build it from a list
Just because it is easier for me to see and debug,
and because it gets re-assembled as I expect.

for example

fieldsLyrList = [
        "KEY KEY VISIBLE NONE;",
        "INSURED INSURED HIDDEN NONE;",
        "LINE LINE VISIBLE NONE;",
        "LATITUDE LATITUDE HIDDEN NONE;",
        "LONGITUDE LONGITUDE HIDDEN NONE;",
        "CONFIDENCE CONFIDENCE HIDDEN NONE;",
        "SOURCE SOURCE HIDDEN NONE;",
        "NOTES NOTES HIDDEN NONE;",
        "PHYSADDRESS PHYSADDRESS HIDDEN NONE;",
        "PHYSCITY PHYSCITY HIDDEN NONE;",
        "BLDGTYPE BLDGTYPE HIDDEN NONE;",
        "CSTR CSTR HIDDEN NONE;",
        "CSTRYR CSTRYR HIDDEN NONE;",
        "SIZE SIZE HIDDEN NONE;",
        "EXPDATE EXPDATE HIDDEN NONE;",
        "AGCY AGCY HIDDEN NONE;",
        "AGT AGT HIDDEN NONE;",
        "CIS CIS HIDDEN NONE;",
        "FORMAE FORMAE HIDDEN NONE;",
        "FORMC FORMC HIDDEN NONE;",
        "VALAE VALAE HIDDEN NONE;",
        "VALC VALC HIDDEN NONE;",
        "DED DED HIDDEN NONE;",
        "LIM LIM HIDDEN NONE;",
        "PC PC HIDDEN NONE;",
        "CORRECTPC CORRECTPC HIDDEN NONE;",
        "ANOMALIES ANOMALIES HIDDEN NONE;",
        "DISRKEY DISRKEY VISIBLE NONE"
        ]

fieldsLyr = ''.join(fieldsLyrList)

Thanks, that's an interesting idea.  I hadn't thought about using a list. 

I did get it to work going about it the 'extra mile' method.  I added new temp text fields, then populating them prior to the FC to FC conversion, then adding another set of date fields and populating them using the newly created text fields, similar to what Ian suggested.  Its extra code, but it appears to be working.  I'll give your method a try to see if that can reduce the amount of code I have.

[GeorgeHaskett]

I always distrust line continuation flags (especially as they are deprecated in python...)

When I have a long string argument, I prefer to build it from a list
Just because it is easier for me to see and debug,
and because it gets re-assembled as I expect.

for example

fieldsLyrList = [
        "KEY KEY VISIBLE NONE;",
        "INSURED INSURED HIDDEN NONE;",
        "LINE LINE VISIBLE NONE;",
        "LATITUDE LATITUDE HIDDEN NONE;",
        "LONGITUDE LONGITUDE HIDDEN NONE;",
        "CONFIDENCE CONFIDENCE HIDDEN NONE;",
        "SOURCE SOURCE HIDDEN NONE;",
        "NOTES NOTES HIDDEN NONE;",
        "PHYSADDRESS PHYSADDRESS HIDDEN NONE;",
        "PHYSCITY PHYSCITY HIDDEN NONE;",
        "BLDGTYPE BLDGTYPE HIDDEN NONE;",
        "CSTR CSTR HIDDEN NONE;",
        "CSTRYR CSTRYR HIDDEN NONE;",
        "SIZE SIZE HIDDEN NONE;",
        "EXPDATE EXPDATE HIDDEN NONE;",
        "AGCY AGCY HIDDEN NONE;",
        "AGT AGT HIDDEN NONE;",
        "CIS CIS HIDDEN NONE;",
        "FORMAE FORMAE HIDDEN NONE;",
        "FORMC FORMC HIDDEN NONE;",
        "VALAE VALAE HIDDEN NONE;",
        "VALC VALC HIDDEN NONE;",
        "DED DED HIDDEN NONE;",
        "LIM LIM HIDDEN NONE;",
        "PC PC HIDDEN NONE;",
        "CORRECTPC CORRECTPC HIDDEN NONE;",
        "ANOMALIES ANOMALIES HIDDEN NONE;",
        "DISRKEY DISRKEY VISIBLE NONE"
        ]

fieldsLyr = ''.join(fieldsLyrList)

FYI,

When I used a list I received the following error:

[INDENT]PYTHON ERRORS:
Traceback Info:
  File "C:/arcgisserver/gisData/services/claimsMap/toolsNscripts/runClaimsMap_v2.py", line 125, in <module>
    arcpy.MakeFeatureLayer_management(gcPolicies, "gcPolicies_lyr", queryLyr, "",  fieldsLyr)

Error Info:
     <type 'exceptions.RuntimeError'>: Object: Error in executing tool[/INDENT]

However when I dropped the commas and change the list to a continued line using () it ran up to the FC to FC conversion point and then gave me the same old error...

I was looking up reference for the line continuation issue and a Python wedsite implied that you can use the () instead of the \ character to continue long lines.

Are their plans to drop support for all line continuation characters after 2.7 or just the '\' one in question?