how to skip a Excel Workbook ?

[madhubabum]

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

[marcus_sommer]

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

[madhubabum]

Hi Marcus Sommer

Thanks for your reply

It will showing  the following

Error : $(concat)

[marcus_sommer]

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

[marcus_sommer]

Yea, there was two small errors with the field- and tablenames which should be written overall equally - but this within the attachment worked.

- Marcus

[madhubabum]

Hi Marcus Sommer

Thanks For your replies,

How can we skipp  a workbook , if field values are not matched ?

Thanks

Madhu

[marcus_sommer]

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

[marcus_sommer]

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