Excel and ArcGIS Pro 2.0.1

[JoeBorgione]

At that agency where I work, several problems with adding or accessing an Excel Spreadsheet have been surfacing recently.  The first few seem to be related to Error: Failed to connect to database. General function failure Unexpected error from external databa... , but this latest one is in ArcGIS Pro 2.0.1.  Here is a snippet of the email I just received from the end user:

...Today I’m trying to add an Excel spreadsheet for geocoding in Pro and it’s not working properly. The data in the spreadsheet get jumbled around so that the columns and rows don’t make any sense, i.e. cause-of-death might be in the address field or the address might be in the occupation field, or one person’s address gets listed as another’s person’s address but in the age field. The data aren’t consistently moved around to the same place, it’s just a random mess. Even information I’ve deleted from my spreadsheet that I don’t include in my maps—names, DOBs—is showing up in Pro in random spots in the table.

It works if I add the data as a .csv file so that’s my workaround for the moment.

I am wondering if anyone else has experienced this issue as well.

[DanPatterson_Retired]

They just said ... adding excel .... to Pro...

They didn't indicate whether they were using the Excel to Table tool or not.

That is the first thing I would confirm.

The second thing to confirm is whether the Excel file is compliant with standards like only one data type in a field.  And on top of that, no blank rows or columns in the data range.  Better still, ensure all cells have a value even if they have to specify a no data value.  Finally, right-click on each field name, and specify the data type for the column explicitly so the conversion doesn't have to guess.

[JoeBorgione]

Dan-  she is specifically not using excel to table; this is a workflow that has been used successfully in the past where she adds the excel spreadsheet to an ArcGIS Pro project and does her thing.  Same spreadsheet format, same field names, same field definitions...

Dan_Patterson‌

[DanPatterson_Retired]

Yes I know... worked in the past... doesn't work now ...

• either the workflow no longer works because there is a bug
• or it works on other files but nowt this one (or ones recently tested
• or the conditions are now more stringent than they were and the new version is being less forgiving (which would be a good thing)

just narrowing down the possibilities

[JoeBorgione]

I'd like to look over the users shoulder, and/or take a look at the data (which isn't going to happen in this case, since it's health department private data).  We've been seeing an Excel related issue that has different manifestations recently.  Seems to span ArcGIS versions (10.3X to 10.5.1, and now Pro), and shows up regardless of Windows version (10, 7).  I've been working with the support team on it, but we can't nail down any discernible pattern so it's more of a PITA than something we can resolve....

Dan_Patterson‌

[DanPatterson_Retired]

I have noticed the same, but I try desparately not to use Excel to maintain data in any form.  Changes in Excel would also be manifested in how other 3rd party programs handle excel files as well.  The explicit formatting of columns is a first go on how to resolve data transfer issues.  If I get some time, I will try some of the excel readers/writers in python/numpy but what is really needed is an Excel reporting tool much like how some spreadsheets show how a delimited text file will be parsed prior to bringing it into the spreadsheet.

[JoeBorgione]

That's generous of you, but here is what I'm going to do:  Tomorrow (11/9/17) we have a county-wide gis users group meeting and this problem is going to be a talking point.  I'm going to suggest that people use Excel to Table and be done with it.  (I keep my use of excel limited to doing my taxes...)

Dan_Patterson‌

[DanPatterson_Retired]

I definitely agree with that proposal...

We will collectively hope that there isn't much pushback against direct use of excel files.  People usually are quite acceptable of any change in the Arc* suite of programs 

But maybe as an interim solution... a validation tool that shows what the file 'might' look like would be useful

[LR]

Whether Excel files work with Pro seemingly depends on the Excel version, OS version, system language, column formatting, random cell editing (even just clicking into a cell), the weather, your coworker's shirt color and at least ten other unknown factors.

So far I have enjoyed:

- data randomly getting jumbled up

- tabs not being recognized at all

- data not being recognized when there's a formula in the cell

- data not being recognized because the cell was changed (just a new value typed in)

- formats being interpreted wrongly

- first line not being treated as column name

- assorted fun with special characters

Workarounds, sorted by effectiveness:

- don't use Pro

- save as CSV

- save as xls

- paste values in a new sheet (not just a new tab!)

- upload sheet to MS or Google, then download again (for foreign language Excel files). Don't change any values later!

Generally I stick with "don't use Pro" and just do my processing in ArcMap. When I have to use Pro "because it looks prettier" I just bake the joins into a new layer and load that.

[DanPatterson_Retired]

Is this all happening with Excel to Table??? Because I don't see it as an option on your list.