Forcing ArcMap to Ignore Double Quotation Mark In CSV Text Field When Exporting to Shapefile?

Question

In ArcMap 10.2 I'm following this workflow:

1. Add .csv to map.
2. Right click .csv, --> "Display XY Data"
3. Right click XY Event Layer, --> "Data" --> "Export Data" --> save it as a shapefile.

There are 1232 rows in my .csv but the resulting shapefile has only the first 235 rows. The last row in my new shapefile (row 235) has one double quotation mark (") in the fifth (text) column.

This screen grab shows how the double quotation mark should appear in its cell.

This screen grab shows the result of bringing that row into a shapefile in ArcMap. Notice the quotation mark is absent but the lat, lon and subsequent field values appear in the same text cell, while the subsequent lat, lon, and so on fields are empty.

I found out that you can escape a double quotation mark by adding a second right after it, like this... (""), so I made this change manually in Excel. This was the solution. Next time I followed the above workflow I had all 1232 rows in my new shapefile.

How can I achieve this escaping of the double quotation mark in ArcMap rather than doing it manually in Excel?

A related side problem: Although setting the " manually to a "" in Excel allows all 1232 rows to be converted in ArcMap, the cell in question in the resulting shapefile has "" instead of where it should only have ". It look like this:

Answer 1

You need to double quote the double quote and enclose the entire value in double quotes.

Assuming your CSV looks something like the following when you open it directly in Notepad/text editor:

X,Y,ATTR
1,1,ABC DEF
1,2,BCD EFG
2,1,CDE" FGH
2,2,DEF GHI

If you open your CSV in Excel and save it again, Excel will automagically correct the quoting to:

X,Y,ATTR
1,1,ABC DEF
1,2,BCD EFG
2,1,"CDE"" FGH"
2,2,DEF GHI

And it will work in ArcMap:

Notes

• I did nothing to manually escape the original double quote other than opening the CSV in Excel and clicking the save button.
• Tested in ArcMap 10.3.1 and Microsoft Excel 2013.

Answer 2

You can use arcpy:

import arcpy,os
#Inputs
csv = "C:\Test\Textfile_with_quotes.txt"
Xfieldname = 'X'
Yfieldname = 'Y'
coordinatesystem_epsg = 3006

#Path and name for shapefile that will be created
path_for_shape = r'C:\Test'
name_for_shape = 'Shapefile.shp'

#Make an event layer of the csv to use as template for shapefile
arcpy.MakeXYEventLayer_management(table=csv, in_x_field=Xfieldname, in_y_field=Yfieldname, out_layer=r'tempevent', 
                                  spatial_reference=arcpy.SpatialReference(coordinatesystem_epsg))
arcpy.CopyFeatures_management(in_features=r'tempevent', out_feature_class=r'in_memory\tempfc')

#Create shape
arcpy.CreateFeatureclass_management(out_path=path_for_shape, out_name=name_for_shape, 
                                   geometry_type='POINT', template=r'in_memory\tempfc', spatial_reference=arcpy.SpatialReference(coordinatesystem_epsg))

#Read csv and write to shape
fields = [f.name for f in arcpy.ListFields(os.path.join(path_for_shape,name_for_shape)) if not f.name.upper().startswith(('SHA','OBJ','FI'))]
icur = arcpy.da.InsertCursor(os.path.join(path_for_shape,name_for_shape),fields+['SHAPE@X','SHAPE@Y'])
with open(csv) as f:
    header = next(f)
    for line in f:
        line = line.strip().split(',') #Remove newline charachters (\n) and split into list
        line+=[line[0],line[1]] #Add X and Y coordinate to end of list to use as input to SHAPE@X-Y tokens
        icur.insertRow(line)
del icur