Loading data from Web URLs

[alec1982]

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%2...

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2016/warehouse%20company%20stocks%2...

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2016/warehouse%20company%20stocks%2...

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..

[#2]

Can we load data directly from URL

I think we need any connector

[avinashelite]

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

Go to edit script>web files > internet files 

[alec1982]

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..

[settu_periasamy]

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?

[Peter_Cammaert]

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

[MarcoWedel]

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%20stoc...]
  (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

[alec1982]

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

[Peter_Cammaert]

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