3

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?

  • You've got a single quote in "P'tit". – mkennedy May 23 '18 at 23:55
  • I wondered about that. The only problem is this is from a "comments" column and P'tit is part of a legitimate place name entered by someone else. I need every character in the comments field to be transferred. I will ask this in a new question. – Waterman May 24 '18 at 0:01
  • You could find and replace in the file ''' with '~' before loading then field calc '~' to ''' with a single replace statement after loading... assuming ~ is not already in the file. – Michael Stimson May 24 '18 at 0:18
  • @Luke Code snippet added. What is the best way for me to share the section of CSV you requested? – Waterman May 24 '18 at 0:42
  • @Luke Done. Hope that suffices. – Waterman May 24 '18 at 1:17
7

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""")
  • Bingo. Thanks that was it. Arc 10.2.2 must limit Feature Classes to 256 characters. But I notice that you have the field mapping in the 4th argument slot, but Esri's page on FeatureClassToFeatureClass says the 4th argument is for a Where Clause, and field mapping is the 5th argument. Also, why the triple quotation marks at either end of your field_mapping values, rather than just one? – Waterman May 24 '18 at 23:18
  • It can be in *any position because I used a keyword argument (field_mapping="field map value etc") not a positional argument ("field map value etc"). *Note that keyword args must come after any positional args you use though. Triple quotes are because I ran the tool manually and right-clicked the result object in the result window and selected "copy as python snippet" and ArcGIS wrapped it in triple quotes. Dunno why. – user2856 May 24 '18 at 23:22
  • Cool. Also, can you steer me toward a good reference page on how to build field mapping statements like yours? I'd like to know what each value in your example does. I looked on Esri's field mapping page but it doesn't show any examples that look like yours. – Waterman May 24 '18 at 23:25
  • I didn't build it, I copied it as a python snippet. Building one manually as a FieldMap object can be done, but is a bit fiddly - desktop.arcgis.com/en/arcmap/10.3/analyze/python/… – user2856 May 24 '18 at 23:27
  • OK, for future reference then... how do you "copy it as a python snippet"? Not expecting you to explain that step by step, but do you know where I can find a relatively good web page that does? – Waterman May 24 '18 at 23:32
0

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.

  • I'm not sure that the ' quote char is the problem. The original code with the provided CSV works fine for me (ArcGIS 10.3). And indeed the 7th entry in the CSV also has a single quote, and ArcGIS doesn't complain about that... – user2856 May 24 '18 at 1:54
  • It couldn't hurt (much) to test it to eliminate that possibility @Luke. I've encountered this problem in CSV files (and also extra commas but that's a different nightmare) and this was how I resolved it... but not in python, I did it manually with Notepad++ find and replace then manually field calculate in ArcMap to put the apostrophes back. Another choice is to replace ' with ` which still looks almost right but isn't a string terminating character. – Michael Stimson May 24 '18 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.