topic Re: Use second row as header and delete first row in Connectivity & Data Prep

Use second row as header and delete first row

Tue, 08 Sep 2015 11:35:03 GMT

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,

FROM [lib://Applicants]

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

WHERE RecNo () <>1;

gives:

Does anyone know the answer?

Re: Use second row as header and delete first row

ogster1974 — Tue, 08 Sep 2015 12:19:09 GMT

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

Tue, 08 Sep 2015 12:23:19 GMT

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

ogster1974 — Tue, 08 Sep 2015 12:31:48 GMT

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

Wed, 09 Sep 2015 07:42:33 GMT

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

ffd — Tue, 27 Sep 2016 21:01:41 GMT

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;