Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

Tags (1)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Load New File Data into Table

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

6 Replies
swuehl
Not applicable

Re: Load New File Data into Table

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

Not applicable

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. 

swuehl
Not applicable

Re: Load New File Data into Table

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?

Not applicable

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.

swuehl
Not applicable

Re: Load New File Data into Table

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

Not applicable

Re: Load New File Data into Table

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

Thanks,

Mindy