Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am loading data from a folder with dozens of text files. The file names contain the dates in the format DD-MMM-YYYY, like this:
Weekly_TransactionExtract_A_05-AUG-2013_11-AUG-2013_12082013045253.txt
Also see the image attached.
I am only loading certain records in the file (where exists [Account no]) - but the load takes very long because it is running through all these files.
I want to add a variable to the start of the load script which is a start date of the particular campaign the QV File is reporting on. Then I want the load script to only load FROM files where the date in the file name is bigger than this variable.
Does that make sense? Please help.
Part of Load Script also attached.
Thanks,
Gerhard
Maybe something like this:
LET vRefDate = num(today());
Set vConcatenate = ;
sub ScanFolder(Root)
for each FileExtension in 'txt'
for each FoundFile in filelist( Root & '\*.' & FileExtension)
FileList:
$(vConcatenate)
if( date#(mid('$(FoundFile)',29,12),'DD-MMM-YYYY') > $(vRefDate)) then
LOAD *, '$(FoundFile)' as SourceFile
FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);
Set vConcatenate = Concatenate;
end if
next FoundFile
next FileExtension
end sub
Call ScanFolder('C:\Work\QV Apps') ;
Sorry this is a bit above my level....should that entire block of code go before all of my Loads?
in the attached images you can see my 4 tabs. I only want to use this for the "Transaction" and the "Applications" tabs, in which I load from different folders - images 3 and 4.
So should I add your code to my main tab where my variables etc are set? I'm lost...