Working with multiple excel with high volume data

[Sai_Jitendra]

I have multiple excel files

1.Excel1 contains 5000+ data which is connected to database and fetches the data everyday

2.)Excel2 is output excel 

Here scenario is everyday I need to get the latest data from Excel1 and Excel2 where, if any rows already exists in Excel2 need to update that data basing on Excel1 but any new row data from Excel1 ,need to add that row in Excel2 . Few columns in Excel2 are manually filled by team everyday which we should not update those columns.

Challenge is I am able read both excel files with OLEDB in PAD and using For each loop traverse the data for Excel1 and verifying each and every row and update/insert the required columns in Excel2  using SQL OLEDB update statements but looping through 5000 + rows of data is taking so much time .I tried using vbscript or other scriping methods in Power Automate Desktop which is not supporting any operation related to Data Table.Is there any way to update the data in datatables and bulk insert the data in Excel2 or any other way to make process faster which should everyday.

Is there any other solution for this approach, if we can do this automation faster it save so much of time and effort

Thanks,

Sai Jitendra

[MichaelAnnis]

I don't know SQL, but I would do all this in VBA. 

The logic would be in two separate major steps

1. In Excel 1, we lookup whether or not the row (primary key) exists in Excel 2 via formula (1 for yes, 0 for no)
   1. Filter for 0 (does not exist), copy primary key and paste in appropriate column at the bottom of Excel 2
2. Now that we know all primary keys exist, copy Excel 2 to a new sheet - here we will use the following logic
   1. If index/match (primary key/column in Excel 1) exists (or is not empty) use Excel 1, else, use Excel 2

So with that logic, Excel 1 will trump any existing information in Excel 2, you can ignore the manual columns you referenced that you do not want to touch.

This will have 5,000 rows of formulas in every column but the manual columns, but when it is done calculating, which shouldn't take too long, you can copy your Excel 2 formulas sheet and paste values back over your Excel 2 sheet, then delete the formulas page.

Hope this helps.