After we’ve made the Regex expression ready, we can then put it into a Regex Tool. Since the result is just a sequence of Strings, the easiest way to store them is by doing a Tokenize action and split the different string into rows:
As we can see here, the name/value pairs are stacked all in one column. The next thing to do is to rearrange them into two different columns, named “name” and “value” respectively. To do this, a crosstab action is needed. But before we can do that, we need to prepare some extra columns first.
The first new column we need is the row count. There are a few different ways to do that, here the easiest one is just use the Record ID tool.
After we got the row counts, we need to do a further tweak here to make each name/value pair to have a same number. The reason for this is to get a group by column ready for the crosstab step later. So the next tool is a Multi-Row Formula and the formula for doing this is shown below:
The next step is to get the column headers ready for the crosstab by adding another new column. What we need to do is to set the value of this new column to “name” for each name row, and set its value to “value” for each value row. This is also done by using a Multi-Row Formula tool. The formula is based on the original row count column, as shown below:
Now the output from the workflow is like this:
The steps thereafter are straight forward. The crosstab action is ready, so just do it next. Followed by some adjustments to the Null value and a bit of formatting, then the expected output of the workflow is there.
