Determine Number of Tabs in excel workbook

Greg_703914

Does anyone know a way to count the number of tabs in an excel workbook? The reason I ask is I have an automation that extracts data from a specified area of each tab when the tab exists. In all instances at least one tab will exist but I need to determine how many tabs are in a workbook so I can determine how many times to have the "Save a spreadsheet to a table" action to run for each consecutive tab/sheet in the excel workbook. I saw a stack overflow article here:https://stackoverflow.com/questions/46033700/how-to-get-the-count-of-number-of-sheets-available-in-excel-file that outlines how Javascript can help with this. Can someone maybe help apply some script in the "Field Formulas" action? or have another way to accomplish creating an excel workbook sheet count variable?

Furthermore, It would be preferred if there was a way to use the variable defined above to assist in looping the "Save a spreadsheet to a table" action based on the number of tabs that are in a workbook, instead of creating 20+ actions in the automation for potential instances when they are needed. Please let me know if any additional information is needed or my explanation is not clear.

Jozef_783863

Enhancement
It appears this functionality is not available, so I recommend submitting in Ideas.

https://community.pushbot.com/categories/ideas

Building
I searched the action list and I found three similar actions, but the first 2 will not work in this context.

1. CSV: Summarize rows with formulas
2. Files: Get file information
3. Files: Convert file to text

CSV: Summarize is a powerful action, but it does not fit because: 1) CSVs have no tabs, 2) it only works on cell data.

Files: Get file information does not return data exclusive to spreadsheets (like tabs).

Files: Convert file to text did return a large text field with text like "xl/worksheets/sheet1.xml". This pattern might work with Text: Find words that match a pattern. My first concern is the Excel file will be very large. With the new Excel release, the new Excel file limit should take care of this. My second concern with this last action is the text field will be very large. With your 20+ spreadsheet tabs, do you have a rough estimate of average column and row count for the file?

Jozef_783863

Thanks for posting the new Idea, @Greg_703914!

https://community.pushbot.com/discussion/804/

Suggested Approach:
I created a mock Excel file with: 25 tabs, 15 columns in each tab, 40 rows in 20 tabs, 350 rows in 5 tabs. Each cell value was "test value" so the cell is very small. This returned a large text field (908 KB) that slowed the Fields list when attempting to load. You will need to delete this large text field value after the actions execute (via a Field Formula).

With this file, I used the action sequence below to get a table with all the sheet names in one column.

1. Files: Convert file to text
   (Note: The help article states this only works with TXT files, but I am using it here since it seems to work.)
2. Text: Find text next to other text
   (Example action setup image is available below for how I found the sheet names)
3. Tables: Get unique rows
   (Each sheet name appeared twice, so I removed duplicates)

Result:

Greg_703914

The maximum number of columns each tab will have is 13 or through column M. The maximum number of rows will vary somewhere in between 40 to 334 on each tab. Most workbooks will be closer to 40 rows rather than 334 rows but in some rare cases you could see up to 334 rows. Do you think this will make the file too large if there are 20 tabs?

Greg_703914

@Jozef_783863 your solution worked for me. Thank you. When the file gets too big the process lets me know. Hopefully this message goes away with the new excel platform that is currently being tested.