Splitting Delimited Data from a Single Cell into Multiple Related Rows

[John_Lovelace]

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

[mceleavey · Accepted solution]

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.

M.

[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

[apathetichell]

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

[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!!

[mceleavey]

And welcome to the community!

We're always here if you need anything else.

M.