Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

How ignore empty sheet in xls files?

Last sheet in files is empty, and when I run script i receive following error message:

"Cannot locate table in BIFF file."


Part of script:

FOR j=0 TO NoOfRows('tables')-1

  LET SheetName = if(right(peek('TABLE_NAME',j,'tables'),14)='FilterDatabase',

    subfield(purgechar(purgechar(purgechar(subfield(peek('TABLE_NAME',j,'tables'),'$_',1), chr(39)),'$'),'_'), 'FilterDatabase', 1),

                    purgechar(purgechar(purgechar(peek('TABLE_NAME',j,'tables'), chr(39)),'$'),'_') );

LOAD *

.

.

.

next j;

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
4 Replies
Gysbert_Wassenaar

Try setting the ErrorMode to 0 for the loop.

SET ErrorMode = 0;

// For Next loop

For ...

    .....

Next

SET ErrorMode =1;


talk is cheap, supply exceeds demand
vchuprina
Specialist
Specialist
Author

I try this, script running without error message, but data from file doesn't load

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Gysbert_Wassenaar

Works for me. See attached files.


talk is cheap, supply exceeds demand
vchuprina
Specialist
Specialist
Author

Thank you Gysbert. It works fine when sheets in source file are numerated:

Sheet1 Sheet2 .... SheetN,

but doesn't work when sheet name without number at the end.

As example names of sheets in my files:

District    Comp    Status Sheet1(empty).

Maybe you could advise me another way to disregard Sheet1

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").