Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have 3 Excel Workbook in D:\ExcelFiles With multiple Sheets as following screen shots;
Particular Sheet Name is same for all Workbooks : Output Tracker.
In 2 nd Work book Row 5 is missing in Output Tracker Sheet.
In such type of Scenarios , I want to Skipp that entire Workbook . How ?
Output : I want to load WorkBook 1 & Work Book 3
I think you will need an additionally load-step to check if there is any value in cell A5 available - an quite easy and very flexible way to do such loadings and checkings is to load with filelist and to include the checkings there within a if-loop. Take this as starting point:
for Each file in FileList('D:\ExcelFiles\*.xlsx')
check:
first 5 load A, recno() as RecNo from $(file) (ooxml, no labels, table is [Output Tracker]);
let vCheck = peek('Opportunity Type', 4, 'Check');
if len('$(vCheck)') >= 1 then
table:
$(concat)
load *, '$(file)' as filename
from $(file) (ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);
set concat=' concatenate (table) ';
end if
drop tables check; let vCheck = null();
NEXT;
- Marcus
Hi Marcus Sommer
Thanks for your reply
It will showing the following
Error : $(concat)
Yes there was something missing:
set concat='';
for Each file in FileList('D:\ExcelFiles\*.xlsx')
check:
first 5 load A, recno() as RecNo from $(file) (ooxml, no labels, table is [Output Tracker]);
let vCheck = peek('Opportunity Type', 4, 'Check');
if len('$(vCheck)') >= 1 then
table:
$(concat)
load *, '$(file)' as filename
from $(file) (ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);
set concat=' concatenate (table) ';
end if
drop tables check; let vCheck = null();
NEXT;
- Marcus
Yea, there was two small errors with the field- and tablenames which should be written overall equally - but this within the attachment worked.
- Marcus
Hi Marcus Sommer
Thanks For your replies,
How can we skipp a workbook , if field values are not matched ?
Thanks
Madhu
The checking within the loop is now extend to the missing data (see attachment):
for Each file in FileList('C:\Temp\*.xlsx')
check:
first 5 load A as [Opportunity Type], recno() as RecNo from $(file) (ooxml, no labels, table is [Output Tracker]);
let vCheck = peek('Opportunity Type', 4, 'check');
if len('$(vCheck)') >= 1 then
let vConcatenate = if(noofrows('table') >= 1, 'Concatenate (table)', 'table:' & chr(10) & 'NoConcatenate');
$(vConcatenate)
load *, 'Data' as Type, '$(file)' as filename, filesize('$(file)') as FileSize, filetime('$(file)') as FileTime
from $(file) (ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);
set concat=' concatenate (table) ';
else
let vConcatenate = if(noofrows('table') >= 1, 'Concatenate (table)', 'table:' & chr(10) & 'NoConcatenate');
$(vConcatenate)
load 'Missing Data' as Type, '$(file)' as filename, filesize('$(file)') as FileSize, filetime('$(file)') as FileTime
Autogenerate 1;
end if
drop tables check; let vCheck = null();
NEXT;
- Marcus
I don't understand. Files with data and skipped files are included within the table with filename, filetime and filesize and if you applied such a logic there aren't any script-errors which could be handled and recorded. In general the error-mode will be switched on, continuously queried and then appropriate react and afterwards switched off again - most often implemented within if-loops, see also: ErrorHandling in Qlikview.
- Marcus