Where are my worksheets in my excel file? And what parameter data type are they?

1403
6
11-14-2018 03:34 PM
ChristopherHigham1
New Contributor II

I am trying to perform analysis on worksheets in a .xlsx file. However, I am unable to access the worksheet. In the ArcGIS Pro catalog, I see my Excel file with a drop-down arrow, but when I click on it, the worksheets do not appear. I am expecting about 16 sheets to drop down, with "$" in the name of each. I don't understand why this is happening.

I tried troubleshooting by copying all the data into a new excel file, with the same number of sheets, with no special formatting. This did not solve any problems, and I don't know what to do.

Also, a related question, what parameter data type is an excel worksheet within a file? I am creating a script tool which is supposed to take an excel file and a worksheet as 2 separate parameters. I know the excel file is a "file" type, but I don't know about the worksheet.

Any help would be greatly appreciated!

0 Kudos
6 Replies
DanPatterson_Retired
MVP Emeritus

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

catalog view or catalog pane? Are they present when you use the Add Data option?

0 Kudos
ChristopherHigham1
New Contributor II

Nothing shows up in the catalog pane. In the catalog window, I can double-click the excel file, but then I get the message that the "container is empty". When I try to access it through "Add Data", I get the same message.

Ironicaly, they show up when I try to access the same file and sheets in the catalog pane in ArcMap. But I am trying to work in ArcGIS Pro.

0 Kudos
DanPatterson_Retired
MVP Emeritus

It could be one of the other limitations listed in the link as 'best practices' that may be the issue with the spreadsheet.  Has it worked with other spreadsheets or just not this one

You might try 

Excel To Table—Conversion toolbox | ArcGIS Desktop 

to see if it translates properly in the first place, as a test.  other simple tests

The first row is taken as the field names for the output table.

DanPatterson_Retired
MVP Emeritus

As for the 2nd part of your question... 'worksheet' isn't a defined parameter for tools, you have to go with the 'file' to access the excel file

XanderBakker
Esri Esteemed Contributor

Actually, I think an Excel file would be a workspace since it contains "tables". Anyway, it is best to convert the Excel data into tables, since Excel allows a lot of things that are not compatible with database table principles. 

ChristopherHigham1
New Contributor II

Thank you both for your input! Part of the workings of my tool is to convert a worksheet into a table. It turns out the best parameter I can use for a worksheet input right now is string. I still cannot get my tool to yield a drop-down list of sheet options, in the same way as the Excel to Table tool. But as long as you type in the correct sheet correctly, it works as a string. I still used the excel file as a file type.

0 Kudos