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

Check whether a sheet in excel exists before loading it

Sun, 21 Sep 2014 13:52:26 GMT

Hi all,

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

Thanks,

Boris

Sun, 21 Sep 2014 14:28:53 GMT

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

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

Regards,

Kalpesh

nagaiank — Sun, 21 Sep 2014 14:40:35 GMT

Use the following script

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

(Your load statements here)

EnfIf

maxgro — Sun, 21 Sep 2014 15:29:20 GMT

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;