7 Replies Latest reply: Jul 5, 2016 7:09 AM by Steve Dark RSS

    Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD

      Dear QlikCommunity,

       

      Please help.  I'm stuck after Step 1.  I'm sure there's even an easier way to do all this in less "steps".  Basically, I'm trying to cut down the number of files and load time. 

       

      The below is basically what I want to accomplish:

       

      Daily .csv files located in one folder  ->  1) stored as .qvd files in another folder   ->   2) consolidated into monthly .qvd files based on Mth_Yr field  ->  3) consolidated into master .qvd file

      // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
      // / / / / / /      Load each csv and store into a qvd in QVDs folder only if not already done     / / / / / / / //
      // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //

      1a)   E:\share\QlikView\Common Reference\Data\Compliant Events      

                 CompliantUnix-Interactive_02-19-2015-04-55-00.csv

                 Compliant-Unix-Interactive_02-20-2015-04-55-00.csv

                 Daily-Wintel-Interactive_02-26-2015-15-02-00.csv

                 Daily-Wintel-Interactive_03-25-2015-15-02-00.csv

                 Wintel-RemoteInteractive_03-04-2015-15-02-20.csv

      1b)   E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs   

                 CompliantUnix-Interactive_02-19-2015-04-55-00.qvd

                 Compliant-Unix-Interactive_02-20-2015-04-55-00.qvd

                 Daily-Wintel-Interactive_02-26-2015-15-02-00.qvd

                 Daily-Wintel-Interactive_03-25-2015-15-02-00.qvd

                 Wintel-RemoteInteractive_03-04-2015-15-02-20.qvd

       

      1) Code.....

      DIRECTORY E:\share\QlikView\Common Reference\Data\Compliant Events;

      FOR Each vFile in FileList('*.csv')
        LET vQVD = Replace(Replace(vFile, '.csv', '.qvd'), 'Compliant Events','Compliant Events\QVDs');

        IF Alt(FileSize('$(vQVD)'), 0) = 0 then
          NextFile:
          LOAD
             1
      as Row_Counter,
              DATA_SRC_ID as Event_ID,
              SESSION_ID as Session_ID,
              Date(MonthStart(SESSION_END_TIME),'MMM-YYYY') as Mth_Yr,
              Timestamp(Timestamp#(SESSION_END_TIME,'DD-MMM-YYYY hh:mm:ss'),'MMM DD YYYY hh:mm') as Event_End_Time,
              Timestamp#(SESSION_START_TIME,'DD-MMM-YYYY hh:mm:ss') as Created_Timestamp,

              USER_ID as User_ID,

              FileName() as Source_File
          FROM
             [$(vFile)]
             (
      txt, codepage is 1252, embedded labels, delimiter is ',', msq);

          STORE NextFile into '$(vQVD)' (qvd);
          DROP Table NextFile;

        END If

      NEXT vFile;

      // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
      // / / / / / / / /      Load each qvd into appropriate month qvd  only if not already done        / / / / / / / / / / //
      // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //

      1b)   E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs   

                 CompliantUnix-Interactive_02-19-2015-04-55-00.qvd

                 Compliant-Unix-Interactive_02-20-2015-04-55-00.qvd

                 Daily-Wintel-Interactive_02-26-2015-15-02-00.qvd

                 Daily-Wintel-Interactive_03-25-2015-15-02-00.qvd

                 Wintel-RemoteInteractive_03-04-2015-15-02-20.qvd

      2)  E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs

                 Compliant Events Feb 2015.qvd

                 Compliant Events Mar 2015.qvd

       

      2) Code.....

          ? ? ? ?

       

       

      // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
      // / / / / / / / / / / / / / / / / / /        Load each monthly qvd into a master qvd         / / / / / / / / / / / / / / / / / / / //
      // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //

      2)  E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs

                 Compliant Events Feb 2015.qvd

                 Compliant Events Mar 2015.qvd

       

      3)  E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs

                 Compliant Events.qvd

       

      3) Code.....

          ? ? ? ?

        • Re: Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD
          Steve Dark

          Hi Mindy,

           

          You may want to take a look at this blog post on picking up a folder full of CSV files:

          http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

           

          To create the monthly QVDs I would assume you only want to do this for the current month (as there is no point recreating old months).  There are also only a finite number of file types, so you can loop these.  So you could end up with something like:

           

          // Set the current period to variables

          let vThisYear = Year(today());

          let vThisMonth = right('0' & Month(today()), 2);

           

          // Load the types of file we are dealing with

          Temp_FileMasks:

          LOAD FileMask INLINE [

          FileMask

          Compliant-Unix-Interactive_

          Daily-Wintel-Interactive_

          Wintel-RemoteInteractive_

          ];


          // Loop through these file types

          for iFileType = 0 to NoOfRows('Temp_FileMasks') - 1


               // Grab the next mask to deal with

               let vFileMask = peek('Temp_FileMasks', iFileType, 'Temp_FileMasks');


               // Load all data for the current month to refresh the monthly file

               TempMonthsData:

               LOAD

                    *

               FROM $(vFileMask)$(vThisMonth)-*-$(vThisYear)-*.qvd (qvd);


               // Write this file away and clear the data down

               STORE TempMonthsData INTO $(vFileMask)-Monthly-$(vThisYear)-$(vThisMonth).qvd (qvd);


               DROP TABLE TempMonthsData;


               // Create a master QVD from all monthly files

               MasterQVD:

               LOAD

                    *

               FROM $(vFileMask)-Monthly-*.qvd (qvd);


               STORE MasterQVD INTO $(vFIleMask)-MasterFile.qvd (qvd);

          next



          This is untested code, but hopefully it will point you in the right direction for achieving what you are after.  The only extra thing you may have to consider is building for the previous month as well as the current (if files come in at the end or after the end of the month).


          Hope it helps.


          Steve


            • Re: Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD

              Thanks for the code Steve.  I'll have to take time to learn and understand it all. This will probably work going forward on a monthly basis as you have the code written but right now I have to do this for a thousand files dating back to December of last year.  And, of course, not all the file names are consistent...yet.  That is why I am hoping to use an internal date field to get the bulk loaded.

               

              Example of names:
              NonCompliant CSVs folder:

              Golden Host-Daily Report(DR)-Wintel-RemoteInteractive_04-02-2015

              Golden Host-Daily Report(DR)-Wintel-RemoteInteractive_04-04-2015-15-02-20

              Golden Host-Daily Report(DR)-Unix-Interactive_04-13-2015R

              Golden Host-Daily Report(DR)-Unix-Interactive_04-13thru20-2015

              Bypass_Report(DR)-Unix-Interactive_12-01-2014-12-07-2014-pt1

              Bypass_Report(DR)-Unix-Interactive_12-08-2014-12-10-2014

               

              Compliant CSVs folder:

              TAMCompliant_April-2015_Full

              TAMCompliant_March2015 - new DTG format

              TAMCompliant02_May-2015

              TAMCompliant25-May-2015_00_00

              TAMCompliant25-May-2015_12_00

                • Re: Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD
                  Steve Dark

                  Hi Mindy,

                   

                  It's going to be fiddly getting everything straightened out there, where you have date ranges and suffixes.

                   

                  The routine you have to turn CSVs to QVDs will help you out.

                   

                  Going back through prior months will be possible by creating a loop that goes back through prior months.  The loop would look something like this:

                   

                  let vMonthsBack = 8;

                   

                  for iMonth = 0 to vMonthsBack

                       let vThisMonth = Date(AddMonths(today(), -iMonth), 'MM-YYYY');

                   

                       // Create your merged files in here.

                  next

                   

                  Do the files you are processing have date fields in the files as well?  How many different file formats do you have, or are all file formats the same just with different file names?

                   

                  - Steve

                    • Re: Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD

                      Hi Steve,

                       

                      For the first part I actually found some really nice code.   Yet, when I tried to move forward with what you provided me, I got the "...file not found..." message.  Then I realized that it dealt with the date fields in the filename.  What I need to do is replace the texts with the dates within the file names?

                       

                      How do I go about replacing the text May-2015 to the date May-2015?

                      TAMCompliant01-May-2015_12_00.qvd

                       

                      To get the dates:

                      Date(Date#(SubField('$(vBaseName)','-',2),'MMM'),'MMM') as FileMth,
                      Date(Date#(Left(SubField('$(vBaseName)','-',3),4),'YYYY'),'YYYY') as FileYr;

                       

                       

                      Step 1) ... good to go

                      TRACE ==== Auto load source .csv files into .qvd files with same name in different folder ====;

                      LET vSourcePath = 'E:\share\TAM\QlikView\Common Reference\Data\Compliant Events\';

                      FOR Each vFile in FileList ('$(vSourcePath)*.csv')

                       

                          LET vBaseName = Mid(SubField('$(vFile)','\',-1), 1, Index(SubField('$(vFile)','\',-1),'.',-1)-1);
                          IF IsNull(FileTime('$(vSourcePath)\QVDs\$(vBaseName).qvd')) then

                               tmpTable:
                               LOAD

                                    *,
                                    1
                      as Row_Counter,
                                    'Compliant'
                      as Compliancy,
                                    FileName() as Source_File

                               From

                                    [$(vSourcePath)\$(vBaseName).csv]
                                   (
                      txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                               STORE tmpTable into [$(vSourcePath)\QVDs\$(vBaseName).qvd] (qvd);
                               DROP Table tmpTable;

                          END If


                      NEXT


                      LET vBaseName=;
                      LET vFile=;
                      LET vSourcePath=;

                      TRACE ==== End .csv to .qvd autoload ====;

                        • Re: Incremental Loading of Multiple CSV Files to QVDs to Mthly QVDs to Master QVD
                          Steve Dark

                          Hi Mindy,

                           

                          I'm not sure I follow your question quite.

                           

                          What you want to do as a next step is to loop for each month, and merge all QVDs for that month into a single one.  Is that correct?

                           

                          For your folder of Compliant QVDs (as they will be out of the back of the first routine) you could do something like:

                           

                          let vMonthsBack = 8;

                           

                          for iMonth = 0 to vMonthsBack

                               let vThisMonth = Month(AddMonths(today(), -iMonth));

                               let vThisYear = Year(AddMonths(today(), -iMonth));

                           

                               for each vFile in FileList('$(vSourcePath)\QVDs\TAM*$(vThisMonth)*$(vThisYear)*.qvd')

                                    MonthlyFile:

                                    LOAD

                                         *

                                    FROM [$(vFile)] (qvd);

                               next

                           

                               STORE MonthlyFile INTO [$(vSourcePath)\QVDs\Monthly\TAMCompliant_Monthly_$(vThisMonth)$(vThisYear).qvd] (qvd);

                           

                               DROP TABLE MonthlyFIle;

                          next

                           

                           

                          This will only work for some of your files though, as for others you have numeric months, so would need to provide a different file mask

                           

                          Also, I'm not sure if having the * between the Month and Year in the FileList will work where you have May2015 in one of the filenames without a hyphen.

                           

                          Hope that helps move you a step forward though.

                           

                          Steve