topic Re: Loop for Excel in QlikView

Loop for Excel

mario-sarkis — Wed, 27 Jan 2016 16:00:18 GMT

Hey ALL,

hope can anyone help :

i have many excel files here is ana example of want

first excel file is "file2015' and the seconde is "file2016" each has many sheets

i need to read from file2015 the sheet1 and concatenate it with sheet1 from "file2016"

maybe you can help by giving me a loop that help to do this task,

Thank you,

Re: Loop for Excel

p_verkooijen — Wed, 27 Jan 2016 16:03:58 GMT

//create a empty table to concatenate to

ExcelData:

LOAD * INLINE [

Fieldname];

Then use FROM file*.xls....

Concatenate (ExcelData)

LOAD *

FROM file*.xls ......

Or use loop

For each File in FileList ('file*.xls')

Concatenate (ExcelData)

LOAD *

FROM file*.xls ......

Next File

Re: Loop for Excel

Wed, 27 Jan 2016 16:12:01 GMT

Hi Mario,

If i understand what you look for, you want to make a loop to concatenate two sheets from two files.

Try with this script:

for each File in FileList ('*.xls')

DataBase:

load *,

from [$(File)]

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

next;

If you want to concatenate sheets, you have to proceed like this:

For each vSheet in 'Sheet1$', 'Sheet2$','Sheet3$' etc...

DataBase:

LOAD

FROM

[Excel.xls]

(biff, embedded labels, table is [$(vSheet)]);

next;

But if I was known exactly what you need to do I could give you the exact solution.

Best regards

Re: Loop for Excel

HirisH_V7 — Wed, 27 Jan 2016 16:46:41 GMT

Hi,

Check this,

Dynamically Loading Multiple Excel Files

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Load Multiple excel sheets using For loop

HTH,

Hirish