topic Re: Load New File Data into Table in QlikView

Load New File Data into Table

Mon, 03 Aug 2015 20:00:31 GMT

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

swuehl — Mon, 03 Aug 2015 21:28:59 GMT

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

Tue, 04 Aug 2015 17:15:02 GMT

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

swuehl — Tue, 04 Aug 2015 17:20:15 GMT

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

Tue, 04 Aug 2015 17:27:21 GMT

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

swuehl — Tue, 04 Aug 2015 17:47:00 GMT

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

Re: Load New File Data into Table

Tue, 04 Aug 2015 18:05:32 GMT

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

Thanks,

Mindy