Excel data Manipulations

Help Studio
1

Hello Friends..
Plz Guide

I have two excel A & B Having bank statement data with multiple rows & Column

Requirements

1st Excel A : Read Column name :“Description/B” Check for word "DR & CR "
& add extra column name “Status” and mention status like Outgoing for DR & Incoming for CR

Excel A

image
image1627×648 26.4 KB

2nd : Excel B : Read Column name “Description” for Every Check No like : “CHQ#541114897” and look for match in Excel A : Column name :“Description/B” IF we found matching check No in both Excel A & B
then add status : Match by adding extra new "Status "column in Excel B

Excel B :

image
image1627×648 28.7 KB

Forum Engagement Daily Reports
2

Hi @Bot_Learner1

Excel A (DR/CR status):

  • Read Excel A into a DataTable (dtA).

  • Add a new column “Status.”

  • For Each Row in dtA:

  • If row("Description/B").ToString.Contains("DR") → row("Status") = "Outgoing".

  • If row("Description/B").ToString.Contains("CR") → row("Status") = "Incoming".

Excel B (Check number match):

  • Read Excel B into a DataTable (dtB).

  • Add a new column “Status.”

  • For Each Row in dtB:

  • Extract the check number from row("Description").

  • Compare with Excel A:

    • If any rowA("Description/B") contains the same check number, set rowB("Status") = "Match".

  • You can do this either with LINQ or nested For Each Row loops.

Write Back:

  • Use Write Range to save both updated DataTables back to Excel A and B.

Generated by LLM
Happy Automation

3

thanks for response Prashant

For LINQ do we need Database like MSSQL as i don’t have at …need to do using excel only …if not then how to write LINQ please let me know if you u can bro

4

No database is needed. LINQ in UiPath works directly on the DataTables you read from Excel.

Change accordingly…
Example:

For Each rowB In dtB.AsEnumerable()
    Dim checkNo = rowB("Description").ToString
    If dtA.AsEnumerable().
        Any(Function(r) r("Description/B").ToString.Contains(checkNo)) Then
        rowB("Status") = "Match"
    End If
Next

So you can query Excel data with LINQ without MSSQL.

Happy Automation

1 Like
Forum Engagement Daily Reports
6

thanks for responding bro…

Could you please let me know ..which activity i need to use below to write above LINQ ?

image
image732×681 22 KB

7

LINQ is just VB.NET code you place inside Assign activity.

For more :slight_smile:

8

thanks

bro do u have any idea on below issue ?

image
image1445×814 56.6 KB

image
image1449×497 53.3 KB

9

Try :slight_smile:

The error occurs because DT1 or some “Description” values are null-

DT1 IsNot Nothing AndAlso DT1.AsEnumerable().Any(Function(r) Not IsDBNull(r(“Description”)) AndAlso r(“Description”).ToString.Contains(CheckNo))

1 Like
10

Thanks @prashant1603765 for Help & Support U got talent bro…!

1 Like
Forum Engagement Daily Reports
11

Hello @prashant1603765 Write range back to excel changes my excel layout (excel has 4 rows above headers) is there any other way to update , Excel B : ColumnName : CheckNoStatus , Cells values for each row like Match/NotMatch

image
image762×406 19.6 KB

Help Help > Studio Help > Activities