Fetching Values from given text

[aronama]

Hi

I’m having trouble creating a bot that can analyze and extract values from text. I need a UiPath bot to check if there is any value present in the “Text” column, and if so, it should write that value in column D. If no value is found, the bot should write “not_found” in column D. The bot should loop through each row to perform this task.

I would greatly appreciate any help in creating this bot.

[singh_sumit (Sumit Singh Tariyal)]

Hie @aronama follow these steps for this automation
your input

read the data and save as for example - dt5

use for each row and inside pass if condition to check the column text is empty or not if it is empty then it goes in then or else goes else branch

pass this same as i pass in the image

and for increase cell so bot is able to write data into the next cell

create this index value in for each row
cheers happy automation

[singh_sumit (Sumit Singh Tariyal)]

@aronama mark this solution if you find it resolve your query so it help others too in the future if they stuck at the same conditon…
cheers Happy Automation

[SorenB]

Hello Aronama and welcome to the UiPath Forum

I would do something like this:

Main.xaml (11.1 KB)

[aronama]

Sure I’ll try your solution then will mark this question as solved

[aronama]

@SorenB Thank you so much, I’ll try your solution.

[aronama]

@SorenB Your solution is not writing value in expected output column.

[aronama]

@singh_sumit

can you please share the xaml file ?

I’m unable to understand it through screenshots.

[Varsha_PH]

Hi @aronama , follow these steps:

1. Create a List of Strings: Use an Assign activity to create a list of key values, e.g., keyValues = {"Everyday", "value2", "value3"}.

2. Read Range: Use Excel Application Scope and Read Range to load the data into a DataTable.

3. For Each Row: Use For Each Row to loop through the DataTable.

4. For Each Key Value: Inside the loop, use a nested For Each activity to iterate through the keyValues list.

5. If Condition: Use If row("Text").ToString.Contains(key).

6. Assign Activity:

   • If a key value is found: Assign row("Output") = key.
   • If no key value is found after looping through all keys: Assign row("Output") = "not_found".

7. Write Range: Use Write Range to save the updated DataTable back to Excel.

[singh_sumit (Sumit Singh Tariyal)]

@aronama actually this is my work pc so for some security reason i"m not able to send anything from that PC… you can send me message or screenshot if you stuck at any point
cheers Happy Automation

[UI_Michael (Michael Scheel)]

Hi,

read the values as Datatable with Read Range Activity.

After this you can use “For Each Row in Datatable”-Acitvity.

Simply check your Condition with “If” and concat the last column to your datatable with “Add Data Colum”.

After this you are able to use “Write Range” Acitivity and write your solution back to your file.

**NOTE: The combination of For Each and Write Range will be working much faster then printig each new cell-value into the final file. **

[SorenB]

Hello

Try again now. Save the Excelfile on your desktop.

Main.xaml (11.5 KB)
Excelfile.xlsx (8.3 KB)

I used ToLower for the check, so that it ignores case.

Regards
Soren

[mkankatala (Mahesh Kankatala)]

Hi @aronama

Simply you can use the LINQ Expressions to get the required output, Check the below steps,
→ Use the Read range workbook activity to read the excel and store in a datatable called DT.
→ Then use the assign activity to write the LINQ Expression,

- Assign -> DT = (From row In DT
                  Let ExpectedOutput = If(String.IsNullOrEmpty(row("Text").toString) OrElse String.IsNullOrWhiteSpace(row("Text").toString), "not_found", "Everyday")
                  Select DT.Clone.Rows.Add({row(0).toString, row(1).toString, row(2).toString, ExpectedOutput.ToString})
                 	      ).Copytodatatable()

→ Then use the write range workbook activity to write the DT to the same excel.

Check the below workflow for better understanding,

Output -

Hope it helps!!

[aronama]

I tried your solution but it’s only working for “Everyday” not for dynamic values, like instead of everyday there must be some different value in the text. but the not is giving everyday as output. Not giving not_found if there’s no value found

[mkankatala (Mahesh Kankatala)]

Could you please share the Input and Expected output data with us… @aronama

If the data is Confidential then share us the dummy data.

[aronama]

Yes here’s the dummy sheet.
with Expected output

I’m not able to upload dummy as I’m new user. So attached screenshot for your reference.
Consider column D for expected output.

[mkankatala (Mahesh Kankatala)]

Okay got it… @aronama

I written the LINQ Expression based on your Input and Output, Check the below LINQ Expression,

- Assign -> DT = (From row In DT
                  Let List_Values = row("Values").toString.Split(","c).Select(Function(x) x.toString.Trim).tolist()
                  Let ExpectedOutput = If(List_Values.any(Function(y) row("Text").toString.tolower.contains(y.toString.ToLower)), List_Values.where(Function(y) row("Text").toString.tolower.contains(y.toString.ToLower)).Select(Function(y) y.ToString).First.toString, "No_Values")
                  Select DT.Clone.Rows.Add({row(0).toString, row(1).toString, row(2).toString, ExpectedOutput})
                              ).Copytodatatable()

Check the below file Sheet1 has Input data and Sheet2 has output data,
expectedoutput.xlsx (10.2 KB)

Hope it helps!!

[singh_sumit (Sumit Singh Tariyal)]

Hie @aronama here i"m sending you the zip file unzip and use it
cheers Happy Automation
Fetching Values from given text.zip (3.2 KB)
Fetching values.xlsx (8.6 KB)

[aronama]

@mkankatala

Hi I have tried the recent solution it working fine but giving this result.

Regards
Naman

[mkankatala (Mahesh Kankatala)]

The values in the values column are not seperated with commas can you share us the original file… @aronama

The values in the values column will seperate with comma then it will work for sure.

Hope you understand!!