topic Re: Load Multiple sheets from one Excel files in QlikView

Load Multiple sheets from one Excel files

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Load Multiple sheets from one Excel files

Chanty4u — Fri, 15 Apr 2016 17:37:43 GMT

chk dis

Load Multiple excel sheets using For loop

Re: Loading multiple sheets from excel

Re: Load Multiple sheets from one Excel files

bhavvibudagam — Sat, 16 Apr 2016 05:25:35 GMT

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.

Re: Load Multiple sheets from one Excel files

Sat, 16 Apr 2016 06:56:14 GMT

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;

Re: Load Multiple sheets from one Excel files

Sat, 16 Apr 2016 07:02:55 GMT

Hi Bhavvi

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

Re: Load Multiple sheets from one Excel files

jagan — Sat, 16 Apr 2016 07:21:47 GMT

Hi,

Is this Excel sheets are static or dynamic?

Regards,

Jagan.

Re: Load Multiple sheets from one Excel files

bhavvibudagam — Sat, 16 Apr 2016 08:58:28 GMT

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;