Load Multiple sheets from one Excel files

[bhavvibudagam]

Hi,

   can any please help me.I have added one Excel file and QVW FIle.Please find below.

In an Excel file i have different sheets as 100389,100462,700123,700156.

Now i want to concatenate the data in 100389 and 100462 by loading those 2 series files in single load statement like as 700 series files have load.

    Please help me to do this. And i am getting one error i.e "cant locate table in Biff file" .And the loop has to be repeated for only particular sheets dont consider the sheets in between 100389 to 100462.

Thanks,

[Chanty4u]

chk dis

Load Multiple excel sheets using For loop

Re: Loading multiple sheets from excel

[bhavvibudagam]

Hi chaitu,

      Already i have tried that Load Multiple excel sheets using For loop.

please find the above QVW file but my sheet numbers are like 100389 , 100462.I have to concatenate the data in these two sheets.

[#4]

Hi Bhavvi

Try like this

for each vpeek in '100389$','100478$','700891$','701842$';

T1:

LOAD [Call ID],

     Region,

     STATE,

     Format1,

     Format2,

     call1,

     call2,

     call11,

     call21

FROM

(biff, embedded labels, header is 1 lines, table is [$(vpeek)]);

NEXT vpeek;

[#5]

Hi Bhavvi

try to change in ur excel sheet from .xls to xlsx format.

[jagan]

Hi,

Is this Excel sheets are static or dynamic?

Regards,

Jagan.

[bhavvibudagam]

Hi jagan,

     Now i am able to load all the data when i modified the sheet names as sheet1,sheet2.......sheet19.

But original sheetnames are : 100366,100423,100879,100456,100756,....700369,700825,700196,701896,701832.

         Can you please help me how to mention these sheet numbers to load the data.

Thanks in Advance.

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 19;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

       Concatenate(Data)

LOAD [MARKET ID],

     [MARKET NAME],

     STATE,

     [SBB FORM]1,

     [MMB FORM],

     SBB,

     MMB,

     SBB1,

     MMB1

FROM

(ooxml, embedded labels, header is 2 lines, table is [sheet$(index)]);

NEXT

DROP field  Data.Field1;