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

    Read internet file with a loop

      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

               

              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