Load New File Data into Table

[#1]

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

[swuehl]

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

[#3]

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

[swuehl]

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?

[#5]

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.

[swuehl · Accepted solution]

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

[#7]

YES!!! That worked.  Thanks so much.  Now to study and learn this new piece of coding.

Thanks,

Mindy