Excel splitting a column value to multiple columns

[bhishaj]

There is an excel with a few columns. However, columns of interest are Column A and H only.
A new excel needs to be created by splitting/extracting the necessary data from column H of the source file to various new columns.

The data (Columns A and H) in source_file

ID	Description
123	Access has been Approved for LAST_NAME, FIRST_NAME (userID) EMAIL_id: Application: XYZ Role: ROLE_1 Envir: PROD Attributes: ONE TWO THREE PERCENT CAPACITY 100 SHARED RESOURCE YES RESOURCE POSITION ROLE RP1 Business Reason: Some reason. Some other name Data Owner Other role Primary not required Primary
456	Access has been Approved for LAST_NAME2, FIRST_NAME2 (userID2) EMAIL2_id: Application: XYZ2 Role: ROLE_2 Envir: PROD Attributes: FOUR FIVE SIX PERCENT CAPACITY 80 SHARED RESOURCE NO RESOURCE POSITION ROLE RP2 Business Reason: Some reason. Some other name Data Owner Other role Primary not required Primary

and the format in which the data is required as

ID	Email	Network ID	Security Role	Capacity	Resource Position Role	Shared	Name
123	EMAIL_id	userID	ROLE_1	100	RP1	YES	LAST_NAME, FIRST_NAME
456	EMAIL2_id	userID2	ROLE_2	80	RP2	NO	LAST_NAME2, FIRST_NAME2

I’ve tried to do something but it does not seem to be a correct approach.

1. Used Read Range Activity to read all the contents of source file.
2. Filter Data Table to get the 1st and 8th column. Store the resultant to a datatable - filtered_table
3. Assign activity → operation_table = filtered_table.Clone
4. ForEach Row in filtered_table
   a) dtRow = operation_table.NewRow
   b) name = Split(row(2).toString, " ")(0) – Is this correct?
   c) Email…all other columns mentioned above… How do I split them?
   d)
   e)
   f)
   g) Add Data Row – dtRow to operation_table
5. Write to excel

[ppr (Peter Preuss)]

@bhishaj
Welcome to the forum

give a try on doing the extraction with the help of regex e.g.

etc.

Also have a look here:

Regex help tutorial MEGAPOST – Making your first Regex post, Reusable Regex Patterns, Regex Troubleshooting, Sample Workflow and more

THE PURPOSE OF THIS POST IS TO HELP NEW REGEX USERS If you need help with Regex be sure to provide in your post, a Sample, the Output and the Pattern (as much as you know). CONTENTS: Section : I need a Regex Pattern ASAP! - My first Regex Post Section : Regex samples that you can use! Section : I need help applying regex in UiPath Section : Regex Matches and Groups Section : Le…

[bhishaj]

Main.xaml (10.8 KB)

@ppr

Thank you for this. It helps a lot! However, I’m still having a few issues to get the value from regex assigned to a variable - I may be doing it all wrong . If possible, could you kindly help me with the error in attached file?

[ppr (Peter Preuss)]

due version missmatches I was forced to open the xaml on xml view.
it looks like you are doing following:

for each row… loop var: row

• using a match activtiy:

for the input of the match activity pass: row (YourColNameOrIndex).toString