ArcGIS Add XY data cannot specify x and y fields

Question

I have imported numerous CSV files into ArcGIS before using the "Add XY Data" dialog box. Sometimes I run into a problem where I can select the table, but then the drop-down menus to specify the X, Y, and Z fields are blank, where they would usually display a column header like "Longitude" (see picture below).

I'm running into this problem now. The CSV table has a header row, but nothing shows up in the Add XY data dialog box field specification, so I cannot add the data to ArcGIS. I've also tried importing the table and displaying as XY data, but the problem persists. This has happened in ArcMap versions 10.2 and 10.3.

Any ideas why Arc can't find XY fields? Could special characters in the table create this problem?

enter image description here

Here's a sample of the data:

Sample of data

• Michael Stimson: Yes, special characters can cause this problem. Can you provide the first few lines of the suspect CSV file to determine the likely cause of this problem.
• Evan: I added a picture of some of the data (total is over 7,000 rows). I don't think there's anything too crazy, some NAs, some quote and degree symbols. Could those do it?
• Get Spatial: The problem is likely due to a data typing problem. ArcGIS assigns data types by looking at some number of the first few rows. If it reads the lat and lon attributes and finds "NA" in them, it will assume they are text and not valid numeric fields to be used for x and y coordinates. Go through and replace those with nulls. Also, you will end up with only a few points displayed since you are missing one or both of the coordinates in many cases.
• Evan: Thanks @GetSpatial. Data typing makes sense; I just hoped it would take the raw data and read what it could. I'm experimenting with computer-read navigation off videos so I wanted to see how the results mapped, but I guess I'll have to edit the data first.

Answer 1

The problem is likely due to a data typing problem.

ArcGIS assigns data types by looking at some number of the first few rows. If it reads the lat and lon attributes and finds "NA" in them, it will assume they are text and not valid numeric fields to be used for x and y coordinates.

Go through and replace all of the 'NA' entries, with blank. This will set them to NoData, as opposed to an ''. Also, you will end up with only a few points displayed since you are missing one or both of the coordinates in many cases.

Here is Esri's help topic for reference: Adding x,y coordinate data as a layer

• Michael Stimson: Null doesn't exist for CSV or DBF files, it's best to import into Microsoft Access (JET) database if you have Access (or Open Office equivalent) or use Table to Table then filter out the rows that contain 'NA'.. they were never going to appear so why have them at all? If you have MS Access you can then change the field type from 'string' to 'double', if not add Lat_float and Lon_float and field calculate float(!lat!).. Strong typing of a database ensures that the data will convert; you've encountered this issue because of weak typing in your data format.
• Get Spatial: @MichaelMiles-Stimson - As I clarified in the answer, the purpose is to set them to NoData, as opposed to an empty string. Sometimes, it is not feasible to import to a database, depending on the source of these files. It might be preferable to keep the invalid points in the table for a couple reasons. 1. QA/QC process - to see what values are missing. 2. No need to create a derivative table when the original will simply ignore invalid records.