Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Is there a way to check if a certain sheet exists in an excel file before I load it?
Thanks,
Boris
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
Use the following script
If Not(IsNull('your excel file name')) Then
(Your load statements here)
EnfIf
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;