Need a solution in forming a properly aligned Output

Hey Experts,

I am facing a problem in data formatting while using "Text to Columns" tool

Input:

After applying "Text to Columns" tool with following Configuration

I got output like below

There is a small difference with what i expected, ie...

If you see T452909 value has been printed after Read Access, ideally it should be attached with values in "Access2" column

Expected Output should be looking like this

If you see now the value T452909 is appended next to the values( A242116, CHISGRP(G), CHIS01(S), CHIS02(S), CHIS04, T452909) in "Access2" column...

This should work as Dynamic,, the extra row can appear after Read Access or Update Access or Control Access rows but it should be attached to the proper "Access2" rows always...

Thanks in Advance.....

PS: Input file been attached..

Book1.xlsx

Workflow

Developer

Input

Regex

Output

Parse

Join

Data Investigation

Accepted answers

Matt_D

How about this? Should capture the 100 users scenario if you get more than 1 line break.

Properly Aligned Output.yxzp

All comments

CharlieS

Hmmm, I think the best course of action would be to address that line break before the Text to Columns.

Could you share that example in text instead of an image for the Community to take a crack at?

Matt_D

Hi @GouthamVijay

What is the actual input? .csv, .xlsx? A screenshot doesn't help (as it cannot be copy and pasted) and to build a workflow becomes a pain as we have to build the input manually. 🙃 Can the input be provided as is without any manual changes?

First suggestion is to use a multirow formula tool and look for rows containing : that end with , and then concatenate the next row on the end of the current row. If that next row is now contained within the one above, return nothing else return the row. Should cleanse your data for the text to columns tool.

I would like to know what happens if you give 100 users Read Access, do you get many rows?

DataNath

Does this work for you @GouthamVijay? It basically appends the following row if either of those fields you mentioned (X Access) are missing so then it appears in the correct access2 row. You're still left with the code in Access 1 after the split to column and so I've added an extra formula tool to clean that up. Any issues let me know and I can revisit!