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: 
Not applicable

Check whether a sheet in excel exists before loading it

Hi all,

Is there a way to check if a certain sheet exists in an excel file before I load it?

Thanks,

Boris

3 Replies
Not applicable
Author

You can check by trying to load it and see what error is returned, if any;

set ErrorMode = 0;

load * from file;

if ScriptError=8  // this is file not found error

then

exit script;

end if

You can get below list of error mode from the help doc or reference manual

0No error
1General error
2Syntax error
3General ODBC error
4General OLE DB error
5General custom database error
6General XML error
7General HTML error
8File not found
9Database not found
10Table not found
11Field not found
12File has wrong format
13BIFF error
14BIFF error encrypted
15BIFF error unsupported version
16Semantic error

Regards,

Kalpesh

nagaiank
Specialist III
Specialist III

Use the following script

If Not(IsNull('your excel file name')) Then

(Your load statements here)

EnfIf

maxgro
MVP
MVP

you can loop on all sheet of an excel and check if exists (in the above script, Sheet2)

ODBC CONNECT32 To [Excel Files;DBQ=C:\Users\mgrossi\Downloads\Book1.xls];

Sheets:

SQLTABLES;

       

let var = NoOfRows('Sheets');

FOR i = 0 to $(var)-1

  let sheetName=subfield(peek('TABLE_NAME', i,'Sheets'),'$',1);

  if '$(sheetName)'='Sheet2' then

       trace $(sheetName) exists;

  end if;

next;