Concatenate load of Excel sheet that doesn't yet exist.

[#1]

Good Morning, Got an interesting scenario thats popped up, im running a report that imports vast  amounts of data from Excel into Qlickview.

Usually when you have to load more than 65,536 rows of data, the data will spill into a new tab called sheet2$

im trying to future proof my report so that when sheet 2 is fully populated it will then pull data from sheet 3 so on and so forth

On the XLS file I am importing  these sheets dont yet exist and Qlickview wont let me future proof my report by naming the tabs to be loaded in a future event without me having to go back and modify my report ever month or so.

The code ive used so far.

Source_Data:

LOAD [Contract A/C] as CA,

Balance as Amount

FROM

[*.xls]

(biff, embedded labels, table is Sheet1$);

;

Source_data2:

Concatenate

LOAD @1 as CA,

@3 as Amount

FROM

[*.xls]

(biff, no labels, table is Sheet2$)

;

[#2 · Accepted solution]

Hi,

You could add the following statement before  the LOAD statements

SET ErrorMode=0;

This will allow the script editor to ignore errors during script execution.

This way you won't have to go back to the previous script if any errors are encountered.

Hope that helps.

Regards,

-Khaled

[#3]

Thankyou very much! Right answer. A BIG However though which is very important. you must type

SET ErrorMode=1; like below otherwise Qlickview will not detect errors made after this line of code is written!

Source_data2:

SET ErrorMode=0;

Concatenate

LOAD @1 as CA,

@3 as Amount

FROM

[*.xls]

(biff, no labels, table is Sheet2$)

;

SET ErrorMode=1;

[#4]

Hi,

Glad. Yes, should write that definitely.

Regards,

-Khaled