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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
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;