Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Is it possible before a load to check if a sheet exits in a xls file ?
Thks
Michel
You can set the ErrorMode to 0, so that the .qvw will reload successfully, try to load from the sheet, and if it doesn't exist, your ScriptError will be 11 (Field not found). Then you can just set the error mode back to 1.
So your script could look something like this:
SET ErrorMode = 0;
LOAD *
FROM YourFile.xlsx
(ooxml, embedded labels, table is [Sheet Name]);
IF ScriptError = 11 THEN
//do something
ELSE
//do something else
ENDIF
SET ErrorMode = 1;
From QV Help:
ErrorMode
This variable determines what action is to be taken by QlikView when an error is encountered during script execution. By default (ErrorMode=1) the script execution will halt and the user will be prompted for action (non-batch mode). By setting ErrorMode =0 QlikView will simply ignore the failure and continue script execution at the next script statement. By setting ErrorMode =2 QlikView will trigger an "Execution of script failed..." error message immediately on failure, without prompting the user for action beforehand.
Example:
set ErrorMode=0;
Returns the error code of the last executed script statement. This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component. The following error codes exist:
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 Example:
set ErrorMode=0;
load * from abc.qvw;
if ScriptError=8 then
exit script;
//no file;
end if
or see this post
Re: Pulling multiple excel sheets into QV when all headings are the same
you can loop on excel sheets (see gwassenaar) and check the name before loading
Thks a lot for your answer...
The system returns an error , It's true ---> ScriptError <>0
But I cannot dertermine what is the number of the error... ;-(
you can also loop in a path to build a list of result and then evaluate in this list if your excel is ther or not
FileList:
load * Inline [
l1
];
Set vConcatenate = ;
sub ScanFolder(Root)
for each FileExtension in 'xlsx'
for each FoundFile in filelist( Root & '\*backseg*.' & FileExtension)
concatenate(FileList)
FileList:
LOAD
subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-1) as instancia,
subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-0) as producto,
date(subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-4)&'-'&subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-3)&'-'&subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-2)) as Fecha,
subfield ('$(FoundFile)','\',substringcount('$(FoundFile)','\')+1) as file,
keepchar(subfield(subfield('$(FoundFile)','.log.',2),'-',4),'0123456789') as hora,
filesize('$(FoundFile)') as bytes,
filetime ('$(FoundFile)') as lastupdate
AUTOGENERATE 1;
Set vConcatenate = Concatenate;
next FoundFile
next FileExtension
for each SubDirectory in dirlist( Root & '\*' )
call ScanFolder(SubDirectory)
next SubDirectory
end sub
Call ScanFolder('your path here');
i hope it help
Hi
You can open the Excel file using ODBC and get the sheet names like this (vReportDefinitions contains the Excel file path and name):
ODBC CONNECT32 TO [Excel Files;DBQ=$(vReportDefinitions)];
SpreadsheetData:
SQLTABLES;
DISCONNECT;
For i = 0 to NoOfRows('SpreadsheetData') - 1
Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData')
TRACE $(zSheet);
//do something with the sheet, eg
LOAD *
FROM $(vReportDefinitions)
(ooxml, no labels, table is [$(zSheet)]);
Next
HTH
Jonathan