How to create a table from key value pairs which are not always present?

[MarietaO]

Hi everyone,

I would like to extract a table. For instance:

SOH is a special character

Input

id=1, message body= [35=DSOH45=CSOH]

id= 2, message body= [35=FSOH53=ASOH45=CSOH]

+10k messages as such

Result

35  45 53

D    C    

F     C   A

Create headers with all keys provided through all messages.

If key is not provided on message then cell should be empty.

As you can see 53 is not always present, when that is the case its cell is empty.

Thanks in advance.

[MarietaO]

Works, thanks a lot. And if I want to short by numeric values the columns e.g. asc.

[ITWhisperer]

Can you give an example of what you have and what you would like it to be?

[MarietaO]

Sure, thanks a lot.

k1=1, k2=3,body=[8=FIX.4.1
9=90
35=0
49=INVMGR
56=BRKR
]

k1=1, k2=3, body=[8=FIX.4.1
1=10
35=0
49=INVMGR
58=BR
]

many key value pairs in body (not known).

order is numeric on columns.

To export to csv as a table.

1	9	35	49	56	58
	90	0	INVMGR	BRKR
10		0	INVMGR		BR

[ITWhisperer · Accepted solution]

| transpose 0 header_field=key column_name=key
| sort 0 key
| transpose 0 header_field=key column_name=key
| fields - key

[MarietaO]

works perfectly thanks.

[ITWhisperer]

| rex "35=(?<f35>\w+)SOH"
| rex "45=(?<f45>\w+)SOH"
| rex "53=(?<f53>\w+)SOH"

[MarietaO]

e.g. 8=FIX.4.1
9=90
35=0
49=INVMGR
56=BRKR

[MarietaO]

The contents of the body field are not indexed. SOH is an invisible character in ASCII.

SOH

The unique keys  are unknown.

Body is indexed but not the key value pairs that I would like to transform to a table.

[ITWhisperer]

| rex max_match=0 "(?<_name>\d+)=(?<_value>\w+)\cA"
| eval namevalue=mvzip(_name, _value)
| mvexpand namevalue
| eval _name=mvindex(split(namevalue,","),0)
| eval _value=mvindex(split(namevalue,","),1)
| eval {_name}=_value
| fields - namevalue
| stats values(*) as * by _raw