Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Loading multiple excel sheets containing different data in one go using for loop

I have an excel workbook with 20 worksheets. Each worksheet has a different name (i.e. Product,customer,sales, etc.) All the worksheets are in the different format and contain the different fields. How do I create a loop to load all worksheets in the excel workbook in Qlikview?


I don't want to load sheet one by one manually.

Tags (1)
3 Replies
Highlighted
Contributor
Contributor

Re: Loading multiple excel sheets containing different data in one go using for loop

Please someone help, looking for an answer for long, but i am getting solution for the scenarion when the sheet have same data format

Highlighted

Re: Loading multiple excel sheets containing different data in one go using for loop

Hi Atiya,

The below script consolidate all excel files and sheets into singe table. If you have any issues with the below code, I would suggest you to attach sample excel files and post expected output. Good luck.

Data:

Load '' as DummyField AutoGenerate 0;

FOR EACH file in FileList('C:\Users\Tamil\Desktop\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

Temp:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('Temp')-1

LET vSheetName = PurgeChar(Peek('TABLE_NAME', i, 'Temp'), chr(36));

LET vSheetName = Replace(vSheetName,chr(39) & chr(39),chr(39));

Concatenate (Data)

Table:

Load * ,

FileBaseName()   as File,

FileName()       as File_Name,

'$(vSheetName)'  as Sheet_name

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

NEXT i

Drop table Temp;

Next

DROP Field DummyField;

Highlighted
Master III
Master III

Re: Loading multiple excel sheets containing different data in one go using for loop

Hi Atiya,

See below:

Code to Access all files in directory (subdirectories)

SUB DoDir (Root)

FOR each File in filelist(Root& '\*.xls')

Tab1:

LOAD <<Field Names>>

FROM

[$(File)]

(biff, embedded labels, table is <<Table name>>$);

NEXT File

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('Your Directory');

Code to Access All sheets in a Excel work book

Directory;

for a=1 to 3

LOAD employee

FROM

Looping\constructs1.xlsx // this is the excel sheet name

(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a

Next

Thanks,

AS