topic Re: Add XY Point Data in ArcGIS Pro - Not with Excel 2016 in ArcGIS Pro Questions

Add XY Point Data in ArcGIS Pro - Not with Excel 2016

JoeFlannery — Tue, 26 Feb 2019 14:22:35 GMT

One step forward, one step back.

I so want to migrate to ArcGIS Pro, but little things like this keep me in ArcMap.

I have a table in a Microsoft Excel 2016 spreadsheet with attribute information plus latitude and longitude fields. I want to add the data to my ArcGIS Pro map and, optionally, create a new point feature class from the table.

In ArcMap 10.5.1, the Add XY Data dialog window gives you drop-downs for choosing the X and Y fields from the Excel 2016 spreadsheet (*.xlsx file format). Everything works as expected.

In the ArcGIS Pro XY Table to Point tool, there are no drop-downs for choosing the X and Y fields from the spreadsheet and the tool automatically creates a feature class in your default FGDB!

I manually type in my Lat/Lon field names, and it apparently does not like the spreadsheet field names I am using? Oh, no, that’s not the problem!

<< Excel Spreadsheet fields

If I add the Excel table to my ArcPro map’s TOC and open the table to view it, first off, I see that my field names are not being recognized. I see Field1, Field2, etc. with my field names in the next row down.

Secondly, I see that my Lat/Lon fields are being recognized as “Text” fields, even though I purposely made them Number fields in the Excel spreadsheet.

From Esri:

“ArcGIS Pro supports XLSX files (Excel 2007) and XLS files (Excel 2003 and earlier).”

Source: https://community.esri.com/groups/esri-training/blog/2017/05/26/working-with-excel-data-in-arcgis-pro

Export the Excel spreadsheet to a an earlier version of Excel or to a CSV file and everything works!!

CSV File in Pro

Now the XY Table to Point tool shows drop-downs for my table fields and it automatically recognized Longitude and Latitude as my coordinate fields.

I can use an Excel 2016 spreadsheet in ArcMap 10.5.1, but I can't use it in ArcGIS Pro 2.3. It is sad to me that this wildly popular workflow is not functional, yet, in ArcGIS Pro.

Re: Add XY Point Data in ArcGIS Pro - Not with Excel 2016

ThomasColson — Wed, 27 Feb 2019 20:14:48 GMT

I was able to run XY Table to Point as well as Make XY Event Layer with an Excel 2016 .xlsx file. There is something amiss with your particular table, perhaps some formatting, which has happened to me before. Suggest opening a case with tech support, as the functionality is definitely there.

Re: Add XY Point Data in ArcGIS Pro - Not with Excel 2016

DanPatterson_Retired — Wed, 27 Feb 2019 20:19:57 GMT

your fields are left justified for the long/lats, suggesting that although looking like numbers, they are actually text. So somewhere in those two columns you have text (a space, a blank cell, some test outside of the row range of the data) which is formatting the whole columns as text

Re: Add XY Point Data in ArcGIS Pro - Not with Excel 2016

JoeFlannery — Wed, 27 Feb 2019 22:05:33 GMT

Thank you Thomas and Dan.

I copied/pasted my data into a new sheet within the Excel 2016 workbook, pasting "values" only into the new sheet. I saved the workbook and tried again in ArcGIS Pro. This time the XY Table to Point tool worked as expected!

Thanks again for your time to respond to my posting.

Re: Add XY Point Data in ArcGIS Pro - Not with Excel 2016

KoryKramer — Thu, 28 Feb 2019 21:02:26 GMT

Joe - looks like maybe you were seeing [BUG-000107849: ArcGIS Pro does not use the first line from an Excel table as field names.] I'm glad you've figured it out for now. More discussion if you're interested over on https://community.esri.com/thread/180986 including some more technical explanation by Colin Zwicker.

Re: Add XY Point Data in ArcGIS Pro - Not with Excel 2016

JoeFlannery — Thu, 28 Feb 2019 21:36:54 GMT

Kory - thank you for the follow-up note. This is fantastic information regarding what I experienced!