Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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 (2)
1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Load an excel sheet only if the sheet exists

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.

5 Replies
undergrinder
Valued Contributor II

Re: Load an excel sheet only if the sheet exists

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.

undergrinder
Valued Contributor II

Re: Load an excel sheet only if the sheet exists

First test your code with proper spreadsheets, if it works well, afterwards add the set errormode =1 code sniplet.

G.

Not applicable

Re: Load an excel sheet only if the sheet exists

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

undergrinder
Valued Contributor II

Re: Load an excel sheet only if the sheet exists

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.

Re: Load an excel sheet only if the sheet exists

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

    ...

Community Browser