Merge 2 different Excel In 1st excel

Help Studio
, ,
1

Hello,

This image shows a spreadsheet with columns related to invoice and financial data such as Supplier, Invoice, Due Date, Purchase Order, Sales Order, Organization Name, End User, Payable, Subtotal, Total Amount, Supplier Reference, Service Period, and Product Table. (Captioned by AI)
This image shows a spreadsheet with columns related to invoice and financial data such as Supplier, Invoice, Due Date, Purchase Order, Sales Order, Organization Name, End User, Payable, Subtotal, Total Amount, Supplier Reference, Service Period, and Product Table. (Captioned by AI)1431×103 4.63 KB
- This Excel 1

The image shows a spreadsheet detailing the "PURE" product with columns for quantity, rate, amount, term start date, and term end date for three different entries. (Captioned by AI)
The image shows a spreadsheet detailing the "PURE" product with columns for quantity, rate, amount, term start date, and term end date for three different entries. (Captioned by AI)799×187 4.11 KB

This Excel 2

I want to merge these 2 Excel in Excel 1 like

This image shows a spreadsheet with columns for supplier information, invoice details, purchase orders, financial data, service periods, and transaction amounts. (Captioned by AI)
This image shows a spreadsheet with columns for supplier information, invoice details, purchase orders, financial data, service periods, and transaction amounts. (Captioned by AI)1735×205 8.08 KB

How can i do this?

2

hello @HELZMOTH_BJ ,

Firstly, you need to find last row of excel 1 by using - dt.rowcount
then you need to use write range in excel 1 with datatable of excel 2 and give range - “A”+(dt.rowcount+1).tostring

This image shows a sequence of actions within an automation workflow to read data from two Excel workbooks and write the combined data to another Excel file. (Captioned by AI)
This image shows a sequence of actions within an automation workflow to read data from two Excel workbooks and write the combined data to another Excel file. (Captioned by AI)543×416 14.6 KB

let me know if this works.

Cheers!

3

@HELZMOTH_BJ,

If there is no logic to be checked just concat as it is, you can use below approach

  1. Read Excel 2 data using Read Range workbook activity.
  2. Use Write Range activity to write the step 1 datatable in to Excel 1 at the end cell of the range. As per screenshot, you should be providing range Q1. Also add headers property should be checked.
4

Can you please provide xaml

5

This is a spreadsheet containing invoice details for various suppliers, including columns for Invoice ID, due dates, purchase dates, sales order dates, organization names, and amounts in Euro. (Captioned by AI)
This is a spreadsheet containing invoice details for various suppliers, including columns for Invoice ID, due dates, purchase dates, sales order dates, organization names, and amounts in Euro. (Captioned by AI)1415×362 15.9 KB

Your output look like this but i wan to add the dt2 from last column and copy the dt1 value to rest of the rows

7

Got you,

Here’s the Xaml containing logic as per your requirement, Let me know if it works.

BlankProcess2.zip (16.5 KB)

Below are 2 input excel

excel 1.xlsx (8.9 KB)
excel 2.xlsx (8.8 KB)

Regards.

2 Likes
8

@HELZMOTH_BJ

You just need to read Excel 2 and use write range with the range as the first cell where the data needs to start in excel 1

cheers

9

This work but i also want to copy the 1st row of excel1 till column Service_Period until it reach empty row.

The image displays a spreadsheet with a list of invoices, detailing information such as the supplier, invoice number, due date, purchase and sales order, organization name, payable amount, totals, description, quantity, rate, amount, and term start and end dates. (Captioned by AI)
The image displays a spreadsheet with a list of invoices, detailing information such as the supplier, invoice number, due date, purchase and sales order, organization name, payable amount, totals, description, quantity, rate, amount, and term start and end dates. (Captioned by AI)1711×112 7.71 KB

10

You should create other thread for this as it will be easy for other user who will see this post in future.

11

Assumption:

  • side by side merge of Excel1 and Excel2
  • Repeat / fillup Excel1 rows for Excel2, when Excel2 Rows count greater then Excel1

Variables:
dtMerged | Datatype: DataTable

Flow:

  • Read excel1 with read range → dt1,
  • Reade excel2 with read range ->dt2
  • Assign Activity: dtMerged = dt1.Clone()
  • Merge DataTable Activity: destination: dtMerged | source: dt2.Clone()
  • Assign Activity:
    dtMerged =
(From d1 in dt1.AsEnumerable
From d2 in dt2.AsEnumerable
Let ra = d1.ItemArray.Concat(d2.ItemArray).toArray
Select r = dtMerged.Rows.Add(ra)).CopyToDataTable

then write back dtMerged to an Excel / new Worksheet