Load multiple excel web embaded files

[alec1982]

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.

[alec1982]

anyone ?

[petter]

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;

[petter]

[alec1982]

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

[petter]

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.

[alec1982]

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?

[petter · Accepted solution]

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;

NEXT;

[alec1982]

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

[petter]

Change it to:

FOR vYear=2014 TO 2016

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