Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
I am trying to load data from web URLs. here are samples of the URLS:
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..
Can we load data directly from URL
I think we need any connector
Yes, we can connect for the URL s directly from QV .
Go to edit script>web files > internet files
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..
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?
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
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
(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
I have tried similar script. the problem is that it returns errors for not available months.
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