How to campare values from input value to excel sheets

Help Automation Ops
,
1

Hi Team i have 2 excel sheets with me
and i have input variable as “721st Judicial District Court, Travis County, Texas”

here in these 2 excel i want to replace Judicial District Court with JDC from Excel sheet
and in the same way i want to replace Texas with TX from States-abbreviations excel sheet. i want to check all the values in excel in both the sheets

here input variable value will be changed everytime. and that value should be checked in excel sheets
Address.xlsx (9.1 KB)
States-abbreviations.xlsx (9.3 KB)

2

Hi @T_Y_Raju

  1. Read Excel Sheets:
    Use the ‘Excel Application Scope’ activity to read both Excel sheets into DataTable variables. Let’s call them dataTable1 for the first sheet and dataTable2 for the second sheet.
  2. Define Input Variable:
    You mentioned that the input variable value changes every time. Let’s assume you have a string variable named inputVariable that holds the value “721st Judicial District Court, Travis County, Texas” or any other value.
  3. Perform Replacements:
    Use LINQ queries to perform the replacements on both DataTables. Here’s an example LINQ query for the first DataTable (dataTable1):
   dataTable1.AsEnumerable().ToList().ForEach(Sub(row) 
       row("Column Name") = row("Column Name").ToString().Replace("Judicial District Court", "JDC")
       row("Column Name") = row("Column Name").ToString().Replace("Texas", "TX")
   End Sub)

Note: Replace "Column Name" with the actual column name where you want to perform the replacements.

Similarly, for the second DataTable (dataTable2):

   dataTable2.AsEnumerable().ToList().ForEach(Sub(row)
       row("Column Name") = row("Column Name").ToString().Replace("Texas", "TX")
   End Sub)
  1. Write Back to Excel:
    After performing the replacements, use the ‘Write Range’ activity to write the updated DataTables back to the respective Excel sheets.

Hope it helps!!

3

can u please send the work flow if possible

4

its giving error

image
image808×374 11.6 KB

here in the replace i want to pass the dynamic values

5

is there any other method to do?

6

Hi @T_Y_Raju ,
I see them get only uppercase
that’s right?
you can use regex

7

how do i do this in for each row in datatable

8

any update how to go further on this

9

hi can u send the work flow if possible

10

this is quite difficult any other method of doing this or can u share the work flow of the same