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.

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

• user2856: 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.
• Michael Stimson: 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?
• Waterman: @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.
• user2856: What version of Excel?
• Waterman: @Luke - I'm using Excel 10 (Microsoft Professional Plus), version 14.0.7208.5000 (32-bit)

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:

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.

• Stephen Lead: "You need to double quote the double quote and enclose the entire value in double quotes" is my favourite instruction ever ;)
• Waterman: 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: @Luke - sorry, summer vacation got in the way. I'm using Excel via "Microsoft Office Professional Plus 2010".
• Waterman: @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?
• user2856: @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.