How to match the values from excel

Help Robot
,
1

I have excel file with 2 columns with Name and abbreviation.

i will get input from outside as variable InputValue

example input value will be 245st street ABC District Court| here i want to replace ABC District court with JDC from the excel column 2

Name --district
Abbrevation-DC

like if the excel row matches with with input Value from excel in district column have to take the corresponse row value from the excel sheet from column2

below is the excel file sample
Col1 col2
|KENYA COURT|ABBREVIATION|
|ABC District Court|JDC|
|County Court Law No. #|CCL No. #|
|||FEDERAL COURT|ABBREVIATION|
|UK District Court|USDC|
|Germany Bankruptcy Court|GSBC|

2

Hi @T_Y_Raju ,
You can read 2 file to get data table
Use for loop to compare 2 data
If match , assign value then write back to file
Can you share input file and expect result , I will code for you
Regads,
LNV

3

@T_Y_Raju

You can try something like this

use if condition first to check if it is matching

dt.AsEnumerable.Any(function(x) inputValue.Contains(x))

on then side use inputValue.Replace(dt.AsEnumerable.Where(function(x) inputValue.Contains(x))(0)("Col1").ToString,dt.AsEnumerable.Where(function(x) inputValue.Contains(x))(0)("Col2").ToString)

cheers

4

You can perform the following,

InputValue.Replace(dt.AsEnumerable.Where(Function(x) InputValue.Contains(x("Name").ToString)).Select(Function(x)  x("Name").ToString).FirstOrDefault.ToString,dt.AsEnumerable.Where(Function(x) InputValue.Contains(x("Name").ToString)).Select(Function(x)  x("Abbreviation").ToString).FirstOrDefault.ToString)

dt is the datatable where all column values are stored

5

input value is 245st street ABC District Court

This is excel file
Col1 col2
|KENYA COURT|ABBREVIATION|
|ABC District Court|JDC|
|County Court Law No. #|CCL No. #|
|||FEDERAL COURT|ABBREVIATION|
|UK District Court|USDC|
|Germany Bankruptcy Court|GSBC|
[/quote]

6

Can you share input file and expect result

8

Hello @T_Y_Raju Can you try this

  1. Read Excel File:
  • Use the “Excel Application Scope” activity and specify the path to your Excel file.
  1. For Each Row:
  • Inside the “Excel Application Scope,” add a “For Each Row” activity to loop through each row of the Excel file.
  1. If Condition:
  • Within the “For Each Row” activity, add an “If” condition to check if the current row’s “Name” column matches your input value.
  1. Assign Activity:
  • Inside the “If” block, use an “Assign” activity to assign the value from the “Abbreviation” column of the current row to a variable.
  1. Replace Text:
  • Use the “Assign” activity to replace the matched part of the input value with the abbreviation obtained from the previous step.
  1. Outside For Each Row:
  • After the “For Each Row” activity, you can use the modified input value as needed in your workflow.

Thanks & Cheers!!!

9

Book1.xlsx (8.2 KB)

input value-261st Judicial District, Travis County, Texas

out put-261st JD, Travis County

10

Hey you can try the below XAML ,
Replacing_TextData.xaml (7.6 KB)

Input - 261st Judicial District Court, Travis County
Output -
image