4 Replies Latest reply: Nov 11, 2009 12:32 PM by Jean-Jacques Jesua RSS

    Read internet file with a loop

    Jean-Jacques Jesua

      Hi,

      I'm a new QV user. Maybe my question is trivial.

      I want to read several files on the web with different name but with the same structure (web page)

      example:

      http://www.parisavelo.net/detail-station-11001-b.htm

      I know how to read this page.

      To help me , I have an excel file with the name of all the files i want to read.

      How can i make script to make a loop ?

       

      Thanks for your help

      Jean-Jacques

       

       

        • Read internet file with a loop
          Johannes Sunden

          Hi Jean-Jacques,

          Basically what you can do is load all the values from Excel with the filenames into a field. Then you tell QlikView to count the values in the field. After this you loop as many times as there are values, pulling a new value from a new position every time and using that path to load from the specific html file.

          This is written without testing in QlikView so there might be some syntax issues:

          let vCounter = fieldvaluecount('ExcelFieldWithNames'); //Counting the values from the excel table

          for i=1 to $(vCounter) //initiating the loop - as many times as there are values in excel

          let vExcelFieldValue = FieldValue('ExcelFieldWithNames',$(i)); //pull the actual URL from position i in the excel file

          Load
          xxx,
          yyy
          from $(vExcelFieldValue); //Loading the table from the current URL

          next //Moving to the next step where i increases by 1 until it finally hits the number of field values in the excel file

            • Read internet file with a loop
              Jean-Jacques Jesua

               

              Thanks Johannes for your help, it works fine (almost).

              Just another point. What if the vExcelFieldValue doesn't exist (maybe the value is not synchronize with the URL). Is there a test to prevent the access ?

              thanks again

               

              Jean-Jacques

                • Read internet file with a loop
                  Johannes Sunden

                  I hope I understand this correctly. You want some kind of error checking mechanism in place?

                  What you can do is:

                  let vCounter = fieldvaluecount('ExcelFieldWithNames'); //Counting the values from the excel table

                  for i=1 to $(vCounter) //initiating the loop - as many times as there are values in excel

                  let vExcelFieldValue = FieldValue('ExcelFieldWithNames',$(i)); //pull the actual URL from position i in the excel file

                  set ErrorMode = 0; //This tells QlikView not to halt on script errors like if the URL doesn't have the same table structure

                  Load
                  xxx,
                  yyy
                  from $(vExcelFieldValue); //Loading the table from the current URL

                  if ScriptError=11 then
                  //In here you can choose what to do if it's a ScriptError 11 for example, Field not found.
                  end if

                  next //Moving to the next step where i increases by 1 until it finally hits the number of field values in the excel file

                  -----------
                  ScriptError codes:

                  1 No error
                  2 General Error
                  3 Syntax Error
                  4 General ODBC Error
                  5 General OLE DB Error
                  6 General XML Error
                  7 General HTML Error
                  8 File Not Found
                  9 Database Not Found
                  10 Table Not Found
                  11 Field Not Found
                  12 File Has Wrong Format