Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

6 Replies
swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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
Author

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

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
Author

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

Thanks,

Mindy