topic Re: ArcGIS Pro not recognising worksheets in a spreadsheet in ArcGIS Pro Questions

ArcGIS Pro not recognising worksheets in a spreadsheet

LachlanMilne1 — Mon, 19 Nov 2018 00:38:09 GMT

I am having a difficult time getting ArcGIS Pro to recognise multiple worksheets in an xlsx spreadsheet. For some reason catalog does not recognise all worksheets within a spreadsheet (see black below), whereas ArcMap has not difficulty (see white). When I try to use the 'excel to table' tool in Pro I get the error message, whereas the same tool works correctly in ArcMap.

Is there a way to force ArcGIS Pro to recognise all worksheets in a xlsx spreadsheet?

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

DanPatterson_Retired — Mon, 19 Nov 2018 01:13:38 GMT

beyond the generic recommendations here

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

Did you add the whole spreadsheet first? then try to select the sheet from the contents ?

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

LachlanMilne1 — Mon, 19 Nov 2018 02:46:50 GMT

Hi Dan - Thanks - No, but when I tried this approach only one worksheet was visible. I have also tried saving a copy of the spreadsheet - same issue arises. I am going to start with a new spreadsheet and try importing the data to it. This problem with worksheets not being visible in spreadsheets has happened with other spreadsheets, so it is not just this one. Also maybe there are merged fields or other issues with the spreadsheet I am not aware of that are preventing it showing up in ArcGIS Pro, but that must be unlikely because I could access the worksheet and proecess it in ArcMap with no problems.

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

DanPatterson_Retired — Mon, 19 Nov 2018 03:15:56 GMT

Could be something in the spreadsheet. Could be the location of the file.

Could be just about anything. If that spreadsheet doesn't work and others do, and others don't, you need to put the does and don'ts into some common category that is the root problem.

On that note, check out the help tips in the link I sent, which I am sure you are aware of.. but there may be a glimmer you may have missed (hidden this, split that, links anything, etcetera... that foul spreadsheets allow are generally not in arc*world)

Also tomorrow morning a 7:00 AM, I am releasing a blog post on Excel use and data issues when working with it and ArcGIS Pro

If you must work with spreadsheets, the rules are strict. Anything that allows for mixed data types has even more rules.

Arrays of a uniform data type are best… then it doesn't matter what goes into the rows/columns/cells (ie images)

Arrays of mixed dtypes have to keep the rows or the columns of one data type, then you can stack them row-wise or column-wise as long as you know what is where and can define it (ie tables in general, like dbase, arc*whatever tables, numpy arrays etc, most other science-used data formats)

Spreadsheets don't have such rules. You can put anything anywhere. Maybe that is why everyone uses them.

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

NaomiBegg2 — Thu, 04 Jul 2019 02:02:19 GMT

It looks like Pro 2.4 is broken when it comes to Excel.

I can't open Excel sheets unless they are in CSV. I can't use CSV as they remove the leading 0's which I need for a join later.

I can add the excel sheet in an older version of Pro but as soon as I try and open the project in the latest version the table breaks.

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

DanPatterson_Retired — Thu, 04 Jul 2019 02:19:36 GMT

Try

Excel To Table—Conversion toolbox | ArcGIS Desktop

instead of working with them directly since

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

has too many qualifications to guarantee success

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

KoryKramer — Thu, 04 Jul 2019 15:14:25 GMT

Do you get any warning or error messages when trying to work with Excel, and if so, what do they say?

Note that there were changes in 2.4 noted in https://pro.arcgis.com/en/pro-app/get-started/whats-new-in-arcgis-pro.htm

See: https://pro.arcgis.com/en/pro-app/help/data/excel/work-with-excel-in-arcgis-pro.htm

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

DionLiddell — Mon, 08 Jul 2019 04:18:01 GMT

Admin command prompt then AccessDatabaseEngine.exe /quiet sorted our issues - thanks!

Re: ArcGIS Pro not recognising worksheets in a spreadsheet

RichardFiore2 — Sun, 07 Jun 2020 22:17:09 GMT

Thought I had navigated most of the Excel Class V rapids until I tried to do some calculated fields referring to data in Excel sheets. Finally realized it was the "$" appended to all Excel worksheet names that threw errors... Excel to Table saved any more hair from being pulled out! Thank you!