Load an excel sheet only if the sheet exists

[#1]

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

[undergrinder]

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]

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

G.

[#4]

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 · Accepted solution]

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

    ...