Filter column then create excel file and send email in uipath in loop

Help Studio
, ,
1

Hi Guys , I have a masterfile of vendors email id and output file, Now on the output file ,I have to filter vendor name and extract the data for this vendors, create a separate excel file and vlookup with masterfile and send email to this vendor email id. same needs to be done with all the vendors, How can we achieve this.

2

Hi! From what I understand, you can achieve this by reading first the content of the Excel file into a Data Table and then filter it. After that you can paste the filtered result into your new Excel file where you insert the Vlookup function. You can then loop through each of the new vendors that you got using For Each Excel Row.

3

Hi

Hope the below steps would help you resolve this

  1. Read both the excel file and keep as datatable named dt1 and dt2
    Use a BUILD DATATABLE activity with one column u want and get the output as dtfinal

  2. Now use For each row loop and pass dt1 as input

  3. Inside the loop use a LOOKUP DATATABLE activity to do the vlookup between two table where pass the datatable dt2 as target datatable and column name from both the tables for look up process and get the output as string names stroutput
    Have a view on this doc on how to use lookup datatable

  4. Then use a ADD DATAROW activity and pass the above string variable as input in arrayrow property like {stroutput} and datatable as dtfinal

This dtfinal will now have the updated records

Cheers @Syed_Sultan_Ahmed1

4

@Syed_Sultan_Ahmed1

  1. Read Master File
  2. Read Output File
  3. For Each Row in Output File
    = If Vendor Name Matches
    then
    Extract Data
    Create Separate Excel File
    Read Master File
    Perform VLOOKUP
    Send Email
    repeat the process for all vendors
5

Hi, I am attaching the files , now on output file the bot will filter the vendor name one by one whatever the data we get after the filter that data will be written on new excel and then vlookup with master file to get the email addres to send email with this new excel file as a attachment. so the bot will do for all the vendors…

6

outputfile.XLS (36.5 KB)
MasterFile.XLS (26 KB)

7

Hi, I am attaching the files , now on output file the bot will filter the vendor name one by one whatever the data we get after the filter that data will be written on new excel and then vlookup with master file to get the email addres to send email with this new excel file as a attachment. so the bot will do for all the vendors

8

Hi, I tried but not getting the correct output, Please checxk if I am doing something wrong

sending_email_vendor.zip (46.9 KB)

9

Hi @Syed_Sultan_Ahmed1

To achieve the task of filtering vendor names, extracting data for each vendor, creating separate Excel files, performing a VLOOKUP with a master file, and sending emails to each vendor’s email ID using UiPath, you can follow these steps:

  1. Read the Master File:
  • Use the “Excel Application Scope” activity to read the master file (which contains the vendor names and email IDs) into a DataTable.
  1. Read the Output File:
  • Use the “Excel Application Scope” activity to read the output file (which contains vendor names and data to be extracted) into another DataTable.
  1. Loop Through Vendor Names:
  • Use a “For Each Row” activity to loop through the vendor names in the output file’s DataTable.
  1. Filter Data for Each Vendor:
  • Inside the loop, use DataTable.Select method to filter rows in the output file’s DataTable based on the current vendor name.Example:

vbnetCopy code

filteredRows = outputDataTable.Select("VendorName = '" + currentVendorName + "'")
  1. Create a Separate Excel File:
  • Create a new Excel workbook using the “Excel Application Scope” activity.
  • Write the filtered data (filteredRows) into this new Excel workbook using the “Write Range” activity.
  1. Perform VLOOKUP:
  • Use Excel activities to perform a VLOOKUP operation in the newly created Excel file with the master file.
  • You can use Excel formulas or custom code to perform the VLOOKUP.
  1. Send Email:
  • Use the “Send SMTP Mail Message” activity to send an email to the vendor’s email ID, attaching the Excel file created in step 5.
  • You can customize the email subject, body, and attachments as needed.
  1. Repeat for Each Vendor:
  • Continue looping through the vendor names in the output file, repeating steps 4 to 7 for each vendor.
  1. Error Handling:
  • Implement proper error handling and exception handling in your workflow to handle any issues that may arise during the process.
  1. Logging and Notifications:
  • Implement logging to keep track of the status of each vendor’s data extraction and email sending.
  • Consider sending notifications or alerts in case of errors or issues.
  1. Testing:
  • Test the workflow with sample data to ensure that it correctly filters, extracts, creates Excel files, performs VLOOKUP, and sends emails for all vendors.

This workflow will allow you to automate the process of extracting and emailing data to vendors from the output file, and it will repeat this process for each vendor listed in the output file.

Thanks!!

10

Thanks Nitya, I will check and update the same

1 Like
11

I was trying but couldnt get the output, Can you please make the sample xaml and send it to me.
outputfile.XLS (41.5 KB)
MasterFile.XLS (26 KB)

12

Hi @Syed_Sultan_Ahmed1

xaml :- sending_email_vendor.zip (53.0 KB)

I was attaching the xaml please check the xaml…

1.In that for each row and for each row in side i was used the if activity
Then section :-
You can extract the data what you want.And store in the excel and below that only i
have used the mail function so you can provide the your email and password it will
send the email to that persons in master file …

13

Let me know if its working or not

@Syed_Sultan_Ahmed1

14

Hi Praveen , Not able to extract data using extract datatable .If you can use it and let me know

15

from where your extracting the data
if not confidential you can share

@Syed_Sultan_Ahmed1

16

Its not working , Please help me out, its urgent

17

Hi guys this is resolved by Ganta Laksman Sir …thank you very much

18

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