5 Replies Latest reply: Sep 27, 2016 5:01 PM by Fredrik Folkeryd RSS

    Use second row as header and delete first row

    Henke Willemsen

      Hi,

       

      I  have been searching a lot on the forum and tried (to combine) several answers.

       

      I am linking an url to a web application and retrieve information (see screenshot below). Everything is fine, but after retrieving data the column headers (Aangemaakt, Recruiter) etc. are also imported as data.

      data load.png

      I want to not import the "Paginagrootte Tonen" etc. and tried things like header =1, etc. but didn't get it working.

       

      I now use:

      LOAD

          @1,

          @2,

          @3,

          @4,

          @5,

          @6,

          @7,

          @8,

          @9

      FROM [lib://Applicants]

      (html, codepage is 1252, no labels, header is 2, table is @6)

      WHERE RecNo () <>1;

       

      gives:

       

      Result 1.png

       

      Does anyone know the answer?

        • Re: Use second row as header and delete first row
          Andy Weir

          Using your HTML I tried with a table in one of our HTML Pages and found you could alias your header names in the load script e.g

           

          @!1 AS "Aangemaakt",

          @!2 AS "Recruiter"

           

          and use your exclude rowno 1 to get a clean table.  How to get at paginated data in a table now that would be interesting.

           

          Hope this helps

           

          Regards

           

           

          Andy

            • Re: Use second row as header and delete first row
              Henke Willemsen

              Thanks Andy,

               

              This helps indeed, the only thing is that I manually have to create the AS "Aangemaakt", etc, while it is already in the loaded data .

               

              If this could be automated by some code, then this would save me a lot of (future) work, off course for only 7 columns this is fine.

               

              reg. Henke

                • Re: Use second row as header and delete first row
                  Andy Weir

                  Like you say its a one time issue on setup of your script.

                   

                  I think the issue they will have is HTML Table headers can contain a lot stuff not needed for the import.

                   

                  Mine for example cam through as

                   

                  [',25,document.getElementById('joblistgriduserhiddenfields').value,''); " class="tableheader"> CRM No] AS "CRM#",

                   

                  So tricky to automate for each site will be coded different.

                   

                  Please mark any replies that have been helpful/correct for other users of the community to follow.

                   

                  Regards

                   

                   

                  Andy

              • Re: Use second row as header and delete first row
                Henke Willemsen

                I will be using the URL's a lot and they will all be containing the same data as in the example. If someone would know the answer I would be very much ablied.

                • Re: Use second row as header and delete first row
                  Fredrik Folkeryd

                  Hi henkewillemsen! I came across the same situation and extended your initial solution a bit, combining it with a suggestion from The specified item was not found.. This way you don't have to create the aliases manually.

                   

                  My approach looks like this:

                   

                  //load the table from the web page, except the unwanted first row

                  tmpTable:

                  LOAD

                     *

                  FROM [lib://web_page_connector]

                  (html, utf8, no labels , table is @1)

                  where RecNo()>1;

                   

                  //iterate over values in the first loaded row (=the source table second row that contains
                  //the field names I want) to build a mapping table

                  for f = 1 to NoOfFields('tmpTable')

                    let vDisplay = FieldValue('@'&$(f),1);

                     

                      mapNames:

                      mapping load * inline [

                      TmpField,Display

                      "@$(f)",$(vDisplay)

                      ];

                  next f

                   

                  //rename the nameless fields using the mapping table

                  Rename Fields using mapNames;

                   

                  //prune the no longer needed first row from the table

                  NoConcatenate // needed to avoid this load to collapse into the already loaded table

                  BaseTable:

                  load * resident tmpTable where RecNo()>1; //prune first row, now used as field names

                   

                  Drop Table tmpTable;