topic Re: Loading data from Web URLs in QlikView

Loading data from Web URLs

alec1982 — Wed, 25 Nov 2020 16:16:04 GMT

hi,

I am trying to load data from web URLs. here are samples of the URLS:

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2015/warehouse%20company%20stocks%20and%20queue%20data%20April%202015.xls

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2016/warehouse%20company%20stocks%20and%20queue%20data%20January%202016.xls

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2016/warehouse%20company%20stocks%20and%20queue%20data%20February%202016.xls

the thing to notice is the data is stored in multiple sub directories on web.. I tried a logic to load it but faced some errors..

Anyone has a good logic to load all files from the web URLS

Attached is my logic that is not working..

Re: Loading data from Web URLs

Wed, 11 May 2016 16:43:48 GMT

Can we load data directly from URL

I think we need any connector

Re: Loading data from Web URLs

avinashelite — Wed, 11 May 2016 16:52:24 GMT

Yes, we can connect for the URL s directly from QV .

Go to edit script>web files > internet files

Re: Loading data from Web URLs

alec1982 — Wed, 11 May 2016 16:53:13 GMT

yes, you can select web file on the script file type..

It works if I load individual file but fails when trying to load multiple files..

Re: Loading data from Web URLs

settu_periasamy — Wed, 11 May 2016 17:12:33 GMT

Hi,

I think 'End Sub' is in misplace..

Try this script

Directory;
WarehouseStocksandQueueTemp:
Load '' as Temp AutoGenerate 0;

sub ScanFolder(Root)
for each FileExtension in 'xls'
for each FoundFile in filelist( Root & '\*.' & FileExtension)

Concatenate (WarehouseStocksandQueueTemp)

LOAD
SubField(FileName(),'_',-2) as Month,
Left(SubField(FileName(),'_',-1),4) as Year,
if(len(Country)<1 , peek(Country),Country) as Country,
if(len(Location)<1 , peek(Location),Location) as Location,
[Warehouse Company],
[Aluminium Alloy],
Copper,
Lead,
NASAAC,
Nickel,
[Primary Aluminium],
Tin,
Zinc,
Cobalt,
RMC,
Steel,
F15 as [Opening Stock],
F16 as [Delivered In],
F17 as [Delivered Out],
[Total Closing Stock],
[Live Tonnage],
[Cancelled Tonnage]
FROM [$(FoundFile)]
(biff, embedded labels, header is 1 lines, table is [Non-ferrous metals$]);

next FoundFile

next FileExtension

END SUB

call ScanFolder('Enter Your file path')

Check this thread as well : loop through to load all files from a folder and its subfolders?

Re: Loading data from Web URLs

Peter_Cammaert — Wed, 11 May 2016 17:26:36 GMT

I don't know about this case, but most web servers don't allow you to list the pages and files present in a URL path. If you know the URL of a specific file, you'll be granted access to it. But the path without the page/file name itself is not accessible as a pseudo directory.

For example, if I enter the first URL in the address bar of my browser, it starts downloading the Excel file.

If I remove the file name from the URL, and enter that part in the address bar of my browser, I get a web server error. Try with: http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2015/

This is a web server restriction and not something you can fix in QlikView.

HOWEVER, what you can do is this: if you can predict the filenames that should be available (for example, for this year every monthly spreadsheet up until April or something) you can create a table with constructed URLs that should be available on this web server. The provider that is hosting these Excel files can help you with this.

Best,

Peter

Re: Loading data from Web URLs

MarcoWedel — Wed, 11 May 2016 23:44:04 GMT

Hi Peter,

good one.

Seems to work for Apr 2014 to Apr 2016:

SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';
LET vConcat = '';

FOR vYear = 2014 to 2016
  FOR vMonth = 1 to 12
  LET vMonthName = Date(Date#(vMonth,'M'),'MMMM');

  table1:
  $(vConcat)
  LOAD *,
      FileBaseName() as FileBaseName
  FROM [http://www.lme.com/~/media/Files/Warehousing/Queue%20information/$(vYear)/warehouse%20company%20stocks%20and%20queue%20data%20$(vMonthName)%20$(vYear).xls]
  (biff, embedded labels, table is [Non-ferrous metals$], filters(Remove(Row, Pos(Top, 1))));
   
    If NoOfRows('table1') then
      LET vConcat = 'Concatenate';
    ENDIF
  NEXT vMonth
NEXT vYear

regards

Marco

Re: Loading data from Web URLs

alec1982 — Thu, 12 May 2016 02:31:50 GMT

I have tried similar script. the problem is that it returns errors for not available months.

Re: Loading data from Web URLs

Peter_Cammaert — Thu, 12 May 2016 07:49:20 GMT

You mean that there can be gaps in full years? Then how do you know beforehand which files will be available? Trial & Error? Or is there a web page somewhere that lists the available months at any point in time? Or is there a rule that says something like "at the end of the month, the Excel file of the previous month will be available"?

If it is Trial & Error you have to rely on, use the SET ErrorMode = 0; technique immediately before Marco's FOR loop and add SET ErrorMode = 1; immediately after the FOR loop. This will suppress automatic error handling. Files that couldn't be found will not generate any error messages.

Peter