Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Check whether a sheet in excel exists before loading it

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
Valued Contributor III

Re: Check whether a sheet in excel exists before loading it

Use the following script

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

(Your load statements here)

EnfIf

MVP
MVP

Re: Check whether a sheet in excel exists before loading it

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;

Community Browser