convert multiple excel files into one qvd

[Anonymous]

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.

[Anonymous]

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.

[maxgro · Accepted solution]

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;

[vikasmahajan]

Hi,

Maxgro,

Thanks for sharing !!

Vikas

[danilopvh]

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......