topic Re: load specific sheet from multiple excel files in QlikView

load specific sheet from multiple excel files

upaliwije — Wed, 26 Oct 2016 05:40:46 GMT

Hi Firends

I want to load a speceific excel sheet from several excel files I have written following code to load one file. Pls help me to modify the code load next file and thereafter till all files in the folder are loaded

CrossTable(CLASS, AMOUNT, 3)

LOAD * FROM

G:\PRD_PROFIT\AAEXP.xls

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

Re: load specific sheet from multiple excel files

sushil353 — Wed, 26 Oct 2016 05:53:31 GMT

you can use this subroutine:

sub ScanFolder(Root)

for each FileExtension in 'qvw','mp3','ape','flac','ogg'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

Load

'$(FoundFile)' as [FilenameWithPath],

'$(FileExtension)' as [Extension]

Autogenerate(1);

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('.') ;

Re: load specific sheet from multiple excel files

upaliwije — Wed, 26 Oct 2016 05:56:56 GMT

It is bit difficult for me to understand. If you can pls share an example QV file

Re: load specific sheet from multiple excel files

HirisH_V7 — Wed, 26 Oct 2016 07:34:19 GMT

Can you please mention ,How a file name should be like i.e ending parameters of each file should be like,

For example is it like,

AAEXP.XLS

AAEXP1.XLS

AAEXP2.XLS

AAEXP3.XLS

AAEXP4.XLS

etc,,;

Or any other format.

-Hirish

Re: load specific sheet from multiple excel files

upaliwije — Wed, 26 Oct 2016 08:01:09 GMT

AAEXP,GAEXP,KDEXP,KUEXP LIKE THAT

Re: load specific sheet from multiple excel files

HirisH_V7 — Wed, 26 Oct 2016 08:26:44 GMT

You can use,

*EXP For loading all the files ending with EXP.

CrossTable(CLASS, AMOUNT, 3)
LOAD * FROM
G:\PRD_PROFIT\*EXP.xls
(biff, embedded labels, table is HO$);

HTH,

Hirish

Re: load specific sheet from multiple excel files

upaliwije — Wed, 26 Oct 2016 11:45:33 GMT

Thanks