Upland OL User community

Extract into detailed table with javascript

#1

Hello,
i would like to extract some data with a script into a detailed table.

the raw data looks like the following example.

Positionen=957126 USA 1$ Indians Space Programm 2019 pfr. 20 1 1,6500 33,00
Positionen=
Positionen=829268 Litauen 2€ 30 Jahre EU-Flagge 2015 pfr 50 1 2,8000 140,00
Positionen=
Positionen=829055 Finnland 2€ Jean Sibelius 2015 pfr. 25 1 3,0000 75,00
Positionen=
…

The spaces between does not always have the same length, or width.

My idea is the following:
Get the rows behind “Positionen=” and then split this data with split(’ ').
The rows with only Positionen= would be ignored.
The resulting array from split would be added to the detailed table.
This is my idea so far, the question is, how do I implement this.
I thought about an extraction step, which marks the correct line (this realized in the datamapper).

But how do I extract the data from this line with javascript to a detailed table?

Many thanks for your answers.
NBecker

#2

Create a loop that iterates on all the lines. Inside the loop, add an Action step that runs a script similar to this:

let oneLine=data.extract(12,1000,0,1,"").trim();
let elements = oneLine.split(" ");
let length = elements.length;
if(length>1){
	let myRecord={
		val1 : elements[0],
		val2 : elements[1],
		val3 : elements[2],
		val4 : elements.slice(3,length-6).join(" "),
		val5 : elements[length-6],
		val6 : elements[length-5],
		val7 : elements[length-4],
		val8 : elements[length-3],
		val9 : elements[length-2],
		val10: elements[length-1]
	}
	record.tables.positionen.addRow(myRecord)
}

In this example, the detail table is named positionen and it contains 10 fields. Field val4 is special because it concatenates several elements into one field. So in the first detail record, it contains “Indians Space Programm”, in the second, “30 Jahre-EU Flagge” and in the third “Jean Sibelius”. You will obviously have to adapt it to your own needs.

#3

Hi @NBecker,

Can you let us know please if having a dynamic number of (detail table) record fields does not matter? Because please keep in mind that after executing the JavaScript code “split(" ");” the given example will result in two detail table records with 12 record fields and one detail table record with 11 record fields.

#4

@Marten: I amended my script to handle variable length elements, which appear to only occur for the fourth field value.

1 Like
#5

Thanks for the code snippet.

The loop in the datamapper looks like in the following screenshot:

Extraction
Extraction.jpg1875×982 411 KB

Now I want to try to extract the green marked lines, with the data.extract function.

When I use the code:
let oneLine=data.extract(11,100,0,1,"").trim();
let elements = oneLine.split(" ");
let length = elements.length;

if (length > 1)
{
	let myRecord = {
		val1 : elements[0],
		val2 : elements[1],
		var3 : elements[2]		
	}
	record.tables.Artikel.addRow(myRecord);
}

I got an error message: Cannot call property addRow in object Array (0 items). It is not a function, it is “object”.

It looks like that there was no data found in the line.

#6

Hi @NBecker,

Please make sure that your Data Model contains both the detail table “Artikel” and the record fields “val1”, “val2” and “var3”.

Notes:

  • You can add a detail table to your Data Model by right clicking in the Data Model pane and selecting the option “Add Table…”.
  • You can add a record field to a existing detail table by right clicking on the name of the detail table in the Data Model pane and selecting the option “Add Field…”.