Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading multiple excel files~

Hello,

Just want to ask on what is the work around when loading multiple excel files. given the file names are months and years.

ex: Jan2012, Feb2012, Mar2012, Apr2012, so on...~

Thanks,

Donna

8 Replies
sreenivas
Creator III
Creator III

Not applicable
Author

Hi thanks, but i'm looking for load of multiple 'Files'~ what you have given me is multiple sheets. but thanks for the idea!

khadeer
Specialist
Specialist

Hi,

If u want the data for total months, just conncatenate all excell file, but remember all the fields are common in excell file r same.

Suppose in Jan excel file

Month, Region, Amount

1,XXX,100

1,YYY,200

In Feb

Month, Region, Amount

2,XXX,100

2,YYY,200

when u r concatenating these u get

Month, Region, Amount

1,XXX,100

1,YYY,200

2,XXX,100

2,YYY,200

when selecting months u will get the desired result based on ur selection.

Not applicable
Author

it will be much better if I can do it on a loop

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

See if the below code helps..

FileName:

LOAD * INLINE [

  

   File

   Jan2012

   Feb2012

   Mar2012

];

Data:

Load 1 as Dummy AutoGenerate 1;

For i = 0 to NoOfRows(FileName)

Let vFile = Peek('File',$(i),'FileName');

Concatenate (Data)

Load *

FROM

C:\$(vFile).xlsx

(ooxml, embedded labels, table is Sheet1);

Next

Drop Field Dummy;

Not applicable
Author

Hi,

If all the files having same structure then why you need loop to load the data.. loops will affect performance..

use like..

load

     fieldnames

from *.xls;

//Chandra

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

FileName:

LOAD * INLINE [

File

SBBO Metric 20 we 20121124

SBBO Metric 20 we 20121201

SBBO Metric 20 we 20121208

SBBO Metric 20 we 20121215

SBBO Metric 20 we 20121222

];

For i = 0 to NoOfRows(FileName)

Let vFile = Peek('File',$(i),'FileName');

Data:

Load *

FROM

D:\Nirmal Dev\QV Dev\Source\Sales Force\$(vFile).xlsx

(ooxml, embedded labels, table is Sheet1);

Next

Make sure to change the sheet name accordingly...

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

Change your FOR statement to this.

For i = 0 to NoOfRows('FileName')-1