topic Re: Load Script - Excel Table Loads Now Failing in QlikView

Load Script - Excel Table Loads Now Failing

Anonymous — Wed, 01 Apr 2015 09:33:54 GMT

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.

Re: Load Script - Excel Table Loads Now Failing

sunny_talwar — Wed, 01 Apr 2015 15:48:26 GMT

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

Re: Load Script - Excel Table Loads Now Failing

Anonymous — Wed, 01 Apr 2015 16:04:50 GMT

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

Re: Load Script - Excel Table Loads Now Failing

Wed, 01 Apr 2015 16:23:53 GMT

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.

Re: Load Script - Excel Table Loads Now Failing

sunny_talwar — Wed, 01 Apr 2015 16:46:12 GMT

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

Re: Load Script - Excel Table Loads Now Failing

Anonymous — Wed, 01 Apr 2015 17:06:51 GMT

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)) ;

Re: Load Script - Excel Table Loads Now Failing

Wed, 01 Apr 2015 17:14:00 GMT

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

Re: Load Script - Excel Table Loads Now Failing

Anonymous — Wed, 01 Apr 2015 17:29:41 GMT

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?

Re: Load Script - Excel Table Loads Now Failing

Anonymous — Fri, 03 Apr 2015 06:50:06 GMT

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?