topic Re: If we have multiple sheets in excel , what is command to select specified sheet in scripts in QlikView

If we have multiple sheets in excel , what is command to select specified sheet in scripts

Wed, 26 Feb 2014 07:35:32 GMT

If we have multiple sheets in excel , what is command to select specified sheet in scripts

Re: If we have multiple sheets in excel , what is command to select specified sheet in scripts

alexandros17 — Wed, 26 Feb 2014 07:40:10 GMT

use the wizard, it will guide you even with the selection of the sheet (is a drop down that list all the sheets in your xls)

Re: If we have multiple sheets in excel , what is command to select specified sheet in scripts

its_anandrjs — Wed, 26 Feb 2014 08:11:49 GMT

Hi,

Use For loop for this otherwise use wizard.

HTH

Thanks & Regards

Re: If we have multiple sheets in excel , what is command to select specified sheet in scripts

arulsettu — Wed, 26 Feb 2014 08:17:00 GMT

hi try this example....

LOAD [Customer ID],

Customer,

Address,

City,

Zip,

Country

from

(biff, embedded labels, table is CUSTOMER$);

Re: If we have multiple sheets in excel , what is command to select specified sheet in scripts

amit_saini — Wed, 26 Feb 2014 08:18:09 GMT

Hi Ravi,

Hope these codes will help you:

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);

Re: If we have multiple sheets in excel , what is command to select specified sheet in scripts

Peter_Cammaert — Wed, 26 Feb 2014 08:25:56 GMT

It's not a command. You select the target sheet by specifying the "table is" option in your LOAD statement. For example:

LOAD *
FROM Excelworkbook.xlsx (ooxml, embedded labels, table is Data1);

causes the script engine to load all columns from sheet Data1. If you want to load multiple similar sheets in one stroke, you'll have to replace Data1 with each successive sheet name. Or use a FOR loop like Anand suggested. For example:

FOR EACH vSheet in 'Data1', 'Data2', 'Data3'
ExcelData:
LOAD *
FROM Excelworkbook.xlsx (ooxml, embedded labels, table is $(vSheet));
NEXT

Best,

Peter