Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
undergrinder
Specialist II
Specialist II

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.

View solution in original post

5 Replies
undergrinder
Specialist II
Specialist II

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
Specialist II
Specialist II

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

G.

Not applicable
Author

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
Specialist II
Specialist II

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.

Colin-Albert

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

    ...