topic Re: Mystery with Excel Files in ArcGIS Pro Questions

Mystery with Excel Files

JoeBorgione — Mon, 22 Apr 2019 22:34:10 GMT

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?

Re: Mystery with Excel Files

curtvprice — Tue, 23 Apr 2019 00:37:27 GMT

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.

Re: Mystery with Excel Files

DanPatterson_Retired — Tue, 23 Apr 2019 01:05:57 GMT

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

Re: Mystery with Excel Files

ShanaBritt — Fri, 26 Apr 2019 05:45:43 GMT

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

Re: Mystery with Excel Files

JoeBorgione — Fri, 26 Apr 2019 13:38:48 GMT

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...

Re: Mystery with Excel Files

TedKowal — Fri, 26 Apr 2019 14:21:14 GMT

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?

Re: Mystery with Excel Files

JoeBorgione — Fri, 26 Apr 2019 15:07:54 GMT

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.

Re: Mystery with Excel Files

curtvprice — Sat, 27 Apr 2019 13:55:33 GMT

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?

Re: Mystery with Excel Files

JoeBorgione — Mon, 29 Apr 2019 20:35:08 GMT

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!