topic Re: convert multiple excel files into one qvd in QlikView

convert multiple excel files into one qvd

Anonymous — Sun, 12 Apr 2015 16:02:21 GMT

I have multiple excel files that have exact same data structure. the file names follow up the following patter,

sales201501.xls

sales201502.xls

sales201503.xls

etc.

where the 01, 02, 03 are week number. A new excel file will be added weekly into same folder.

I want to convert those excel files into ONE qvd, incrementally add new data weekly after the qvd is created.

Can someone help on script? thanks.

Re: convert multiple excel files into one qvd

Anonymous — Sun, 12 Apr 2015 17:16:49 GMT

You can load the data and utilize filename() function:

Data:

LOAD
...,
filename() FileName
FROM sales*.xls ...;

From the FileName, you can cut the year and week part, and use function MakeWeekDate(YYYY, WW) do create the date. You can do it right there in the initial load if you wish.

And, store this into the QVD. Of course you have to concatenate the "Data" table to the existing QVD before storing.

As fior the incremantal load... I did something similar once using this way - after the files were read from the folder, I called a batch from the script that moved all the files to another folder. Maybe there are better ways.

Re: convert multiple excel files into one qvd

maxgro — Sun, 12 Apr 2015 17:49:45 GMT

DIRECTORY;

if QvdCreateTime('allexcel.qvd') > 0 then

// qvd exists

trace exists;

tmp:

LOAD MaxString(FileName) as MaxFileName from allexcel.qvd (qvd);

vMaxFileName = Peek('MaxFileName', 0, 'tmp');

trace vMaxFileName=$(vMaxFileName);

DROP Table tmp;

// get max file

tmpfiletoload:

load distinct FileName() as filename, '$(vMaxFileName)' as MaxFileName

FROM [20*.xlsx] (ooxml, embedded labels, table is Sheet1);

// get excel to load

filetoload:

NoConcatenate load * resident tmpfiletoload where left(filename,6) > left(MaxFileName,6);

drop table tmpfiletoload;

//load excel

FOR i=0 to NoOfRows('filetoload')-1

f=Peek('filename', $(i), 'filetoload');

trace f=$(f);

allexcel:

load FileName() as FileName, [Product ID], Product

FROM [$(f)] (ooxml, embedded labels, table is Sheet1);

NEXT;

// concat qvd and store

allexcel:

load * from allexcel.qvd (qvd);

STORE * from allexcel into allexcel.qvd (qvd);

ELSE

// qvd not exists, 1st load

trace not exists;

allexcel:

load

FileName() as FileName,

[Product ID], Product

FROM [20*.xlsx] (ooxml, embedded labels, table is Sheet1);

STORE * from allexcel into allexcel.qvd (qvd);

ENDIF;

Re: convert multiple excel files into one qvd

vikasmahajan — Tue, 13 Oct 2015 11:40:41 GMT

Hi,

Maxgro,

Thanks for sharing !!

Vikas

Re: convert multiple excel files into one qvd

danilopvh — Fri, 01 Jun 2018 19:45:16 GMT

Hi, how do I use preceding load inside this script... I'm trying, but is displaying synthetic key

allexcel:

Load

DISTANCIA_KM/HORAS_ULTIMA_TRANSAÇÃO as VELOCIDADE_MÉDIA;

Load

ROUND((MINUTOS_ULTIMA_TRANSACAO/60),0.1) AS HORAS_ULTIMA_TRANSAÇÃO,

SubField(IP_EMISSOR,',',1) as IP_EMISSOR_1,

SubField(IP_EMISSOR,',',2) as IP_EMISSOR_2,

SubField(IP_EMISSOR,',',3) as IP_EMISSOR_3,

SubField(IP_ULTIMA_TRANSACAO,',',1) as IP_RECEBIMENTO_1,

SubField(IP_ULTIMA_TRANSACAO,',',2) as IP_RECEBIMENTO_2,

SubField(IP_ULTIMA_TRANSACAO,',',3) as IP_RECEBIMENTO_3;

load FileName() as FileName,

TIPO_DOC,

UF_ORIGEM......