Why is FeatureClassToFeatureClass tool Failing on Long cell values (Arc 10.2)?

Question

I have written a script to convert a CSV into a Feature Class. The actual line that does the conversion is the very last line in the script in which I call the FeatureClassToFeatureClass tool.

Here is my Code Snippet:

import arcpy
Source_CSV = r"\\int.ec\Data\Stations.csv"
Target_FC = r"Q:\Data\Stations.gdb\Stations"
Description = arcpy.Describe(Target_FC)
Target_FC_name = Description.name
Target_GDB = os.path.split(Description.catalogPath) [0]
Temp_Layer = "Temp_Layer"
SR = arcpy.Describe(Target_FC).spatialReference
arcpy.MakeXYEventLayer_management(Source_CSV, "Lng", "Lat", Temp_Layer, SR, "")
arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, "", "", "")

I am getting the following error:

File "Q:\scripts\CSVtoASMADPointFeatureClass.py", line 10, in <module>
    arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, "", "" , "")
  File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\conversion.py", line 1675, in FeatureClassToFeatureClass
    raise e

ExecuteError: ERROR 001156: Failed on input OID 1263820748, could not write value 'LS_ blue with white trim_ has overflow in Ruisseau Isabelle. Located between "Au P'tit Ruisseau" bar and Bas-Caraquet welcome sign_ just past Rue de Paul. A By-pass was reported at this LS on July 7_ 2012 and a temporary closed status zone was put in place.      ' to output field Comment
    Failed to execute (FeatureClassToFeatureClass).

The value that FeatureClassToFeatureClass could not write is from the 14th row in the CSV. I assume FeatureClassToFeatureClass proceeds sequentially through the CSV, which if correct tells me it had no problem with any of the cell values in the previous lines.

Here are the first 14 rows from my CSV's "Comment" field:

Comment
Foo Bar - overflow to vegetated surface      
overflow to vegetated landscape      
overflow to field      
overflow to field      

Flows into adjacent brook.  station is close to lagoon      this lift station serves a few homes - generally will pump 30 mins over two days.  have 2 pumps 
Lift station #6 (Rue de l'Ile). No standby power_ dual pumps_ total capacity 500 gal. per min. Overflow pipe to Caraquet Harbour. In case of failure_ alarm system sends signal to pager of LS operator.      
LS situated on Chemin St Simon_ No. #7. Located in a small baby barn building off road.      
Propane backup; next to lagoon;       
October 2015: Back up will be installed at this site this year.   Small grey shingled shack with RV dump station beside LS. Overflow_ near Ruisseau Isabelle. At small picnic park      
   October 2015 - lite and buzzer  small station - handles 3 houses and small campground.      Lift station on Rue François Gionet. UTM estimated. This LS pumps at LS Parc Fondateur.      less than 1 %
october 2015 - no lites just pager;  small flow station and no backup   White lift station with siding. Situated at 2264 rue Industrielle. No red warning light_ nor backup power observed.      
Blue & white trim lift station on "rue du Lac". New sewage system being installed at time of inspection. LS services 24 houses on Rue du Lac. LS does not have an apparent overflow pipe nor backup power.      
LS_ blue with white trim_ has overflow in Ruisseau Isabelle. Located between "Au P'tit Ruisseau" bar and Bas-Caraquet welcome sign_ just past Rue de Paul. A By-pass was reported at this LS on July 7_ 2012 and a temporary closed status zone was put in place. 

It has been suggested that the problem is with the single quotation mark in "P'tit", as shown in the error message.

The only problem is P'tit is part of a legitimate place name entered by someone else. I need every character in the comments field to be transferred.

Further, it has been suggested that the problem is not so much with FeatureClassToFeatureClass as it is the CSV converter not escaping single apostrophes.

If this is the problem what coding will force the CSV converter to escape single apostrophes?

Answer 1

It's nothing to do with the single quote in the 14th row comment field. Your 7th row also has a single quote and ArcGIS happily accepts that.

