Load Script - Excel Table Loads Now Failing

[Anonymous]

Hello Community,

My first post so go easy with me please.

I have my first Qlik application which was running just fine but which now fails to load data from a series of Excel files containing data in multiple worksheets.  This has been developed in a FOC copy of QlikView Desktop.

I think that there could be a bug in the Exel (xlsx) load feaure such that when you have (multiple worksheets - may not be material) with embedded labels (column headings) and a worksheet header which includes some blank rows the load gets confused.

The Excel files typically have 8 header rows. 3 of these are blank.

Running the Script Table Files process it finds 5 lines (i.e. ignores blanks).

Importing directly from the File Open (select Excel file), that finds 8 lines.

The script was originally setup with the second method i.e. with 8 lines when starting out.  This worked for over a week (many edits / reloads). I entered a licence key into the product and now the script fails to load (cannot find tables / columns).

Has anyone had any similar experiences / or knows how to handle the situation or has a best practice example for Excel data import. I have many Excel files to import and need to refresh the data periodically.

[sunny_talwar]

Have you by any chance moved the location of your qvw file or the datasource itself? I recommend re-bringing the database into QlikView's script using the  using the File Open button (this will help change anything that might have changed in the database itself.

HTH

Best,

Sunny

[Anonymous]

Not on the original machine Sunny - I just added a licence key.

I set a path variable for the location of the various Excel files in my script. Should I add anything else?

Cheers

Martyn

[#4]

Hi Martin,

It would be very useful if you could post the script code and maybe some screenshots or dummy data of your Excel files.

[sunny_talwar]

Do you know if the database was modified (sheet name changed from what it was before?).

It could be because of many reason. Why don't you try to read the script for the particular table by using the 'Table Files...' button

[Anonymous]

The source files have the exact ame structure but I have had to SET vHeader1 = 5 and SET vHeader2 = 6 to get them to load again. The load seems to be adversly impacted by the presence of blank lines in header of the Excel file?

Script Declarations tab:

Of the form:

SET vPath = 'F:\Martyn\ .....\Data Reports\';

// Source data files

SET vTableA = 'Timesheets';

SET vFile1 = 'Source Timesheet Report 1.xlsx';

SET vSheet1 = 'Timesheets Prior_';

SET vHeader1 = 5;

SET vWhere1 = '[Employee Cost Centre]=1234';

SET vFile2 = 'Source Timesheet Report 2.xlsx';

SET vSheet2 = 'Timesheets Prior_';

SET vHeader2 = 6;

SET vWhere2 = '[Employee Cost Centre]=5678';

Typical File Load tab:

Loads of the form:

'$(vTableA)':

LOAD [Employee etc....

...

FROM

'$(vPath)$(vFile1)'

(ooxml, embedded labels, header is $(#vHeader1) lines, table is '$(vSheet1)')

WHERE($(vWhere1)) ;

CONCATENATE '$(vTableA)':

LOAD [Employee etc....

...

FROM

'$(vPath)$(vFile2)'

(ooxml, embedded labels, header is $(#vHeader2) lines, table is '$(vSheet2)')

WHERE($(vWhere2)) ;

[#7]

I'm sorry I'm not sure if you solved it or not using the vHeader variables .

[Anonymous]

Solved in the sense that it works (for now) as I modfied the script (as shown).

The problem is that Qlik appears to (rarely / occassionally) exclude a random number of blank Excel worksheet rows before the column heading row.  Seems to be a bug?

I need to load data from many Excel files and do so regularly / reliably so wondered about the best way forward / best practice?

[Anonymous · Accepted solution]

Some Excel loads have failed again using this month end's files.

In  the case of the Timesheet workbook / worksheet header I had to set the line variable back to 5 lines from 6 lines for some of the cost centre files (all are in reality 8 lines including all blank lines). 

Does anyone have any thoughts on how best to deal with this across many recurring spreadsheet loads?