How to merge row n with row n+1

Help Studio
, ,
1

I have one json result like this:

{"Beleg":"A1","Belegtitle":"Steinberg chrom","Lieferdatum":"","AllData":"","Auftrag  ":"101"}

{"Beleg":"","Belegtitle":"","Lieferdatum":"voraussichtliches Lieferdatum:  28.10.2021","AllData":"aa","Auftrag  ":"101"}

{"Beleg":"33","Belegtitle":"La chrom","Lieferdatum":"","AllData":"","Auftrag  ":"101"}

{"Beleg":"","Belegtitle":"","Lieferdatum":"voraussichtliches Lieferdatum:  28.10.2021","AllData":"aa","Auftrag  ":"101"}

I want to create for all rows a merge of row 1 with 2, row 3 with 4 and so on.

Result should be :

{"Beleg":"A1","Belegtitle":"Steinberg chrom","Lieferdatum":"28.10.2021","AllData":"aa","Auftrag  ":"101"}


{"Beleg":"33","Belegtitle":"La chrom","Lieferdatum":"voraussichtliches Lieferdatum:  28.10.2021","AllData":"aa","Auftrag  ":"101"}
2

@stela_zh
May we ask you on how to receive the different JSON lines (e.g. text file, Api calls)?

in general, we would work with the JSON Api from Newtonsoft package.

Variables:

grafik
grafik813×156 39.6 KB

Preparation:
grafik
replace empty string with null
strJSON.Replace("""""","null")

Set Merge Settings:
MergeNullValueHandling.Ignore

Split each textline and parse it to a JObject:

(From x In strJSON.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)
Let jo = JObject.Parse(x)
Select v=jo).toArray

grafik
grafik398×653 35.7 KB

Iterate over each second object (starting first object)
Merge the two JObjects with the Merge Function
Add it to a list
Prepare Final Output string:

grafik
grafik677×504 87.8 KB

find starter help here:
MergeFirstJObjectWithSecond.xaml (9.8 KB)

data.json (417 Bytes)

1 Like
3

@ppr thank you for your help.

Don’t know for what reason is not working in my template.
I am scraping the data using data scraping

image

This is how metadata looks :

image
image986×743 42.6 KB

My json is a string generated for each reference :

image
image541×685 14.5 KB

Can’t understand what is not working in this ( your file with the json data file was working in my project).
I tested to remove title and to let other information, but again wasn’t working.
Please do you have another idea?

4

This is how it looks extract wizard:

image
image834×838 19.2 KB

Normally there are more 100 rows, so i want to merge row 1 with 2, row 3 with 4, row 4 with 5 and so on

5

@stela_zh

kindly note: when setting the maximum of results to 0 it will take all. Just check in your implementation if a limit was set

In your request, you provided a JSON. But in the posts, you are showing data scrapping which results to a data table. Any reasons for this difference: JSON vs. DT?

6

You’re a boss, Peter!

7

Thank you for your reply.
I was trying to simply my problem…
but now can’t put all data from datatable to a json file and to continue with your logic.

I am scraping all the data, can see them…just wasn’t able to put all them in json file and to continue your logic.

I tried a mode, but only the last string of json was stored.

Please do you have any other suggestions.
Thank you for your time

8

we also can do the merge on the n & n+1 rows implemented on datarow base

9

How can i do that, do you have one example please?

10

@stela_zh

find an anproach on:

  • doing a merge of n and n+1 row
  • handling a take from first when botw rows doe have a value
  • use nothing when both rows do not have a value

grafik

find starter help here:
MergeNonEmptyValues_RowNWithNextRow.xaml (12.1 KB)

find some introductions to the used concepts here:

11

can you please help me to find the solution for this kind of excel:

This is the csv file, where i want to merge row n with row n+1
image

image
image297×631 17.4 KB

MergeNonEmptyValues_RowNWithNextRow.xaml (9.3 KB)

Don’t know why, for what reason file generated after merge, is with not correct and sime rows are dublicated.
Please can you find what is not working here?

12

please open your own topic on your specific case. Researchers can easier find solutions when threads a clear scoped and seperated. Thanks