Monarch - Import excel sheet with MANY tabs

Hi Melissa,

In Monarch classic you can only load one Excel tab.  However, if you use the Monarch complete (Monarch classic + Monarch Data Prep studio), you can load all 99 tabs in the Monarch Data Prep studio.

Regards

Mo

Hi Melissa.

Unfortunately, the only way to do this is to consolidate the data into one larger range.

For the purpose of this conversation, we are going to assume that you have the same columns in terms of names, datatypes and column position on each tab.

In Excel, each worksheet / tab is its own grid with its name per the bottom tab. A workbook is a collection of worksheets / tabs.

Each worksheet can contain many blocks of data. These can be assigned manual names using the named range function in Excel. Excel will also create its own default ranges that refer to the overall worksheet itself.

When you use Monarch Classic, as Mo said above, the wizard offers you  a list of ranges made up of the default ranges Excel creates (that typically refer to the main contiguous data range in each worksheet) as well as any user defined named ranges also.

Excel does allow you to create 3D ranges (Assigning Range Names Span Different Sheets - MS-Excel Tutorial) but these cannot be used for the purpose of extracting data. You can see this in the following example;

https://tinyurl.com/ybx98w7w

- This has several tabs. Each tab has a dedicated named range that corresponds to the tabs contiguous data range.

- If you click F5, bringing up the Goto panel, you will see 6 named ranges you can select that will jump to the corresponding tab when selected

- However, you will also see that if you select Formulas, Names Manager on the toolbar, there is a 7th hidden range called 'TestRange'

- On the tab 'Test' you can see how I can reference this in a formula but per previous, I cannot reference is for selection purposes

Similarly, if you open the spreadsheet in Monarch Classic, the range 'TestRange' is not visible as its not a single, contiguous object that can be selected. And you can only select one named range. The reason for this is there is no way of knowing that the ranges are actually 'compatible' and could logically be stitched together nor is there any stitching ability in Classic. Therefore you can only select one range or dataset to work on at a time.

So as far as classic is concerned, your only options are to recreate the spreadsheet such that you have one overall grid by either;

- having a single tab with an extra column to identify records from their original tabs (See tab Option01 in the example I reference above)

- Or potentially, if the tabs have a common maximum number of possible rows, create a final tab that uses formulas to consolidate the tabs (See tab Option02 in the example I reference above)

- Note, these options assume you will not have an issue with Excel worksheet overall row/column limits!

Alternatively, you could use Project command line capability to do an export append of each tab to a master table but this requires you have ability to create a batch file as well as the license to do so.

There are also some third party Excel addons that will help you to do this - but you will be looking at duplication of data as a result of the data being taken from location A, B, C, D, E F, and stored in Location G as a result which has a number of practical disadvantages accordingly.

With Monarch Complete however, and the Data Prep Studio engine we could create a  workspace that references each named range (Table01, Table02, Table03 . . . ., Table06) and then perform a union. So firstly, we can select multiple named ranges;

Below, you can see that we have all 6 ranges, loaded;

I can now perform a series of combine operations to link them all together, daisy chain fashion;

Resulting in a new dataset / object made up of all 6 tables appended together;

You can see this is reporting 960 rows, which is the combination of all rows in 6 named ranges (excluding the duplicated headers). I an then use this to perform an overall analysis using the merged data accordingly.

If I save the workspace, next time I open it, the referencing of the 6 tabs, its merging and all subsequent operations are all saved. Thus;

- if any of the ranges have changed in terms of number of records, the dataset will dynamically refresh to reflect the same

- and if I add another tab (Table07), I can easily edit the append to incorporate the new tab into the overall dataset and the new data will be reflected in the subsequent analysis / processing accordingly.

So, with some initial pain, I can easily perform the multi-range analysis with ease thereafter. The workspace can be shared with others also or the results exported to share with others in formats convenient for them (e.g. Excel).

Its worth noting also that this is not limited to the Excel row limit . . .if all the tabs have maximum rows populated, DataPrep will happily append the rows beyond Excels limit - it is not limited in this respect.

If you want to play with this, you can take advantage of the free trail for Monarch Complete here; https://www.datawatch.com/try-now/

I have uploaded the Workspace for you here;  https://tinyurl.com/y9uzlfjj

If you download both the Excel and Workspace example and place them in a folder C:\TEMP, you can use them in combination to see the above results.

Hope this helps.

Anwar

In prior versions, I used an Excel macro suite, ASAP Utilities, to combine multiple sheets into one.  They had to have the exact same structure, but it worked for me.  Google will find the website.  There is a 90 day trial or free for personal use.