Excel activity in UiPath

Help Activities
, ,
1

Dear Concern,

I faced challenges in UiPath excel automation. here are my problem statements which are related to the excel attachment:

  1. In attached file “Route” Column (B column) has a statement, which need to be split into 2 columns (should be separated by either “ - ” or “ to ”). Usually we use “Text to column” option in excel to do this split. How can I split it in UiPath?

  2. “In J column” , End customer name is mentioned either “ End Customer: BDCOM” or “ End User: BBTS. Ref CLTS ID: 2068. Already Commissioned.” How can I separate only “BDCOM” or “BBTS” from these 2 statements in UiPath?

Many thanks for your support.
Extract Data V.2.xlsx (12.9 KB)

2

Hi @Sojol_Hamid ,

  1. Splitting Route: Ypu can check in of condition for conatins(“-”) . If true split by “-” else split by “to”
    I’ve attached the sample workflow for you.
    SplitExcelVal.xaml (8.7 KB)

  2. Sorry i’m not clear about your requirement. In J column there is mix of data. You want to get only “BDCOM” or “BBTS”? if yes again you can check with if condition for contains.

3

Many thanks @manjula_rajendran for your help. I tried to understand your solution of my first problem. How can I write the output of the “split” function in excel. You used same variable “Loc” for splitting “-” & “or”. will it conflict during output? How can I get the split output in “Part A” & “Part B” column after splitting. Please help.

Regarding 2nd option, “BDCOM” or “BBTS” is not a constant term. these 2 are customer’s name. This name can be changed like “ABC” or “XYZ”. I want to take only customer’s name (example: BDCOM or BBTS) & discard rest of the items. It can be done in excel by “find & replace” option but how can I do it in UiPath.

Once again Many many thanks! Kudos!!

4

Hi @Sojol_Hamid ,

In excel what you would “find and replace”. Example please.

Some options which you can use here is

ex:
text = “End Customer: BDCOM”
text.split(":"c)(1)
text.replace(“End Customer:”, “”).trim

5

@manjula_rajendran Hello Sir. Many thanks for your help. I can’t get output of your prepared “splitExcelVal” Uipath project. How can i get my desired output by splitting one column text into right sided 2 added columns. Please help.

6

Is it possible to share your excel? and sample of your desired output?

7

Dear @manjula_rajendran , please find the attached file. I want to split A column (Route) into two parts in “Part A” & “Part B”. It will be parted just at “-” & “to” portion. Could you please help me now.
Text to Column(Delimited).xlsx (9.9 KB)

8

Hi @Sojol_Hamid ,

Here is the workflow for split columns and writing into excel.
Input data
Extract Data V.2.xlsx (12.9 KB)

Output data
ExtractedData.xlsx (11.5 KB)

workflow
SplitExcelVal1.xaml (13.4 KB)

9

Many thanks for your great work!!

10

If it is solved please mark it as solution!

Thanks,
Manjula

11

Sure brother. I didn’t check it yet.

12

WoW! It works!! Many Thanks @manjula_rajendran. You saved a lot of my time & effort. basically, i am quiet new in this domain & i am not so good in VB.net commands. Thanks for your excellent support. Kudos!!