Hi can anyone help me into this please , I have two excel file and both have more than 1 lakhs of data, and i want to look both excel files, wherever value found the respective price will update.
Please have a look once.
datatable 1:
Sharing my thought. since you told each excel having one lakh rows. instead of going with data tables logic. can we do like this below to achieve that.
β Use write cell activity to insert vlookup formula to get the price value from the second excel in the first excel (cell: B2).
β And after that use Auto fill formula to fill the vlookup formula to the range what we want. (dynamically we can assign range by using read range data table β get dynamic row count)
Based on the above logic it will take very less amount of time to get all the price values.
It should work I have done this many times. I donβt have my laptop with me now otherwise I would have create work flow and share it with you.
In my opinion Going with Data table comparing and loop concept will take lot of time for more than one lakh rows. You have to go for vloolup It will get you the correct price values in excel itself.
You can use OLEDB connection using Database Activities and connect your input excel file.
You can use the mentioned connection string in the attached workflow and change the value of input file path as per your machine and use a normal select query: SELECT * FROM [InputSheetName$] to get all the records from input file into a data table. Remember to give square brackets and $ symbol at the end.
Iterate through the datatable and use an update query such as βUPDATE [OutputSheetName$] SET [Price] = 'β+ CurrentRow.item(1)+ββ WHERE [Product]='β+CurrentRow.item(0)+β'β with the mentioned connection string in the attached workflow and change the value of output file path.
Hi,Thank you ,This is working but can you please tell me what to add/Modify in code, so that if the data is not present in the dt2 , it simply return Blank.
like this : sheet 1: