Lookup Data Table Add Data Row

Learn Academy Feedback
1

Hi,

I have 2 Excel files and need to Vlookup one column from second Excel file into the first.
For this I created 2 Data tables from those two Excel files. I used For Each activity with Lookup Data Table activity to Lookup needed column from second Datatable. The logic is that I used Build Data Table activity for storing a column that should be result of Lookup Data Table activity. After I used Add Data Row activity to add resulted column of Lookup Data activity to just created new Data table. In the properties of Add Data Row activity I wrote: ArrayRow: {MyVariable.ToString}, where MyVariable is Variable where result of Lookup Activity stored and in the Data Table filed I indicated newly created Data Table. The problem is that the process of Lookup activity works very slow and sometimes getting Error: “Runtime execution error Source: Add Data row Message: Object reference not set to an instance of an object”. How can I fix it?
May be there are other ways I can vlookup data from two excel files?

2

Hey @Apple1

This error comes when there’s a null value maybe your lookup is not able to get the value properly.

Check out this video it’s about the same problem you’re trying to solve : Adding Specific Columns From One Data Table To Other - YouTube

let me know if you need any other help

3

@Apple1

You can check below post

Hope this helps you

Thanks

4

Hi @Apple1

In the lookup datatable activitiy there is rowindex in the property panel

Using that rowindex u can check whether the My variable is empty or not

If rowindex is a positive number (0 to positive)

Then we can say that what value we are looking in the datatable is there

But if the value is not there the rowindex should be -1

So before adding data using add data row

Use an if Condition before the add Datarow with the rowindex as condition

Hope it helps

Regards,

Nived N :robot:

Happy Automation :relaxed: :relaxed:

5

Hey @Parth_Doshi,

Thank you for your video. I did exactly the same as you explained in your video, but got stuck at the part where you assign ID variable. What should be the Value of ID variable, if I have two Excel files as shown in the pictures:
File1
File2
I need to Vlookup rows from File2 to File1 into the Delivery_code_file_2 column (highlighted as yellow)
Also, I added two Lookup Data Table activities as you show in your video, but do not know which column name should I write in ColumnName filed in the Properties panel for both Lookup Data Table activities.
Could you please clarify?
By the way, Read range activity in my case worked only inside Excel application scope activity.

6

Hey @Apple1
Can you send the photo of you second Excel?
Whatever row you’re looking for in table two, once you get the index use that index to get the value of the column you want to write from file 2 to file 1(syntax is there in video) after that use that value in assign statement in for each row again syntax is there in video and all the syntax are shown very clearly in the video check again it will solve yoir problem.

and for the read range it only worked in Excel application scope because you’re usinf Excel Activities for the you require the scope in Workbook Activities you don’t.

if you still can’t solve it let me know.

7

@Parth_Doshi
Photo of both excel files here:
File1_File2
can you please clarify what should I write in Assign activity and Lookup Data table?

8

DataTable1.Rows(RowIndexTable1)("Delivery_code_file2) = DataTable2.Rows(RowIndexTable2)("Delivery_code_file2)

This should work

9

I did it in the beginning, but it does not work
RowIndexTable1 - in your video instead of Table1 you use DataTable1, is not it? So it is RowIndexDataTable1, as I understood

10

Yes by Row Index Table 1 I meant the index where you want to write the data in the data table 1 same for getting the value from data table 2.

11

were you able to solve it @Apple1?

12

no :frowning:
still struggling

13

wait i will write complete solution for you.

14

ok :slight_smile: :+1:

15

  1. read range first excel - DT1

  2. read range second excel - DT2

  3. for each row in DT1
    assign the delivery_code_file_1 value to variable

      look up data table for delivery_code_file_1 value in DT1 get the row index column 
  name  here = delivery_code_file_1 
  row index variable name Ri1 
  
  look up data table for for delivery_code_file_1 value in DT2 get the row index column 
  name here = delivery_code_file_2
  row index variable name Ri2
  
  now you have two row index
  
  assign 
  DT1.Rows(Ri1)("Delivery_code_file_2") = DT2.Rows(Ri2)("Delivery_code_file_2")

  4. write range DT1 into the respective excel file

I hope this will solve your problem. Please do mark it as solution. @Apple1

Cheers,
Parth

16

@Parth_Doshi
It is a bit not clear:
get the row index column name here = delivery_code_file_1
Can you please calrify where and should I write?

17

in properties of look up data table you have an option in output row index define the variable there.

18

and in look up data table the column name should be what i mentioned above

19

LookupDataTable1
Can you please check screen of properties panel?

20

proper good to go