Renaming a file based on matched excel column

Help
1

I have a predefined excel sheet (Sheet 1) with names and suffix and am scraping data from a website and buliding a datatable (Sheet 2) from it.

Items scraped (sheet 2) are matched against items in sheet 1 and processing is done. For that particular item a document is downloaded and when naming it I want it add the respective suffix for particular item (sheet 1) to the item. Say for example if item Delhi is matched between the sheets and the
downloaded document will have the name “XYZ” + DE.Sheet 1.xlsx (8.6 KB)

2

@pawan do you need the solution for the entire problem?
or if I am not getting it wrong, your challenge is in finding the corresponding column value for Delhi?

3

If you are performing a Vlookup this is an example. Two datatables, one looks up the other and populates a column based on the match. This should be easily reworkable to meet your needs.

Sequence.xaml (11.3 KB)

Input.xlsx (9.1 KB)

4

Yes based on the city add the required suffix by doing a lookup with the corresponding value in the next column

5

Check this out. This entire thread might be useful for you

6

Another easier way to do it is, convert the first excel sheet into a dictionary object. Then you can get the suffix value with the city name in one command.

Here is what you need to do
Create a new variable dict_Suffixes

image
image707×58 3.6 KB

Read Range - Sheet 1 into DataTable1
For Each Row in DataTable1
Assign dict_Suffixes(row("Name").ToString) = row("Suffix").ToString

Once this is done wherever you want the suffix you can get it using dict_Suffixes("cityName")
where cityName is something you can pass on from your other datatable too.

Hope this helps.

7

Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)