Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading data from all the tabs of an excel

Hi ,

There is a folder which contains multiple excel files. Each excel file has data in varying number of tabs. e.g. Excel File A has data in 4 tabs, Excel file B has data in 12 tabs. The number of excel files present in that folder would also vary from time to time.

How do i load data from all these excels with varying number of tabs?

16 Replies
francoiscave
Partner - Creator III
Partner - Creator III

Hi Flipside,

To solve this issue, I've changed the variable :

Let vSheet = replace(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1),chr(39)&chr(39),chr(39));

But I have an issue just below when I load my Tab Name fields...

...

LOAD

  '$(vSheet)' as [Tab Name],

  *

From [$(vFile)] (ooxml, embedded labels, table is $(vSheet));

...

François

flipside
Partner - Specialist II
Partner - Specialist II

I was just about to add (because I found this out too) that you will need to wrap square brackets around the tab name because the quote character will throw an error, so using Anand's example ...

(ooxml, embedded labels, table is [ $(vSheet) ] );

flipside

its_anandrjs

Hi Francois,

You can try this expression also by create variable if the sheet names and where you get problem in the script.

SET vSheetNames = 'Sheet1', 'Sheet2','Sheet3','a','b','c';

for each vSheetName in $(vSheetNames)

Data:

LOAD *,

     '$(vSheetName)' AS SheetName,

     FileName() as FileName

FROM

[Mulitple Data.xlsx]

(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT

Regards

Anand

francoiscave
Partner - Creator III
Partner - Creator III

Anand,

I find your solution very good, but in French language, we have many words who contains simple apostrophe (like Chiffre d'affaires)...

Your solution doesn'st work in this case...

Do you have an (great) idea ?

I've attached some files if you want try something.

Thanks,

François

dickelsa
Creator
Creator

See discussion below:

Loading multiple sheets

Regards,

Dick

Not applicable
Author

If this not work:

Let vSheet = replace(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1),chr(39)&chr(39),chr(39));

Try this:

LET vSheet = PurgeChar(Peek('TABLE_NAME', i, 'Sheets'),chr(39)

Work Ok for me.

francoiscave
Partner - Creator III
Partner - Creator III

Hi Lyudmila,

Thanks for your help !

Have fun with QV,

François