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

[#1]

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

[alexandros17]

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)

[its_anandrjs]

Hi,

Use For loop for this otherwise use wizard.

HTH

Thanks & Regards

[arulsettu]

hi try this example....

LOAD [Customer ID],

   Customer,

   Address,

   City,

   Zip,

   Country

from

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

[amit_saini]

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

[Peter_Cammaert]

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