Regular Expression in a column of datatable, split it and explode rows

Help Forum
, , ,
1

Hello Developers,

I am new to uipath and try to convert python code related to datatable into uipath. Please help me to get this task done

I have developed a workflow for Excel automation. I am stuck in between a point during this automation. I want to apply regular Expression in a column of a datatable. Tried many ways but not able to produce exactly desired result as I wants. I have datatable like Input Data sheet in attached excel

This is sample datatable and this has large number of columns. I want to apply regular expression in column C named as ID column. I want to split data in this datatable based upon $, & separator but also wants to ignore(delete) all the values between * and & or * and $. Row where we find empty cell in column C(ID) can be deleted or ignore.

Output Data sheet of attached excel contains output that I want.

Data.xlsx (10.0 KB)

I have developed it in python and now I want to convert it into uipath. Below is python code of solution of above problem-

import pandas as pd
df = pd.DataFrame({'Order': ['10-112','10-115'], 'Owner':['shubhman', 'rishab'], 'ID':['89ab$cd&78','']})

df['ID'] = df['ID'].str.replace(r'\*[^&$]*[&$]', '').str.split(r'[$&]') # Remove substrings between * and $ or &
df = df.explode('ID') # Split the rows with multiple IDs into multiple rows
df = df[df['ID'].astype(bool)] # Discard the rows with an empty ID

Could you please help me regarding my problem with workflow

2

Hi @Vishav_Gupta … do you want to write the output in the same column or in the new column?

Also could you please share the expected output with one sample data …?

3

@prasath17 I have attached excel file in the post. You can check that here. In Input Data sheet of excel I have mentioned input data and in output data sheet of this excel I have write output data. You can check it. Thanks for your help in advance
Data.xlsx (10.0 KB)

4

I want output in the same column. as you can see with attached output in Excel. Please let me know if some other information is required from my side

5

Hi @Vishav_Gupta

For the input IDs 112abc*28&ab, 389&ab*3$8 the output obtained from your python code does not match with your output. Please check and confirm.

image
image1135×502 22.4 KB

The same is the case with UiPath

image

6

@kumar.varun2 Thank you very much for reply. Python code is producing correct output as I want. You can check output data sheet in attached excel. I can feel what you are thinking. Trying to explain. For 112abc*28&ab, 389&ab*3$8 I want to ignore also character between * and $ or * and & parallel with splitting of data. That’s why it is not considering 28 and 3 value in final output after splitting. I will be very happy if you can help me to produce required output in uipath. Thanks for your help
Data.xlsx (10.1 KB)

7

Hello @Vishav_Gupta

Check this xaml file:
Explode.xaml (10.4 KB)

The code can further be optimized using LINQ

Hope it helps!

Regards
Varun Kumar

8

@Vishav_Gupta

After optimization through LINQ

Explode.xaml (8.1 KB)

9

@Vishav_Gupta

Is your problem solved?

10

@kumar.varun2 thank you very much for your solution. I forget to mark your answer as solution. I really appreciate your help and my problem is solved. Could you please suggest on this Append datatable in Excel and keep base formatting of final Excel

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.