Comapare the File path in a directory with the input value from the Excel row

Help Studio
, ,
1

Hi All,

Could any one kindly help me with the below issue.

I have a file path location with contains all the file path of my requirement.

I wan to compare this each file path with the all ID values in the Excel.

For example my file path is : "“C:\Users\Srinivas\Documents\3465_inputfile.pdf”

I want to compare ID value(3465) with the all the Emp Id available in the Excel and has to update the status as yes in the respective Status column as shown in the below screen shot

image

Kindly help me on this issue.

Thanks

1 Like
2

@srinusoft37

It is better you get the is from excel and then check if file is present…you can follow the steps

  1. Use for each row in excel activity…
  2. Inside the loop use if condition with directory.GetFiles("FolderPath",currentRow.ByField("Emp ID").Value + "*").Count>0
  3. On the then side use assign activity with currentRow.ByField("Status") = "Yes"

If you want the way from files only then

  1. For each file in folder and give the folder pth
  2. Inside the loop use lookup activity and give value as currentFile.Name.Split({"_"},StringSplitOptions.None)(0)
  3. If a value is returned then found …else not found…IsNothing(ValueVar) can be used as condition
  4. Now on then side use the output recieved to update the cel using write cell activity

Hope this helps

Cheers

3

Hi @srinusoft37

=> Take an assign activity and store the File path in a array of string variable called FilePath.

Assign -> FilePath = Directory.Getfiles("Folder Path")

=> Take the Use Excel file activity and give the path of the file.
=> Inside use Excel file insert the for each Excel row activity. Output - CurrentRow
=> Inside for each Excel row activity insert the for each to iterate the FilePath (Array of String variable) Output - CurrentItem

- Condition -> CurrentItem.Contains(CurrentRow("EMP ID").toString)

=> In then block insert the Write cell activity
→ What to write : “Yes”
→ Where to write : CurrentRow.Byfield(“Status”)
→ Check the Auto increment option.

Hope it helps!!

1 Like
4

Thanks for the Quick Reply, In my directory I have more than 1 Files for example

C:\Users\Srinivas\Documents\3465_inputfile1.pdf
C:\Users\Srinivas\Documents\3465_inputfile2.pdf
C:\Users\Srinivas\Documents\3465_inputfile3.pdf so on,

Here i need to verify the First input file path value to the all the excel row values and have to update the status as Yes wit hte respective match value.

Thanks

5

Okay @srinusoft37

I understand you have the files in a folder and you want to iterate the filepaths and compare with the Excel column.

If it was the case, I have update the above solution please check it now.

Hope you understand!!

6

Ya understood and try to build the same, but this is the error

image
image1059×450 19.6 KB

Actually my issue is failing to update the respective column in the excel. I am unable to update with the respective status column values as yes in the Excel.

please find the attached Xmal

test.xaml (12.4 KB)

Thanks in advance

7

Are you using the use Excel file activities and all that I have given the process as above

If yes in the place of write cell workbook use the write cell activity which is the excel activity.

Hope you understand!!

8

@srinusoft37

If you want to use write cell work boom then…first assign a variable to index property in for loop…and then use that in the cell field

"B" + (index+2).ToString

Cheers

9

I am using the work book activities, and unable to identify the issue

Could you please help me by editing with the Flow, which I shared.

I am missing some thing which is unable to find the issue.

Kindly help me

Thanks

10

I recommend to use the Excel activities which are available in modern activities.

Or in the range of write cell workbook activity give like this “A”+(Index+2).toString.

In the place of A change the column which has Status.

Okay I will change the things in your code and repost. @srinusoft37

11

Thanks for the Quick reply, This is working as Expected.

Thanks

1 Like
12

Happy Automation!! @srinusoft37

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.