If you just want to exclude some records, a simple where-clause will do the trick.
But if you instead want to reject the entire file because of one singe record, you will need to load the file in a temporary table, then evaluate this table, and finally load (or not load) the table into your "real" data table. For example:
Load Null() as DummyField AutoGenerate 1;
for each FoundFile in filelist( Root & '\Test*.csv' )
LOAD Count(X) as XCount
FROM "$(FoundFile)" (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where "X [ms]">=8500 and Y2>0;
Let FileIsOK = If(Peek('XCount',0,'tmpFile')>0,False(),True());
If FileIsOK Then
FileName() as FileName,
FROM "$(FoundFile)" (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Drop Table tmpFile;
Call ScanFolder('D:\Docs\Work\2018\Community\Suraj R\Files') ;
Many thanks for your help and the test solution. It works perfectly with the expectation that I had in mind
With regard to improving the effectiveness of this analysis, I have 3 inputs for the same:
1. Would it be possible to copy the files that do not contain the junk data onto a separate folder through the script?
(For instance, into a new folder called Good Data. This would help in easier handling of the data)
2. For some range of values of X (Eg: Between 1650 and 2000), how would we Need to Change the where condition for the same? (Tried to implement with 1650 < "X[MS]" < 2000, but it was faulty)
3. Also, how can we implement a file counter to keep track of the number of files loaded?
(I tried implementing as below, but it gives me the total files in the folder, not the count of files that we actually loaded.
Looking forward to hearing from you and thanks once again.
Screenshots (For all values of Y>0 at X<1500)
I tried implementing a logic for the 1st query in the above post. But, it's not an effective implementation. Introducing the copying function inside the loop leads to multiple execution windows popping up after every iteration which is silly considering the number of files that are to be iomported.
If we try using a loop outside the first for loop, how do I write the looping statement to copy the files read? Do we need to introduce another If condition again?(Tried giving the Loop Termination condition with respect to NoOfRows of files read, but it didn't work)
Thanks and best regards,