
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Load an excel sheet only if the sheet exists
Hello,
I would like to load data from an excel file (sales.xlsx) with several optional sheets (Year, Month). In case a sheet does not exist I want the script to continue and I don't want errors to be raised.
This is my load script:
set ErrorMode = 0;
YearSales:
LOAD
Year
SalesPerYear
FROM [$(vConfigPath)sales.xlsx]
(ooxml, embedded labels, table is Year);
MonthSales:
LOAD
Month
SalesPerMonth
FROM [$(vConfigPath)sales.xlsx]
(ooxml, embedded labels, table is Month);
set ErrorMode = 1;
As expected the script does not exist in case a sheet (e.g Year) does not exist, and the APP gets saved successfully.
However I get this error:
Is there a way to avoid these errors?
Many thanks
KInd Regards
- Tags:
- excel
- load scrip
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Annick,
If the script is proper, and the worksheet is exist and haven't got other problem the output shouldn't contains any error.
Check this page: https://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ErrorVariables/ErrorVariables...
You can use the ScriptErrorList variable to elaborate the problem. (I haven't used before).
G.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Annick,
Your script works well.
If you set the ErrorMode = 0 the logs contains the errors, but the process continue.
I think your code has an other problem:
- Don't forget the commas between fieldnames
- Ensure your variable contains a right values (Lib connection e.g.)
G.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First test your code with proper spreadsheets, if it works well, afterwards add the set errormode =1 code sniplet.
G.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello G.
Actually I have the commas in the script + the variable has a right value.
The script works i.e. gets executed till the end. I just would like to avoid the error displayed at the end : "finished with error(s)" "2 forced errors".
Ideally, I would like to have a way to check if a specific sheet/tab exists within an excel file.
Any idea if this is feasible?
Many thanks
Annick


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Annick,
If the script is proper, and the worksheet is exist and haven't got other problem the output shouldn't contains any error.
Check this page: https://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ErrorVariables/ErrorVariables...
You can use the ScriptErrorList variable to elaborate the problem. (I haven't used before).
G.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You could add some trace commands to your script to check that your variables are OK.
trace >>> Loading files from [$(vConfigPath)sales.xlsx] ;
set ErrorMode = 0;
YearSales:
LOAD
Year
SalesPerYear
...