Your comment string that's failing is 259 characters long. It looks like ArcGIS 10.2 is creating the output FC Comments text field with a width of 256 (or 254 the shapefile limit).

When I run your code in ArcGIS 10.3, it works as expected as the output FC Comments text field is created with a width of 8000 characters by default.

Option 1. Upgrade ArcGIS.

Seriously! 10.2 is sooo old.

I understand if you can't though. We are stuck at 10.2 because of reliance on an ancient version of ArcSDE and Oracle. I had to get special permission to have 10.3 installed. Sigh.

Option 2. Use the field map parameter.

I managed to reproduce your error with this code:

arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, field_mapping="""Comment "Comment" true true false 256 Text 0 0 ,First,#,Temp_Layer,Comment,-1,-1""")

i.e.

c:\Python27\ArcGIS10.3\python test.py
Traceback (most recent call last):
  File "test.py", line 13, in <module>
    arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, field_mapping="""Comment "Comment" true true false 256 Text 0 0 ,First,#,Temp_Layer,Comment,-1,-1""")
  File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\conversion.py", line 1789, in FeatureClassToFeatureClass
    raise e
arcgisscripting.ExecuteError: ERROR 001156: Failed on input OID -1, could not write value 'LS_ blue with white trim_ has overflow in Ruisseau Isabelle. Located between Au P'tit Ruisseau bar and Bas-Caraquet welcome sign_ just past Rue de Paul. A By-pass was reported at this LS on July 7_ 2012 and a temporary closed status zone was put in place.  ' to output field Comment
Failed to execute (FeatureClassToFeatureClass).

The following should work for you:

etc...
Description = arcpy.Describe(Target_FC)
Target_FC_name = Description.name
Target_GDB = os.path.split(Description.catalogPath)[0]
Temp_Layer = "Temp_Layer"
SR = Description.spatialReference
arcpy.MakeXYEventLayer_management(Source_CSV, "Lng", "Lat", Temp_Layer, SR, "")
# arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, "", "", "")
arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, field_mapping="""Comment "Comment" true true false 8000 Text 0 0 ,First,#,Temp_Layer,Comment,-1,-1""")

Answer 2

Try this and see if it works for you:

import arcpy, os
Source_CSV = r"\\int.ec\Data\Stations.csv"
Temp    = os.environ.get('TEMP')

# replace single quotes with ~
TempCSV = os.path.join(Temp,'Temp.csv')
with open(Source_CSV,'r') as InFile:
    with open(TempCSV,'w') as OutFile:
        for Line in InFile:
            OutLine = Line.replace("\'","~")
            OutFile.write(OutLine)

Target_FC = r"Q:\Data\Stations.gdb\Stations"
Description = arcpy.Describe(Target_FC)
Target_FC_name = Description.name
Target_GDB = os.path.split(Description.catalogPath) [0]
Temp_Layer = "Temp_Layer"
SR = arcpy.Describe(Target_FC).spatialReference
arcpy.MakeXYEventLayer_management(Source_CSV, "Lng", "Lat", Temp_Layer, SR, "")
arcpy.FeatureClassToFeatureClass_conversion(Temp_Layer, Target_GDB, Target_FC_name, "", "", "")

# replace ~ with a single quote
for ThisField in arcpy.ListFields(os.path.join(Target_GDB, Target_FC_name),field_type='String'):
    arcpy.CalculateField_management(os.path.join(Target_GDB, Target_FC_name),
            ThisField.name,'!{}!.replace("~","\\\'")'.format(ThisField.name),
            "PYTHON")
os.remove(TempCSV) # get rid of the temp file

It's a simple switch to replace the problem character with another character (in this case ~ which doesn't come up much, if you find that ~ does come up use something like a double pipe || instead) before loading then replace using field calculator for all string fields to switch back.. in the field calc the string is double escaped \\\' becomes \' in the field calculation. You could also take this opportunity to remove leading/trailing spaces, tabs and other undesirable characters or strings that seem to pop up in CSV files that aren't pre-validated.