Excel Tabs, Concatenate all Tabs

[alextomlins]

Hi Guys and Gals,

I have managed to avoid loading in any multi-tab Excel files until now. We have a spreadsheet with Change Requests but every month we create a new tab for the current month.

How do I Concatenate all the tabs data into one table automatically - even if they have added a new tab for the new month.

If this can't be done is there a way of taking the first tab or the tab with '*CURRENT*' in it?

It would be helpful to know all methods for my learning if you have the time but if not only to auto concatenate the sheets even if they are always adding them?

Thanks,

Alex

[rubenmarin]

Hi Alex, to load the first sheet you can omit the 'table is' clause, it gets the first sheet by default.

To retrieve all sheets you can try with an odbc connection to the excel to retrieve tables (sheets):

Re: need to load all the sheets in excel at time ?

[alextomlins]

It's Qlik sense and it's saying i can only connect using Lib Connect...

Very odd - All the sheets names are hand type and so are quite inconsistent - do you know of a way to do this without referencing the sheet name at all and just load all sheets together?

Never done this before so no idea !  

[rubenmarin]

Hi Alex, to connect to a file you need to create a connection to that folder, then you can use "Lib:..." instead of "C:\...", and removing the ',Table is sheetname' the first sheet will be readed.

This is an old video but the basis of connecting to an excel file are very similar.

I'm not sure how the ODBC pat can be translated to Sense.

Another option if you know the number of sheets:

Load Multiple excel sheets using For loop