Compare Datatable- Help

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:
image
datatable2:
image

Expected output:
image
Test1.xlsx (8.6 KB)
Book3.xlsx (9.4 KB)
Please help

Hi @Anjali_Rani ,

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.

if you need any help please let me know. thanks.

Hi, This is not working , I have to compare two datatables and then update price field with respective product.

Please help

Hi,

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.

ExcelComparisonAsDatabase.xaml (8 KB)

Hi,

How about the following?

Sample20211121-2.zip (14.2 KB)

Regards,

1 Like

Cool man. You are rocking.

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:
image
sheet2:
image
output:
image

Hi,

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.

I modified type of dict and LINQ expression.
Can you try the following sample?

Sample20211121-2v2.zip (12.5 KB)

If you want to write price as numeric in xlsx, might need to use Excel-WriteRange instead of System-File-Workbook-WriteRange.

Regards,

1 Like

Hi, Thank you, but it is removing all other fields values from Result one file, can we do this like, image
so that product code value should retain.

Please help

Hi,

Can you try to modify arguments of LoadDataRow as the following?

dt1.AsEnumerable.Select(Function(r) dt1.Clone.LoadDataRow({r("sku"),If (dict.ContainsKey(r("sku").ToString),dict(r("sku").ToString),""),r("Product code")},False)).CopyToDataTable()

Regards,

1 Like

Than you.
This is Working, Actually i have very least idea of LINQ Queries.
But Thanks for all your support. :slight_smile:

1 Like

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