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

[#1]

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

    ? ? ? ?

[stevedark]

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

[#3]

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

[stevedark]

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

[#5]

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 ====;

[stevedark]

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

[Anonymous]

Hi Steve,

I hope you don't mind me crashing this blog.  To accumulate my historical data I have to store into a .qvw table correct?  I.e. I cant append to an existing qvd file right?

lc

[stevedark]

Hi Laura,

Of course I don't mind!

What I would suggest is that you take a read of the blog post that I put a link to in my initial comment.  This should make things clear.

You can append historical data from one QVD into a new QVD that has both old and new rows.  The way that you do this is to load both old and new data into a QVW file and then issue a STORE statement over the top of the existing QVD (or to a new one, if required).

It is a common practice to have separate 'QVD Generator' apps, that LOAD and STORE data and then DROP all tables - so that the app contains no data.  The QVW files with the charts then load purely from QVD files.

Hope that helps,

Steve