Mystery with Excel Files

[JoeBorgione]

This has been an on-going problem for one of our county departments for quite sometime. They get most of their address data in the form of an Excel (.xlsx) and need to geocode that data.  Currently they are using Pro 2.3.1 but this has been a problem in earlier versions as well.

When they try to add an Excel file as a table, they can't:

The one I've highlighted is one I created for them and can add to a Pro session just fine. Here is what it looks like if they use the add data window:

If they copy the data to the local C: drive, they are good to go. These screen shots came from the user having the problem, and I notice she uses unc shares.  If I use the unc share instead of a mapped letter drive I can add all the excel spreadsheets I want.  To me it seems like a network access issue:  Is there something I can ask their Network admin guy to take a look it?

[curtvprice]

Could it be the use of an apostrophe in the path? I still recommend always starting folder and file names with a letter and only using a-z 0-9. And underscore. No spaces either. 

[DanPatterson_Retired]

This still applies

/blogs/dan_patterson/2016/08/14/filenames-and-file-paths-in-python 

and it gets worse in the age of unicode

test a path without 'raw' encoding to see if it will fail

[ShanaBritt]

Definitely make sure the spreadsheet name, worksheet name and field names do not contain spaces, special characters other than and underscore and do not start with a leading number. If possible, you may also want to try saving the xlsx to the older xls format and try using it in Pro. There are some limitations with using Excel spreadsheets in ArcGIS Pro that are described in the help system,

https://pro.arcgis.com/en/pro-app/help/data/excel/work-with-excel-in-arcgis-pro.htm

[JoeBorgione]

Shana Britt‌....

I went so far as to provide the user with a spreadsheet I put together myself: simple name, one column with a one-word name.  Works for me on a unc shared drive, mapped lettered drive, and local c drive.  It works for the user on a local c drive, but when it's on their 'secure' drive, it fails like the others.  Seems weird that the ArcGIS Pro client would not be able to work with this one drive, although I'm not exactly sure what 'secure' means in this case.  We're supposed to meet with the network security guys soon...

[curtvprice · Accepted solution]

I went so far as to provide the user with a spreadsheet I put together myself: simple name, one column with a one-word name.

The issue that caught my eye is not the excel file name, the apostrophe in the path with a folder name ("Annie's Folder"). If you create a folder "Joe's folder" does it work?

[TedKowal]

I don't know if this means anything to Pro but I can and access .xlsx spreadsheets from both unc paths and mapped network drives with no issues in ArcMap nor the Environment.  This may be a pro thing? or could it be a security thing in your network?

[JoeBorgione]

I'm leaning towards a security issue on their network.  Like you, it works for me just fine regardless of where the actual files are stored or how I access them.

[JoeBorgione]

Curtis Price‌:  I got back to the user, and sure enough, when she drops the apostrophe and makes the directory AnniesFolder (one word)  all is well.  Great catch!