Your code is amazing, but I need yourhelp to get it working for just one file in a directory.
I don't want it to look at sub directories at all. I tried making some changes but then the code kept on falling over, because I don't know anything about the kind of scripting you are doing as I am relatively new to scripting in QlikView
Can you please just guide me to make it work by eliminating the For loops so that it just looks for only one file and does not look at sub directories but only the main directory specified.
Sub DoDir (Root)
For Each Ext In 'csv' // filetype to search for in current directory
For Each File In FileList (Root & '\MYFILENAME*.' & Ext)
Load '$(File)' as Name,
FileTime('$(File)') as FileTime,
RangeSum(Peek('FileCount'), 1) as FileCount
For Each Dir In DirList (Root & '\*') // search in sundirectories
Call DoDir (Dir)
Next Dir End Sub Call DoDir ('C:\MYFOLDER'); // Starting point
FIRST 1 LOAD Name AS MostRecentFileName
RESIDENT Files ORDER BY FileTime DESC;
LET vFileToLoad = FieldValueCount('MostRecentFileName');
for each file in FieldValueList('MostRecentFileName')
TableName: LOAD ................... FROM $(file)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
store TableName into [C\$(file).qvd];
DROP TABLE MostRecentFileName,Files;
Although all code that is posted in the Community is free to use and modify, please refer to the actual sources when you provide code to other people. That's the best way to improve the code if needed and check the bugs it may contain and keep the best possible solution in place.
Thanks and regards.
Without the need of that macro, and provided all CSV files are in the same folder you can improve the load performance and do just
Table: LOAD * FROM C:\Folder\FileName*.txt (options and delimiters here);
The "*" is valid as a name and it will work within the folder specified.
Hope that helps.
Ellaborating a bit further my code above, the following should work fine without needs of macro or loops, and likely improving performance in the loading process
LET vLastDate = Date(Today(), 'YYYYMMDD'); // variable with the date you want to start loading Table: LOAD Field1 FROM file*.txt // filename (txt, codepage is 1252, embedded labels, delimiter is '\t', msq) WHERE Date(Right(FileBaseName(), 8)) >= '$(vLastDate)';
Hope that makes sense.
Thanks for you help Miguel. My directory contains multiple files, but I only want to load the file for the most recent date alone, that is why i have avoided using the *.
I tried your code above as I am aiming to avoid using macros and loops if possible, but because of the * it picked up other files with the dates I do not want, as I only want to load the file which has the newest info alone.
Then something like the following may do the trick
DatesOfFiles: LOAD MaxString(Right(FileBaseName(), 8)) AS PossibleDates FROM file*.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq); LET vLastDate = Peek('PossibleDates', -1); DROP TABLE DatesOfFiles; LastDataFile: LOAD * FROM file$(vLastDate).txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Although my guess is it does, I don't know whether this would load faster than the macro one.
Hope that helps.