3 Replies Latest reply: Oct 13, 2015 7:40 AM by vikas mahajan RSS

    convert multiple excel files into one qvd

    surfman joe

      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
          Michael Solomovich

          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
            Massimo Grossi

            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;