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.

[LR]

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.

[JoeBorgione]

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

[TedKowal]

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 

[JoeBorgione]

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

[MatthewChandler]

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.

[ShanaBritt]

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

[DanPatterson_Retired]

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?

[ShanaBritt]

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.