Re: Validating URL's - Qlik Community

Report Inappropriate Content · ‎2014-03-23

Hi, I have an excel file which contains URL's in one field and I would like to validate those URL's whether it is valid or Invalid. Currently I am doing it by manual but I have more than 10K which I need to validate without manual. I am looking forward to avoid manual work, Is it possible to do with Qlikview? Or is there any function or VB macro in to achieve this. Thanks, URL Web Site - Found www.clippingpath.co.uk Valid www.ourmanagement.net Invalid www.digitalphotoservices.co.uk Valid www.glproductins.dk Invalid

swuehl · ‎2014-03-23

You can try it like I suggested here:

Validate a url

rbecher · ‎2014-03-23

Hi Madu,

this seems to work:

URL_List:
LOAD if(left(URL,7)<>'http://','http://'&URL,URL) as URL;
LOAD * INLINE [
    URL
    www.clippingpath.co.uk
    www.ourmanagement.net
    www.digitalphotoservices.co.uk 
    www.glproductins.dk
];

FOR i=0 to NoOfRows('URL_List')-1

    Let vURL = peek('URL', $(i), 'URL_List');
    
    URL_Valid:
    FIRST 1 LOAD 
    '$(vURL)' as URL, 
    @1 as first_line, 
    if(@1='<html>', 0, 1) as is_valid
    FROM $(vURL)
    (txt, codepage is 1252, no labels, delimiter is '\t', msq);

NEXT

You could try to read the first line from the HTML page of the URL. If the domain doesn't exists QlikView gives back "<html>". Of course an existing website's HTML page could also start with a plain "<html>" tag but this is very unusual nowadays with CMS systems and xhtml sites.

- Ralf

Astrato.io Head of R&D

Anonymous · ‎2017-08-17

When I tried to use the above script in Qlik sense, there is error message saying: This statement only works with lib:// paths in this script mode.

Any suggestion?

Thanks!

rbecher · ‎2017-08-18

Web sources doesn't work this way in Qlik Sense. You can use the Qlik REST connector instead.

Astrato.io Head of R&D

Anonymous · ‎2017-08-18

Thanks so much for your help!

I am not going to load data from web, so I did not use Qlik REST connector.

I have a list of URL, and I am going to check which URL is valid and which is not valid, and count the number in Qlik sense sheet. Could you please give any more suggestion?

rbecher · ‎2017-08-18

If you do not load data from the web how else you could check an URL is valid?

Astrato.io Head of R&D

Anonymous · ‎2017-08-18

Thanks so much for your help!

Take the URL in your script as example:

www.clippingpath.co.uk

www.ourmanagement.net

www.digitalphotoservices.co.uk

www.glproductins.dk

I would like to check which URL in the above is down, and then create a pie chart in Qlik sense to show how many is down and how many is OK.

If using Qlik Sense REST connector, we can only check one URL each time and it is also manual.

Is there a way we can check with script as you show in the above?

When I tried to use the above script in Qlik sense, there is error message saying: This statement only works with lib:// paths in this script mode.

Any further suggestion?

rbecher · ‎2017-08-18

You can create a REST connection use WITH CONNECTION to give the URL dynamically:

URL_List:  
LOAD if(left(URL,7)<>'http://','http://'&URL,URL) as URL;  
LOAD * INLINE [  
URL  
http://www.clippingpath.co.uk  
http://www.ourmanagement.net  
http://www.digitalphotoservices.co.uk   
http://www.glproductins.dk  
];  

LIB CONNECT TO 'CheckURL';

FOR i=0 to NoOfRows('URL_List')-1  

      Let vURL = peek('URL', $(i), 'URL_List');  

    SET ErrorMode=0;

    URL_valid:
    LOAD '$(vURL)' as URL, *;
    SQL SELECT 
      "Vary",
      "Keep-Alive",
      "Connection",
      "Accept-Ranges",
      "Content-Length",
      "Content-Type",
      "Date",
      "ETag",
      "Last-Modified",
      "Server",
      "__KEY__response_header"
    FROM CSV "_response_header" PK "__KEY__response_header"
    WITH CONNECTION(Url "$(vURL)");

    SET ErrorMode=1;

NEXT

Astrato.io Head of R&D