Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

pls tell how to loaded 6 excel sheets in a script level at a time............

pls the process how to uplod 6 multiple excel sheets at a time i will write one script but it thrown a error............

let vsh=sheet6;

for each vsh in ('sheet1','sheet2','sheet3','sheet4','sheet5','sheet6');

LOAD

     s.no,

     [employ id],

     salary

FROM

C:\Users\ravi\Desktop\salary.xlsx

(ooxml, embedded labels, table is  $(vsh));

next vsh;

pls tell where mistake was occured in above script

6 Replies
danieloberbilli
Specialist II
Specialist II

Please have a look here: https://community.qlik.com/thread/161317

Maybe this helps

sagarkharpude
Creator III
Creator III

Please refer the following link

https://community.qlik.com/docs/DOC-7860

amit_saini
Master III
Master III

Kumar,

Hope this will help u:

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

Code to Access all sheets in a excel work book and convert to QVD

For a=1 to 4

Directory1:

LOAD employee

FROM

Looping\constructs1.xlsx

(ooxml, embedded labels, table is Sheet$(a));

//STORE Directory INTO C:\Users\chaitanyas\Desktop\Looping\Directory$(a).QVD;

Next

STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;

Drop Table Directory1;

Directory;

LOAD employee

FROM

Looping\Directory.QVD

(qvd);

Thanks,

AS

Ralf-Narfeldt
Employee
Employee

Just a simple fix, remove the parenthesis around your input list to the FOR, and it should work:

for each vsh in 'sheet1','sheet2','sheet3','sheet4','sheet5','sheet6';

Not applicable
Author

Dear Kumar,

Use Eachsheet option in script level,

FOR EACH vsheet IN 'Sheet1','Sheet2','Sheet3','Sheet14','Sheet5','Sheet6'


and load the file.

Rgds,

Priyantha.

prabhas277
Creator
Creator

Hi,

load this script and go to settings-> variable overview in that click on add that assigin  vsh='sheet6' then i will work

let vsh= sheet6 ; 

for each vsh in 'sheet1','sheet2','sheet3','sheet4','sheet5','sheet6' ;

LOAD s.no,

         [employ id],

         salary

FROM

C:\Users\ravi\Desktop\salary.xlsx

(ooxml, embedded labels, table is  $(vsh)); 

next vsh;