Use second row as header and delete first row

[#1]

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.

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:

Does anyone know the answer?

[ogster1974]

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

[#3]

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

[ogster1974]

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

[#5]

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.

[ffd]

Hi henkewillemsen‌! I came across the same situation and extended your initial solution a bit, combining it with a suggestion from Creating a Table of all Field Names. 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;