5 Replies Latest reply: Jan 15, 2014 8:24 AM by Jani Uusihannu RSS

    Incremental Load of New Files Only

      Hi everyone,

       

      I have an issue where my reload time is ever increasing as I receive a new file daily for update. Any tips on how to work around this with incremental load?

       

      Copy of load script:

       

      DailyFile:

      LOAD

           EXTRACT_DATE AS Date,

           PRESENTED_COUNT,

           VIEW_COUNT,

           ACCEPTED_COUNT,

           NOT_ACCEPTED_COUNT,

           SUPRESSED_COUNT

      FROM

      [PathName\FileName - AU_2013*.csv]

      (txt, unicode, embedded labels, delimiter is ',', msq);

       

      There is a date in each file name (format = AU_2013_12_13), but I cut it off with the wildcard so i can load all files in the subfolder at once.

       

      Any guidance would be greatly appreciated.

       

      David.

        • Re: Incremental Load of New Files Only
          Agnivesh Kumar

          Use where condition in your laod statement using timestamp .

          • Re: Incremental Load of New Files Only
            Vishwaranjan Kumar

            hi

            try this

            DailyFile:

            LOAD

                 EXTRACT_DATE AS Date,

                 PRESENTED_COUNT,

                 VIEW_COUNT,

                 ACCEPTED_COUNT,

                 NOT_ACCEPTED_COUNT,

                 SUPRESSED_COUNT

            FROM

            [PathName\FileName - AU_2013*.csv]

            (txt, unicode, embedded labels, delimiter is ',', msq)

            where  EXTRACT_DATE>=max(EXTRACT_DATE);

            • Re: Incremental Load of New Files Only
              Jonathan Dienst

              Hi

               

              This script will load the number of days specified in vDaysToLoad, skipping over any days missing from the folder specified in zPath:

               

              Set vDaysToLoad = 30;

               

              For zDate = Today(1) - vDaysToLoad - 1 To Today(1) - 1

                Let zPath = 'PathName\Filename - AU_' & Date(zDate, 'YYYY_MM_DD') & '.csv'

               

                If Alt(FileSize(zPath), 0) <> 0 Then

               

              DailyFile:

                  LOAD

                  EXTRACT_DATE AS Date,

                  PRESENTED_COUNT,

                  VIEW_COUNT,

                  ACCEPTED_COUNT,

                  NOT_ACCEPTED_COUNT,

                  SUPRESSED_COUNT,

                  FileBaseName() As Source

                FROM [$(zPath)]

                (txt, unicode, embedded labels, delimiter is ',', msq);

               

                End If

              Next

               

               

              HTH

              Jonathan

              • Re: Incremental Load of New Files Only
                Srikanth P

                Here is the idea:

                 

                Intially load all the files in the folder into QVD. Create another QVD having only Filenames.

                 

                Second time reload, compare the file name with filenames qvd and load only new files only.

                • Re: Incremental Load of New Files Only

                  Hi David, did you sort this out somehow?

                   

                  I am having a similar issue, where I have a daily delta qvd load into a QVDs folder running. From this folder I would like to read only the QVDs, which haven't been read already in my report application. There's a datestamp on the delta QVDfile but it could be that there are several QVD loads per day. I know the delta load logic, but don't know the syntax!

                   

                  Firstly create a Loaded.qvd with all the loaded delta. Then in report application compare this Loaded.qvd with the delta QVD files in the QVDs folder, and load only the ones which haven't been loaded already. Then add these into Loaded.qvd. Just like dathu.qv proposes.