Problem description

Here is an Excel file that has a 4-field data table where the fields contain different data. ID field contains values of {key}value structure, as shown below:

1. Get values from each row of ID whose keys are S1, S6, and S10 and add them after corresponding TEAMS field value; 2. Replace the asterisk (*) connector in each NUM value with the comma (,) and add the before each DETAILS field value. Below shows the source data and the desired result, where the contents highlighted in red are data before and after the arrangement:

Directions

1.   Select area A1:D3 in the Excel file and press Ctrl+C to copy it.

2.   Back to esProc to paste the data to cell A1.

3.   Write the following code to do the task:

A2   Import A1’s text data as a table sequence and use @t option to read the first row as field names.

A3   Remove the left braces from the content of ID field and put the data to a new KV field.

A4   Replace the right braces in A3’s data with the equal sign = to arrange the KV field content into the standard Key=value structure.

A5   Use property function to get key values from each standard Key=value string and add them after TEAMS field values. The first step of data arrangement is thus finished.

A6   Replace connectors in NUM field values with commas and put them to corresponding DETAILS values. The second step of data arrangement is thus finished.

A7   Delete the temporary field KV during the table sequence arrangement.

4.     After the code is executed, copy A7’s content to paste it to A8 in Excel.

Q & A Collection

https://stackoverflow.com/questions/61716625/copy-modify-the-copied-content-and-paste