Pick up ALL tabs in an Excel workbook and UNION ALL

Runner
#1

Question: What is the best way to pick up data from ALL tabs in an Excel workbook and then union all of the data together into a single table?

Background: We release an updated list every month. We have ONE Excel workbook that holds all of this information, with one tab corresponding to each month.

The columns are the same tab to tab (i.e. month to month).

For example, these are the tabs (and the data each holds) in a single workbook:
Tab 1 = Jan 2021
Product A, 500
Product B, 200
Product C, 1000

Tab 2 = Feb 2021
Product A, 500
Product C, 900

Tab 3 = March 2021
Product A, 1
Product B, 5
Product C, 900
Product D, 100

#2

Hey @BlueFlamingo! Thank you for the great question.

Mitto’s RegEx input doesn’t currently support multiple tabs from the same Excel workbook, but I will create a feature request for this.

In the meantime, you could save each tab of the Excel workbook as a CSV and use the RegEx input on those CSV files.

#3

Is there any update on this feature request? I have a use case where I need to injest an Excel workbook that has over 50 tabs that all need to be put into a table.

1 Like
#4

Hi Jamie,

In Runner 3.4.0 (current shipping version), we’ve added an Excel job configuration parameter (tab_regex) to support multiple tab selection in the input using RegEx.

You can use it like this:

{
  input: {
    source: Report.xls
    sheet: ""
    tab_regex: Sales|Orders|Returns

This option is provided in the Excel wizard and can be added to any existing Excel jobs.

#5

Thanks, Joe. Is there a way to pull in all the tabs without naming them?

#6

Hi!

Yes, reasonable question. We’re using a standard RegEx parser for the tab selection, so the RegEx for “match anything” as a tab name is .*

In this example configuration, I added a store_location column in the Zuar Bikes Excel file, and set portions of the file to each state. I moved those state rows into tabs named for the states.

The job input section looks like this:

{
  input: {
    source: zuar_bikes.2.xlsx
    sheet: ""
    tab_regex: .*
    start_row: 0
    start_column: 0
    encoding: ASCII
    includes_header: true
    noneify: true
    use: flatfile.iov2#ExcelInput
  }
1 Like