6 Replies Latest reply: Aug 4, 2015 2:05 PM by Mindy Kay RSS

    Load New File Data into Table

      All,

       

      I have code to go through .csv files in a specific folder and load their data into a table.  But now, how do I take it a step further and only load new rows of data from new files in the folder?  What I want is for the code to look to see if FileName already exists in CSVEventDataFiles.  If it does, move on to next file in FileList until a new FileName is found and then add the requested file data into CSVEventDataFiles.

       

      FOR Each vCSVEventDataFile in FileList('E:\share\TAM\QlikView\Common Reference\Data\Compliant Events\*.csv')

        CSVEventDataFiles:
        LOAD

           '$(vCSVEventDataFile)' as FilePathName,

           TextBetween('$(vCSVEventDataFile)', '\Compliant Events\', '.csv') as FileName,

           FileSize('$(vCSVEventDataFile)') as Size,
           FileTime('$(vCSVEventDataFile)') as FileTime,
           Date(Date#(TextBetween('$(vCSVEventDataFile)', '\TAMCompliant', '_'), 'DD-MMM-YYYY'), 'MMM-YYYY') as DataFileDate

          AutoGenerate 1;

      NEXT vCSVEventDataFile

       

      Thanks,

      Mindy

        • Re: Load New File Data into Table
          Stefan Wühl

          I assume you are storing the CSVEventDataFiles table afterwards and loading in at the top of your script the next run, right?

           

          Then try maybe something like:

           

           

           

          FOR Each vCSVEventDataFile in FileList('E:\share\TAM\QlikView\Common Reference\Data\Compliant Events\*.csv')

           

           

          IF Isnull(lookup('FilePathName','FilePathName', '$(vCSVEventDataFile)','vCVSEventDataFile')) THEN

           

            CSVEventDataFiles:

          LOAD

               '$(vCSVEventDataFile)' as FilePathName,

               TextBetween('$(vCSVEventDataFile)', '\Compliant Events\', '.csv') as FileName,

               FileSize('$(vCSVEventDataFile)') as Size,

               FileTime('$(vCSVEventDataFile)') as FileTime,

               Date(Date#(TextBetween('$(vCSVEventDataFile)', '\TAMCompliant', '_'), 'DD-MMM-YYYY'), 'MMM-YYYY') as DataFileDate

              AutoGenerate 1;

            

          ENDIF

           

           

          NEXT vCSVEventDataFile

            • Re: Load New File Data into Table

              Nope.  That is the whole code that I've come up with so far for what I want to do. 

                • Re: Load New File Data into Table
                  Stefan Wühl

                  Not sure if I understand.

                   

                  I believe your ForEach loop will only return each file name in the folder once, so why do you want to check for existence then?

                    • Re: Load New File Data into Table

                      At this moment we currently have over 200 .csv files in the specified folder.  Each day we get 6 or more new files added to that folder.  First, I want to do a bulk load of the data from the current files and then daily incremental loads of only the new files within that folder.

                        • Re: Load New File Data into Table
                          Stefan Wühl

                          Ok, but then I think you should consider storing the CSVEventDataFiles table into a QVD at the end of your script and loading this QVD in at the start (you need to check for existence to not run into an error the very first time):


                          IF FileTime('Files.qvd') THEN

                           

                          CSVEventDataFiles:

                          LOAD * From Files.qvd (qvd);

                           

                          ENDIF

                           

                          FOR Each vCSVEventDataFile in FileList('E:\share\TAM\QlikView\Common Reference\Data\Compliant Events\*.csv')

                           

                          if isnull(lookup('FilePathName','FilePathName', '$(vCSVEventDataFile)','vCVSEventDataFile')) THEN

                           

                            CSVEventDataFiles:

                          LOAD

                               '$(vCSVEventDataFile)' as FilePathName,

                               TextBetween('$(vCSVEventDataFile)', '\Compliant Events\', '.csv') as FileName,

                               FileSize('$(vCSVEventDataFile)') as Size,

                               FileTime('$(vCSVEventDataFile)') as FileTime,

                               Date(Date#(TextBetween('$(vCSVEventDataFile)', '\TAMCompliant', '_'), 'DD-MMM-YYYY'), 'MMM-YYYY') as DataFileDate

                              AutoGenerate 1;

                             

                          ENDIF

                           

                          NEXT vCSVEventDataFile

                           

                          STORE CSVEventDataFiles into 'Files.qvd' (qvd);