Merging Excel

Help
1

I have scenario, where I have multiple excel lets excel1 in this I have one sheet where I have 4 columns and have row 10

In the second excel2 again I have one sheet but here is I have only 3 columns and have 10 rows.
I want to merge these two different excel in another excel in a one sheet. supoose I have third excel and sheet lets say output. I want the first excel1 one sheet data start from the A1 and the data of excel2 written in the same sheet from the data finished of excel1 from row 11. But it should be dynamic. Sometime row increment or decrement.

2

Hi Balkishan,

Use “Read Range” activity upon the 2 excel sheets and save the data in a data table, say DT_1 and DT_2 respectively. Now, inside the “Excel Application Scope” for the 3rd sheet use “Write Range” and assign DT_1 and then use “Append Range” activity and assign input as DT_2 (un-check the header as the header will be taken from the DT_1).

3

Hi @balkishan

You can easily use the Merge Data Table activity to merge the data. Using this activity will generate another datatable that holds data of both data tables which you can write back to a excel file. It will give out the output as you require.

Just make sure you include the first data table that has more columns that the second as the basic structure for the final data table so that it can include the additional column without any issue.

I have done a small example here. The first excel sheet has 3 columns. The second one has two. Using the DataTable.Clone will give the structure for the final datatable. Check out the attached workflow

MergeDataTableWithDifferentStucture.xaml (5.9 KB)

CheckFile1.xlsx (9.2 KB)
CheckFile2.xlsx (9.1 KB)

If this works for you, please mark the answer as the solution :slight_smile:

1 Like
4

Hii thanks for the response, I am able to merge the data in one sheet But it’s not including the headers can I need the headers also.

5

Hi @balkishan

In my workflow, I have included the headers when writing back to a excel file. You just simply need to click on the Add headers property of the write range :slight_smile:

6

@Lahiru.Fernando Thanks for sharing the info it’s really very useful. But As I am doing for multiple excel files then what to do in that case. Shared workflow is for two excel if we have multiple excel then, can you help bro?

7

@balkishan
In that case, the best option is to use Directory.GetFiles to get all excel files in your folder. Then loop through the files, do a read range, and merge them all to one datatable using the Merge Data Table activity. Here is a sample that I had developed for someone else who was searching for the same solution… This shoud work for you…

merge datatable.xaml (9.8 KB)

8

Hi Balkishan,

Please select the checkbox in “add header”, in “Write Range”.
you can refer to the flow below:

project.json (719 Bytes)

Regards,
Rhitam

9

Hi Balkishan,

My bad, i have sent you the jason file in the previous reply, you can find the “.xaml” here
Main.xaml (12.3 KB)

If this solves the issue, please mark it as solved, as its my 1st response to anyone’s query :):stuck_out_tongue_winking_eye:.
Regards,
Rhitam

10

@Rhitam_Deb please don’t share someone workflow