Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
For example.
I have 5 qvd files in a folder.
File 1.qvd -> have 11 rows - filetime = 1.1.2012 01:01:01
File 2.qvd -> have 22 rows - filetime = 1.1.2012 02:01:01
File 3.qvd -> have 33 rows - filetime = 1.1.2012 03:01:01
File 4.qvd -> have 44 rows - filetime = 1.1.2012 04:01:01
File 5.qvd -> have 55 rows - filetime = 1.1.2012 05:01:01
Say I only want to count rows of the 3 newest files in that folder, I would like to get this result.
FileName | FileTime | RowCount |
---|---|---|
File 5.qvd | 1.1.2012 05:01:01 | 55 |
File 4.qvd | 1.1.2012 04:01:01 | 44 |
File 3.qvd | 1.1.2012 03:01:01 | 33 |
I guess I have to create sort of loop logic but I'm not sure How I can skip to read all the other files in the folder.
The folder could have 40.000 qvd files but I only want to check the row count of the newest ones.
So, how would I do this ?
regards, Kári
Hi Kári,
Is this any faster? It only uses one loop so should be.
let RootPath = 'C:\{folder path}\*';
let HowManyFiles = 9;
For each file in FileList('$(RootPath)')
NewTbl:
LOAD
'$(file)' as filepath,
subfield('$(file)','\',-1) as newfilename,
FileTime('$(file)') as newdatefile
autogenerate 1;
Next;
ActiveQVDFiles:
FIRST $(HowManyFiles) Noconcatenate LOAD
*,
QvdNoOfRecords(filepath) as fileCount
resident NewTbl
order by newdatefile desc;
DROP Table NewTbl;
flipside
It could also be a solution if I could somehow have a where condition on the filetime of the files.
Like, filetime() > '1.1.2012 02:01:01'
If this is a better approach then this could help.
regards, Kári
Kári
Here's one way to do this
Set vNewestCount = 3;
Set vLocation = 'C:\Test';
For Each zFile in FileList('$(vLocation)\*.qvd')
T_FileTimes:
LOAD '$(zFile)' As FileName,
FileTime('$(zFile)') As FileTime
AutoGenerate 1;
Next
T_Select:
NoConcatenate
First $(vNewestCount) LOAD * Resident T_FileTimes
Order By FileTime desc;
DROP Table T_FileTimes;
For zi = 1 To vNewestCount
Let zFileName = Peek('FileName', zi, 'T_Select');
Let zFileTime = Peek('FileTime', zi, 'T_Select');
Let zRows = QvdNoOfRecords('$(zFileName)');
Results:
LOAD '$(zFileName)' As FileName,
Date($(zFileTime), 'D.M.YYYY hh:mm:ss') As FileTime,
$(zRows) As RowCount
AutoGenerate 1;
Next
DROP Table T_Select;
Put the correct location in the line setting vLocation and the number of 'most recent' QVDs in vNewestCount. I have not tested this script, but I think it is correct.
Hope that helps
Jonathan
I already found something similar and it looped through the files faster.
Here is the code:
Sub DoDir (Root)
For Each File In FileList (Root & '\*.qvd')
Files:
Load '$(File)' as Name,
FileTime('$(File)') as FileTime
Autogenerate 1;
Next File
End Sub
Call DoDir ('Q:\FilePath);
let HowManyFiles = 9;
MostRecentFileName:
FIRST $(HowManyFiles) LOAD Name AS MostRecentFileName, RowNo() as RowNumber
RESIDENT Files
ORDER BY FileTime DESC;
let a = 1;
LET vFileToLoad = FieldValue('MostRecentFileName', $(a));
do while a <= $(HowManyFiles)
LET vFileToLoad = FieldValue('MostRecentFileName', $(a));
FilesInFolder:
LOAD
FileSize() AS FileSize,
FileName() As FileName,
FileTime() AS FileTime,
count(1) as RowCounter
from $(vFileToLoad) (qvd);
let a = a+1;
loop;
Is it possible to skip all the 25.000 files I have in the folder because it takes about 1 minute to run.
regards, Kári
Hi Kári,
Is this any faster? It only uses one loop so should be.
let RootPath = 'C:\{folder path}\*';
let HowManyFiles = 9;
For each file in FileList('$(RootPath)')
NewTbl:
LOAD
'$(file)' as filepath,
subfield('$(file)','\',-1) as newfilename,
FileTime('$(file)') as newdatefile
autogenerate 1;
Next;
ActiveQVDFiles:
FIRST $(HowManyFiles) Noconcatenate LOAD
*,
QvdNoOfRecords(filepath) as fileCount
resident NewTbl
order by newdatefile desc;
DROP Table NewTbl;
flipside
It's a cleaner code so that helps, but still it takes 44 sec to loop through all the files.
Try moving the SubField line to the bottom step ...
For each file in FileList('$(RootPath)')
NewTbl:
LOAD
'$(file)' as filepath,
FileTime('$(file)') as newdatefile
autogenerate 1;
Next;
ActiveQVDFiles:
FIRST $(HowManyFiles) Noconcatenate LOAD
*,
QvdNoOfRecords(filepath) as fileCount,
SubField(filepath,'\',-1) as newfilename
resident NewTbl
order by newdatefile desc;
DROP Table NewTbl;
It might shave a few seconds off.
It did almost nothing.
43 sec now.
Thanks anyway.
My purpose is to get the median of the rowcount of the 9 newest files so I can trigger some alarm if the rowcount is too low. So if there is any other solution that's quicker I would appreciate that.
regards, Kári
I found a solution for the time-problem.
You of course didn't know this. My files have a timestamp in the filename so I create a much shorter filelist to loop through.
let year = year(Now());
let month = num(month(Now()));
let day = day(Now());
let yesterday = day(Now())-1;
let RootPath = 'Q:\Filepath\*$(year)$(month)$(day)*.qvd';
let RootPath2 = 'Q:\Filepath\*$(year)$(month)$(yesterday)*.qvd';
so I loop through 2 filelists and it only took 2 sec.
thanks for the contribution.
Kári