Extract data from multiple excel and update in one excel

File 1: Source File:

Where bot has to fetch the data

Rows: 50+

File 2: Mapping Table

This is file will have some info which is not there in the File 1 (Source File)

File 3: Template File:

Fill the information captured from source & mapping table file

The Bot has to fill Around 50+ columns in the template file

Our requirement:

Bot has to extract only required values ( Fields required to fill the template file) from source File and fill the values in File 3 ( Template File) and it may have to get some values from file 2 ( mapping table)

Please suggest the flow for this task.

@Sathish_Kumar_S Read the file1 to datatable(dt1) and filter the values according to your requirement. Now, read the file2 to another datatable(dt2) and filter the values similar to did for file1. Finally, merge dt1 and dt2 datatables and write to file3

you need to add data by use any key pattern?

yes, In File 1 the header is starting from row B4 to B52…etc…and value is from row C4-C52,D4-D52,E4-E52…(Different products).etc. We need only few values from from C,D…row based on the header in Template File.

and paste into from Row2 in Template file as per the header.

And We need to search some headers(from File1) in the Mapping File ( File 2) and get value and paste that value into Template file ( For Example Search BRAND in the mapping table file and get Brand type and paste into templare file in respective row. ( Because this information is constant and it will not available in File1)

use lookup table for get value from another table ,
you can then change or add value by table