ArcGIS Pro and Excel Table Problems

BrianKaplan · ‎12-01-2016

I'm having too many quirky things happening with the use of ArcGIS Pro 1.3. Lets take my recent problems with tables. If I use the geoprocessing tool to export a Feature Class table to Excel. Access cannot read it. I have to open it in Excel and then save it as an Excel file and then import it into Access.

But even worse, I have a file that has a field called InvestigationID in an Excel file. It is a unique ID. When I open up the Excel file in ArcGIS Pro, the entire column of InvestigationID is converted a single 10 digit number. The original IDs are 10 digit also. Not only does the table in Pro have this converted value, the original Excel table of unique IDs is converted to this single ID. This is horrible and I'm not going to spend my time trying to find out exactly where this went wrong. I was starting to like Pro but cannot trust it anymore. Time to return to ArcMap.

LanceShipman · ‎12-02-2016

Can you please post the Excel table? We would like to take a look to better understand your issue and improve the software.

BrianKaplan · ‎12-04-2016

Hi Lance, Thanks for you offer to look into this and improving the software is important to me also. The data has personal identifiable information and I'll have to cleanse the data somehow and be sure that I don't cleanse the problem. I'm pretty busy at the moment so it may be a week or two.

LanceShipman · ‎12-05-2016

I'll review your data when you post it.

BrianKaplan · ‎12-19-2016

Hi Lance,

Regarding the problem I'm having with Excel and ArcGIS Pro...

Here's a snapshot of some 10 digit numbers I created using the random feature of Excel. I converted the formula to a value, save the spreadsheet, added it to a map in ArcGIS Pro and opened the table in ArcGIS Pro as shown below. You can see it converted the numbers to a single value. I don't see where I can attach the Excel file. Please note that I'm using Excel Office 2013 32 bit.

Please also note that when I use ArcGIS Pro to export a table to Excel, Access cannot read it unless I open the spreadsheet in Excel and save it. Access can then open the saved spreadsheet. Someone mentioned that it could be because I'm using 32 bit Office.

Thanks for you help on this.

Brian

LanceShipman · ‎12-19-2016

Click on "Use advanced editor" (upper right above the Comment window). At the lower left of the comment window you will see "Attach". Please attach your spread sheet. Preferably a copy that has not been opened in Access and Saved. The number you are seeing is the max 4 byte integer. Why it's there, well we will have to take a look.

BrianKaplan · ‎12-19-2016

Hi Lance,

Here's the spreadsheet. Thanks for the tip on attaching. I created it from scratch using the random number generator using the max and min of the actual values. I have no idea if that is the cause but I needed a min and max with the formula. This spreadsheet wasn't touched by Access. I also tried this spreadsheet on another computer with the same version of ArcGIS Pro and Excel and got the same result. Thank you for your help.

LanceShipman · ‎12-19-2016

The max integer display was a solid clue. The MS Excel driver is interpreting the field as a Long. Since all of your values are greater than max integer, the field overflows, hence the 2147483647. At Pro 1.4 the column is seen as a double and displays correctly. As for the spreadsheet needing to be opened in Access and saved. I'll take a look tomorrow.

LanceShipman · ‎12-21-2016

I took a look at your Excel file. Open the properties, on the General tab select "Unblock". It opens with MS Excel without any problems.

BrianKaplan · ‎12-21-2016

Hi Lance, Thanks for identification of the problem with the Excel. Also thanks for your response about Excel but I'm not sure if we are on the same page.

When I export an Excel spreadsheet from ArcGIS Pro 1.3 using the table to Excel geoprocessing tool, Access will not open it. Excel will. Here's the screenshot of the error from Access when I try to import.

If I open and save it in Excel Access will then open it. I would have sent the exported Excel file but once again, I cannot find the Use advanced editor button.