4

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.

enter image description here

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.

enter image description here

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:

enter image description here

  • 1
    Please edit your question and add some example data that demonstrates the issue (perhaps ~10 rows, i.e row 235 +- 5) formatted as code using the { } button so we can easily test. – user2856 Jul 17 '18 at 1:08
  • 2
    If all you want is a shapefile from the CSV try Find & Replace in Notepad (or other text editor you prefer), exchange " for ~ (or # or |, just something that doesn't normally occur in a line) then display XY data, save as shape then open the shapefile in ArcMap, start editing, open the attribute table and find & replace your ~ character with " and it should be all good. It's a tedious workaround but should be fairly quick to do once.. are you likely to be needing to do this many times? Do you want a python solution? – Michael Stimson Jul 17 '18 at 1:11
  • @MichaelStimson - I will be doing this relatively infrequently. I don't think a python solution is necessary as the manual replace method is fairly straightforward. It's just disappointing to realize Arc has no way to cope with a double quotation mark. – Waterman Jul 18 '18 at 23:05
  • What version of Excel? – user2856 Jul 19 '18 at 7:27
  • @Luke - I'm using Excel 10 (Microsoft Professional Plus), version 14.0.7208.5000 (32-bit) – Waterman Jul 27 '18 at 16:09
2

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:

enter image description here

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.
  • 2
    "You need to double quote the double quote and enclose the entire value in double quotes" is my favourite instruction ever ;) – Stephen Lead Jul 17 '18 at 3:21
  • I'm opening the csv in Excel and saving it but Excel is not 'automagically' formatting the double quote. So instead I've done it manually (a la "CDE"" FGH"). Once again Arc successfully converts all 1232 lines but the resulting value in the new shapefile is "CDE"" FGH" rather than CDE" FGH. – Waterman Jul 18 '18 at 23:01
  • @Luke - sorry, summer vacation got in the way. I'm using Excel via "Microsoft Office Professional Plus 2010". – Waterman Aug 21 '18 at 20:06
  • @PolyGeo - I'm stuck on this question. No one appears to have the solution yet. Do you have any advice on how to solve the problem, or how to solicit further advice from the community? – Waterman Sep 21 '18 at 1:33
  • @Waterman Your only option (to use the CSVs) as far as I can tell is to upgrade ArcGIS. Perhpas you could try saving as XLSX. You've already come across a different problem related to ArcGIS 10.2 that at least had a workaround, but I think you're going to keep coming across more ArcGIS 10.2 issues in future. – user2856 Sep 21 '18 at 1:39
0

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

enter image description here

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.