Combine Excel sheets

Learn Academy Feedback
1

How to combine four excel sheets into one excel where in each of four excel sheets contain multiple sheets ?

2

Do they have the same type of data in all the sheets?

Then you can use merge data table activity

1 Like
3

Hey @apanagan1

Welcome to forum, you can read all the sheets separately using read range and paste it on the first sheet using Append Range activity, if only you have the same structure in all the sheets.

Thanks
Goutham Vijay

4

Hi @apanagan1

If the condition mentioned by @HareeshMR is satisfied, you can use the below snippet to read all the excel files…

https://go.uipath.com/component/merge-content-of-multiple-excel-files

A slight modification will be required since you have multiple sheets in one excel. So within the loop used to loop through excel files, use another loop to loop through the sheets you have in each excel .

To get the sheets in an excel file, you can use get excel sheets activity.

1 Like
5

Data inside the cell differs from one excel to other … but the headers are same and number of tabs in each excel is fixed.

6

Yes, structure is same but the data changes.

7

So, you can get all the sheets in the excel, using get workbook sheets activity and loop through all the sheets and use read range activity to read individual sheet and store data into different data tables. Then use merge data table activity (requuired number of times) and give two datatables each time. Then store the final data table.

Or else, if you want to give the sheet names , you can give them as a string in the read range activity

8

@apanagan1

You can refer the following program,
ExcelAppendSample.xaml (7.9 KB)
sample.xlsx (9.0 KB)

2 Likes
9

Hi…How can i use for each activity when the sheet names are different?
and the sample excel you have sent has 3 sheets but my actual requirement is to combine 4 excel files where in each excel file has 20 sheets in it with different names.

10

@apanagan1
I have used get workbook sheets which works for dynamic sheet names, the same logic can work for multiple files.

11

Can i include multiple sheet names in read range and append range activities?

12

You can’t include more than one sheet name in read range, use for each for that as i did in the sample.

13

But i have to read sheets from different excel files…You have used for each activity because the sheets are in one excel file itself !

14

@apanagan1

You can refer the updated workflow, here I have created 2 excel files with multiple sheets and finally all the data are going to append into a single sheet. You can change the destinations as per your requirement and if you have one or more excel files with dynamic sheets you can follow the same method.

ExcelAppendSample.xaml (11.7 KB)
sample.xlsx (8.9 KB)
sampleOne.xlsx (8.5 KB)

1 Like
15

Hi…where the data getting stored after executing? You haven’t created any new excel file right?

16

Data is getting stored inside sampleOne.xlsx sheet1, if you want to write it in seperate excel you can do. For your ease use output datatable activity and display the data.

17

in one excel we have two sheet like sheet1 and sheet2 w have to put into another excel in one sheet…so can you plz help me …i have attached my fileusecase1.xaml (11.4 KB) abc.xlsx (8.6 KB)

18

hi @apanagan1. very helpful. one more requirement: the worksheet names i want to input in a new column to indicate that the succeeding rows in the newly combined sheet belong to this particular sheet name. how do i do that?