topic Re: Loading multiple excel files~ in QlikView

Loading multiple excel files~

Mon, 11 Feb 2013 06:24:36 GMT

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

Re: Loading multiple excel files~

sreenivas — Mon, 11 Feb 2013 06:28:44 GMT

Refer this post

http://community.qlik.com/message/296647#296647

Re: Loading multiple excel files~

Mon, 11 Feb 2013 06:35:32 GMT

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

Re: Loading multiple excel files~

khadeer — Mon, 11 Feb 2013 06:35:38 GMT

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.

Re: Loading multiple excel files~

Mon, 11 Feb 2013 06:43:13 GMT

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

Re: Loading multiple excel files~

phaneendra_kunc — Mon, 11 Feb 2013 06:44:06 GMT

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

Drop Field Dummy;

Re: Loading multiple excel files~

Mon, 11 Feb 2013 07:39:07 GMT

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

Re: Loading multiple excel files~

phaneendra_kunc — Wed, 20 Feb 2013 15:25:46 GMT

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

Make sure to change the sheet name accordingly...

Re: Loading multiple excel files~

phaneendra_kunc — Thu, 21 Feb 2013 23:26:30 GMT

Change your FOR statement to this.

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