topic Re: Excel and ArcGIS Pro 2.0.1 in ArcGIS Pro Questions

Excel and ArcGIS Pro 2.0.1

JoeBorgione — Wed, 08 Nov 2017 18:41:29 GMT

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 database driver , 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.

Re: Excel and ArcGIS Pro 2.0.1

DanPatterson_Retired — Wed, 08 Nov 2017 18:58:19 GMT

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.

Re: Excel and ArcGIS Pro 2.0.1

JoeBorgione — Wed, 08 Nov 2017 19:21:52 GMT

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‌

Re: Excel and ArcGIS Pro 2.0.1

DanPatterson_Retired — Wed, 08 Nov 2017 19:34:07 GMT

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

Re: Excel and ArcGIS Pro 2.0.1

JoeBorgione — Wed, 08 Nov 2017 21:16:30 GMT

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‌

Re: Excel and ArcGIS Pro 2.0.1

DanPatterson_Retired — Wed, 08 Nov 2017 21:57:22 GMT

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.

Re: Excel and ArcGIS Pro 2.0.1

JoeBorgione — Wed, 08 Nov 2017 22:04:02 GMT

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‌

Re: Excel and ArcGIS Pro 2.0.1

DanPatterson_Retired — Wed, 08 Nov 2017 22:10:01 GMT

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

Re: Excel and ArcGIS Pro 2.0.1

LR — Thu, 09 Nov 2017 15:09:24 GMT

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.

Re: Excel and ArcGIS Pro 2.0.1

DanPatterson_Retired — Thu, 09 Nov 2017 15:46:24 GMT

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

Re: Excel and ArcGIS Pro 2.0.1

LR — Tue, 14 Nov 2017 13:20:44 GMT

Mileage varies, so I don't bother with it anymore. The data usually needs to stay in Excel (coworkers, formulas, etc) and Excel exports CSV reliably, so it's the better option.

Re: Excel and ArcGIS Pro 2.0.1

JoeBorgione — Tue, 14 Nov 2017 14:49:18 GMT

Mileage varies, so I don't bother with it anymore.

After meeting with our local users group, that seems to be the prevailing opinion. One guy doesn't even deal with excel, but rather imports any xls or xlsx file into a google doc....

Re: Excel and ArcGIS Pro 2.0.1

TedKowal — Tue, 14 Nov 2017 16:14:45 GMT

I deal a lot with third party excel data through work. My technique is to bypass Arc Maps excel functions/tools alltogether. One of the main reasons, I am an advocate for the personal database in Pro (MS Access) because it is a main tool for my Excel to GIS transfers. I use an access database to do my imports and translations. I find MS Access works better with excel than Arc GIS. Some of my excel gotchas include:

1. Null vs Blank values

2. Data types -> this can be hell! in Excel the first 8 rows of a table determine the data types for the columns. Excel does not honor your format settings! The only way around this I found was making templates that had example data in the first 8 rows that meet the expected format requirements.

3. Field Order

4. Special Characters

Re: Excel and ArcGIS Pro 2.0.1

JoeBorgione — Tue, 14 Nov 2017 16:17:56 GMT

I've gone that route as well. Love Access and the personal geodatabase...

Re: Excel and ArcGIS Pro 2.0.1

MatthewChandler — Thu, 30 Nov 2017 20:56:54 GMT

I cannot tell if the issue is with Excel, Pro, or both. I imported an Excel spreadsheet to Pro (today) the same way I've been doing it for months and discovered that the field data types changed upon adding the spreadsheet. I'm not certain when 2.0.1 was installed on my machine, but I performed the same task on the 14th with no issues. The only thing I can think is that something changed with Excel in the past two weeks and Pro doesn't like it. ArcMap still performs the import as intended.

Re: Excel and ArcGIS Pro 2.0.1

ShanaBritt — Fri, 09 Feb 2018 02:39:35 GMT

The Excel spreadsheets *.xlsx work pretty well in ArcMap because you have the ability to use the Microsoft Office driver with ArcMap. However, ArcGIS Pro does not use the Microsoft Driver, it's using an open source driver that has limitations. Since ArcGIS Pro is a 64-bit application you would need the 64-bit driver to be able to use personal geodatabases and if you were trying to use ArcMap (32-bit) and Pro on the same machine, you can't have a mix of drivers. If you need something to track, http://support.esri.com/en/technical-article/000012103 .

Excel limitations Work with Microsoft Excel files in ArcGIS Pro—Excel | ArcGIS Desktop

-Shana

Re: Excel and ArcGIS Pro 2.0.1

DanPatterson_Retired — Fri, 09 Feb 2018 02:49:27 GMT

Better still is to convert excel to gdb table first... other recent threads are showing 'issues' with excel (ie adding fields etc).

Is esri using their own conversion software of open source software to do the conversion?

Re: Excel and ArcGIS Pro 2.0.1

ShanaBritt — Fri, 09 Feb 2018 20:49:09 GMT

Here are a few bullets from the link in my previous response

Excel file support in ArcGIS uses Geospatial Data Abstraction Library and its supporting OGR Vector Formats.
For tables that have all text column data types or have null values in the first row, column headers will be autogenerated (for example, Field1, Field2) when the table is opened.

I don't believe the same behavior occurs in Python because it's using a different driver to handle the Excel files.