Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

convert multiple excel files into one qvd

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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: convert multiple excel files into one qvd

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;

View solution in original post

4 Replies
Highlighted
Champion III
Champion III

Re: convert multiple excel files into one qvd

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.

Highlighted
MVP
MVP

Re: convert multiple excel files into one qvd

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;

View solution in original post

Highlighted
Champion
Champion

Re: convert multiple excel files into one qvd

Hi,

Maxgro,

Thanks for sharing !!

Vikas

Highlighted
Contributor II
Contributor II

Re: convert multiple excel files into one qvd

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

Error.PNG