Splitting Delimited Data from a Single Cell into Multiple Related Rows

Help! We have a need to manipulate some input data into related rows and columns. We've exhausted our attempts with the 'text to column' but we seem to reach a dead-end achieving the desired outcome.

The input file is attached. It is a series of database tables and associated fieldnames. Obviously the pipe is a delimiter. In the first row 'field one' in the 'Fields Used' column matches to 'table one' in the 'Table Used' column; 'field two' to 'table two' and so on.

In the second row, the same thing. One wrinkle though; we have a second delimiter - a blank space - which is really two fields related to a single table. Example, what appears to be 'EFFDT EFFSEQ' is two separate fields related to the PS_JOB in the 'Table Used' column.

The expected outcome is in the attached file, also.

I hope this makes sense.

Any assistance would be greatly appreciated.

Cheers,

John

TestSplitRows.xlsx

Datasets

Input

Output

Parse

Preparation

Accepted answers

mceleavey

Hi @John_Lovelace ,

this is a simple matter of splitting each column to rows based on the delimiter and smooshing back together on record position:

Workflow attached.

Hope this helps.

solution.yxmd

All comments

mceleavey

Hi @John_Lovelace ,

this is a simple matter of splitting each column to rows based on the delimiter and smooshing back together on record position:

Workflow attached.

Hope this helps.

solution.yxmd

JBLove

@John_Lovelace

For the Field Used column that has your double delimiter I would consider using two text to column tools. The first parses out the pipe and the second parses out the space. You could then use a Transpose tool to get those columns generated by the space delimiter in a single column.

Good luck!

RaviP

Hi @John_Lovelace

Hope the attached flow works

SplitRows.yxmd

apathetichell

note - i pasted in your input instead of using the excel.

tableused.yxmd

John_Lovelace

Hello @mceleavey Our developer was almost there, but your assistance helped him get to the goal line! Thank you!

PS: First time user of the Community - this is great!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703