Part Duex - Unable to 'Display XY Data' and 'Make XY Event Layer' from csv file in ArcMap and ArcGIS Pro.

JayHodny · ‎10-02-2018

I am working with a csv file that has almost 7,400 records. I was having trouble creating an XY event layer after bringing the csv file into ArcMap. The x and y columns in the csv file were saved as numeric. I made sure the field names had no spaces, and I replaced bad coordinate pairs from an earlier geocoding step with blank cells in the worksheet. Every time I brought the file into ArcMap and attempted to plot the locations, the xy coordinate types were shown as text. I tried the same import in Pro, same result. I finally emailed the csv file to a co-worker (Outlook is the email client), and he was able to bring the csv into ArcMap and Pro, and create XY locations in each application. He renamed the csv file (he did not perform a Save As), and emailed the file back to me. I brought the file into Pro after setting up a basemap, and could create the xy feature class, and continue with my work. I have no clue what or why this happened, or how an email step seemed to correct the problem. Any thoughts?

JonathanFarmer_oldaccount · ‎10-02-2018

Hi Jay,

I'm not sure why you were seeing that behavior. You mentioned your co-worker renamed the file (presumably in file explorer since they did not do a save-as), what was the original name vs. the new name? Were there any spaces or special characters in the file name?

Jonathan

TimOrmsby · ‎10-02-2018

Not an answer but possibly a clue from the help topic Work with Microsoft Excel files in ArcGIS Pro under the Limitations heading:

Excel does not enforce field types for values during data entry like standard databases do. Therefore, the field type specified in Excel is not used in determining the field type exposed in ArcGIS. Instead, field type in ArcGIS is determined by a scan of the values in rows for that field. If the scan finds mixed data types in a single field, that field will be returned as a string field, and the values will be converted to strings.

I don't know if CSV files are treated the same as Excel files, but maybe the combination of numbers and blank cells in your CSV is being interpreted as a mix of data types. Not sure why it would be any different for your coworker, though.

JayHodny · ‎10-02-2018

Hi Jonathan and Tim,

Thank you both for the quick reply on such funky behavior, seen in ArcMap and Pro! My file name: NewarkBusinessLicense_nospacefieldnames_copy-paste.csv. Co-workers rename: NewarkBusinessLicense_nospacefieldnames_copy-pasteBJL.csv. He was able to open my file on his system and then renamed the file and emailed it back to me, at which time I could use it as planned. Partial screen shot of the first few fields.

TimOrmsby · ‎10-02-2018

Besides the file name difference, you mentioned that in your CSV you replaced bad coordinate pairs with blank cells. Your screenshot shows <Null> values, not blanks--at least for the Trade_name field. Did you send your co-worker a CSV with blank cells and get back a CSV with <Null> values? Not sure but still wondering if this is somehow relevant... blanks being interpreted as text and <Null> as numeric.

JonathanFarmer_oldaccount · ‎10-02-2018

Hi Jay,

Thanks for the response. It is certainly odd behavior. Both of those titles, while a little long, should work. Even if the title were an issue, it should have failed for your co-worker. I also don't see anything obvious in the x or y columns that would make ArcGIS think it is a mixed data type field (at least from the screen shot provided).

The only other thing I can think of is that maybe you were not including row 1 as headers and they were being treated as values in the column itself? Would match up with the mixed data type issue that Tim mentioned.

This may be one of those things we can't give a definite answer to without a reproducible case.

Jonathan

JayHodny · ‎10-08-2018

Hi guys, I replaced the <Null> values with "nothing," by highlighting the two coordinate pair cells in the Excel spreadsheet and hitting the delete button, repeat for all of the <Null> coordinate pairs. I think I got them all. The other columns having <Null> values are of type text. The returned and renamed CSV retained the blank cells in the x and y columns. I performed the same import steps and all worked. Strange!

DanPatterson_Retired · ‎10-08-2018

It doesn't like null coordinates, especially if you intend to make an event layer.

If you want to just add the data as a table, put in some number that you know isn't possible for the X and Y coordinates

ie -9999, -9999 You won't be able to make an event layer from the table until you remove and/or address the fact that you have a location without a valid coordinate.

JayHodny · ‎12-07-2018

Hi Dan and Tim,

My apologies for the delay in replying, I was pulled onto other projects. I am now back on this project and issue, and will apply your suggestions. I now have a better understanding of the XY event layer.

Sincerely,
Jay