topic Re: Load multiple excel web embaded files in QlikView

Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 00:02:52 GMT

hi all,

anyone knows how can i load multiple excel files from web directory.. I tried to use wild card.. but gave me errors..

the error is "Cannot Read BIFF file" which means one of the xls files is not actual xls file.. I have no control of the directory beside read only so any suggestion would help.

Example of one of the files

'http://www.ABC.com/~/media/Files/2015/Test%20data%20August%202015.xls'

and there is a file for each month up until the previous month..

Any idea?

Thxs.

Re: Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 20:19:56 GMT

anyone ?

Re: Load multiple excel web embaded files

petter — Tue, 06 Oct 2015 20:29:55 GMT

You can't use wildcards when the Excel files are read from the web via the HTTP-protocol. That only works for normal file drives or UNC paths.

So if you have a list of the file names you could do:

FOR EACH webfile IN 'January','February','March','April','May','June','July','August'

ExcelData:

LOAD

FROM

"h t t p : / / www.abc.com/~......../Test%20data%20$(webfile)%202015.xls" (ooxml ....);

NEXT;

Re: Load multiple excel web embaded files

petter — Tue, 06 Oct 2015 20:30:45 GMT

Re: Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 20:53:36 GMT

I am getting an error say "Cannot read BIFF file"

Re: Load multiple excel web embaded files

petter — Tue, 06 Oct 2015 20:59:25 GMT

The you should add:

ErrorMode = 0;

on a line before the FOR statement.

This should prevent the load script from terminating when it encounters a read error. That way you will be able to load the remaining files. Is that what you want?

Additionally you can use the ScriptError variable immediately after the LOAD statement to report which LOAD that lead to an error.

Re: Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 21:20:36 GMT

thank you so much.. this is working as needed.. next step I have the year listed in the url.. I have files from 2014 and I am expecting files in 2016.. any idea on how I can automate the load to include data from 2014 and load 2016 data as it comes in the future through the load trigger?

Re: Load multiple excel web embaded files

petter — Tue, 06 Oct 2015 21:26:14 GMT

You will have to add an outer loop for years:

FOR vYear=2014 TO 2015

FOR EACH ....

LOAD

'$(vYear)' AS SheetYear // if you need to add it as a field - unless the year is already a column in the xls

.....

....

FROM

"............ $(webfile)%20$(vYear).xls" (ooxml ....);

NEXT;

Re: Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 21:27:37 GMT

yup, exactly but that will not load in future data.. 2016 for example

Re: Load multiple excel web embaded files

petter — Tue, 06 Oct 2015 21:36:01 GMT

Change it to:

FOR vYear=2014 TO 2016

Since ErrorMode 0 will let it silently fail (I think) if it cant find the file....

Re: Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 22:34:09 GMT

I appreciate it.. i was thinking to automate it permanently but that would work.. i will do it up to 2020..

Lats question I have

I want to show the month and year of each file within the table. I tried

$(vYear) as ReportYear,

$(webfile) as ReportMonth,

But that caused an error and returned no columns

Re: Load multiple excel web embaded files

alec1982 — Tue, 06 Oct 2015 23:20:05 GMT

$(vYear) as ReportYear, work fine and the year column is created..

$(webfile) as ReportMonth, no data on the dashboard will show up and no columns are showing up if this is added..