Iterate throw row in excel that contains number separated by space

Help Studio
, ,
1

I have a problem guys,

The idea of my automation to check these numbers one by one to my Masterfile and if it it’s exist in Masterfile I will delete the whole row that contained that number ?!

More Explanation

Iterate throw row in excel file that contains multiple numbers separate by space, I need to use for-each activity to check one by one ?

How I can do that ?

2

@Yasser_Ahmed_Baharetha
Hi Salam,
Do you want to check it in Specific Column?

3

yeah I want to check number in specific column in my masterfile

4

This is a screenshot of the row

Screenshot 11
Screenshot 111680×70 2.94 KB

5

@Yasser_Ahmed_Baharetha
one more question. Do you want to get these numbers or do you want to delete the row if their is multiple numbers present.

6

I want to delete the row from master file

7

@Yasser_Ahmed_Baharetha
pseudo Code


    - For Each (over Excel)
        - Assign: cellValue = row("ColumnName").ToString()

        - Assign: numbersArray = cellValue.Split({" "}, StringSplitOptions.RemoveEmptyEntries)

        -if (numbersArray.Count>1) then delete that row
8

Hi @Yasser_Ahmed_Baharetha

Read master file as Dt
For each row in excel:
    str_var1 = row("colName")
    assign: arr_var2 = str_var1.split(" ".tochararray)
    For each val in arr_var2:
        if (Dt.AsEnumerable().Any(Function(x) x("colName_MasterExcel").ToString = val)):
            use: Remove datarow activity(currentrow)

Hope it helps.

9

@Yasser_Ahmed_Baharetha

You can do below steps:

  1. Use Read Range activity to read you master data and save it to DT_1.
  2. Use “For Each row in Datatable” activity and pass DT_1 in it.
  3. Inside the loop use assign Activity and pass lv_Row_Value= Row(“ColumnName”.ToString.
  4. use another assign Activity and pass **lv_Arr_String= Lv_Row_Value.Split(" "c).ToArray
  5. Use For Each Activity and pass lv_Arr_String and Inside that use “IFstrong text” condition where you need to pass item.Tostring=Your_Number.Tostring and in then Use Delete Data Row Activity and give row as CurrentRow.

Hope this Helps!

10

Thanks Mr.Raja For Your Help,

I think I didn’t explain it very well , I have two excel file not one…

so I’ll share the file that I need to do the previous step and after that I’ll compare it with specific column in my master file.

10-07-2023_DuplicatedTenders.xlsx (7.2 KB)

I Appreciate all help guys , sorry I’m just new on UiPath…

11

@Yasser_Ahmed_Baharetha
Do you want to get these numbers and want to compare in master file write.

12

yeah, a just try and it’s work thank you ,

now I have array of these numbers one by one

13

I need just to compare each number with a column in master file and if it’s duplicated I’ll delete the row

14

@Yasser_Ahmed_Baharetha

For Each (over Excel)
        - Assign: cellValue = row("ColumnName").ToString()

        - Assign: numbersArray = cellValue.Split({" "}, StringSplitOptions.RemoveEmptyEntries)
        - For each val in arr_var2:
        if (MsterDt.AsEnumerable().Any(Function(x) x("colName_MasterExcel").ToString = val)):
Then
            use: Remove datarow activity(currentrow)
15

PrivateChrome.xaml (21.3 KB)

can you show me my mistake sir, I’m so sorry

Master File:
MasterFile.xlsx (34.7 KB)

16

@Yasser_Ahmed_Baharetha
I am not able to open it. Zip your whole folder and paste here.

image
image1483×843 67 KB

17

duplicateProject.zip (175.3 KB)

Sorry To be late sir

18

Check This Update Please, it’s only delete the first duplicated row when it’s go to the next row it’s show error…

duplicateProject.zip (193.3 KB)

I’m So Sorry I know I took so much time from you guys…

19

@Yasser_Ahmed_Baharetha Don’t worry let me check

20

@Yasser_Ahmed_Baharetha I solved this with Filter datatable. Let me share this