Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

Highlighted
Specialist II
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

Highlighted

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

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

See discussion below:

Loading multiple sheets

Regards,

Dick

Highlighted
Not applicable

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.

Highlighted
Partner
Partner

Hi Lyudmila,

Thanks for your help !

Have fun with QV,

François