"The cells in the following Excel sheet is of kv (key=value) forma, and a key could appear more t .."

blackduckie RaqForum 28 No.
1 Reply • 406 View • 3 Years ago

How to Parse KV Format（key=value) Excel Cells

excel(115) key values(1)

The cells in the following Excel sheet is of kv (key=value) forma, and a key could appear more than once in a same row, as shown below.

	A	B	C	D	E
1	A=123	B=456	C=789	A=INV	Y=67
2	A=678	B=783	A=900	U=89
3	B=890	C=PO	F=09

Task: Find values whose keys are A, join them up with comma, and list them in the 6^th column, as shown below:

123,INV

678,900

There are not Excel functions that specially handle kv format data. Though you can piece together a formula using certain functions, it is rather hard to understand.

Directions:

1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).

2. Open the to-be-computed file in Excel, select the area of A1:E3, and press Ctrl+C to paste it to clipboard.

3. Open esProc, select cell A1, and press Ctrl+V to paste data from the clipboard to A1. Make sure that the caret is placed above A1 in its editing status during the pasting.

undefined

4. Go on writing script in esProc:

	A
1	…(Data pasted from the clipboard）
2	=A1.import@t()
3	=A2.(~.array().(~.property("A")).select(~).concat@c())

The script function property is specifically used to query kv format data, and you can use key as an input parameter.

5. Press F9 to execute the SQL script. Then you can click A3 to view the result in the value view section on the right. Click the “Copy data” button on the right-hand to paste A3’s result to clipboard.

undefined