How to merge worksheet tab

Help Studio
, ,
1

I have multiple workbooks in a folder. Each workbook has three tabs. each tab has same column with different data.
eg:
Workbook1 have tab Claim_ABC, Claim_XYZ and Claim_PQR
Workbook2 have same 3 tabs with same tab Claim_ABC, Claim_XYZ and Claim_PQR
Workbook3 have same 3 tabs with same tab Claim_ABC, Claim_XYZ and Claim_PQR
and so on.

there is a possibility of having no data in one of the tab.

How to merge each of the the tab with their respective sheet into one file.
i.e. Claim_ABC data of all three workbook into one
Claim_XYZ data of all three workbook into new
Claim_PQR data from all the workbook from the folder

Please help.

2

Hey @Khooshbu_Jani1

What do you mean by tab here please ?

Thanks
#nK

3

Hello @Khooshbu_Jani1 Please refer to the attached workflow

Example.zip (35.9 KB)

4

Tab mean Sheet1 sheet2 sheet3

1 Like
5

Hey @Khooshbu_Jani1

Did you get the solution ?

Thanks
#nK

6

No not yet. I try to work on above xaml file. But no luck. If anybody can help, I will be thankful to them.

This is my Excel file. I have few files in a folder, with same columns and same Sheets. In the screen shot, ClaimOut1 has all data. Other three sheets are filtered column from Payment(A1). Now I want to merge all Claim_HomeInsurance from all the files in a folder, to one file, Claim_CarInsurance data of all files in a folder to another file and so on.

How to accomplish that?

image
image817×479 135 KB

7

Use Merge Data Table activity

8

Hey @Khooshbu_Jani1

  1. Read Range from all the file sheets into different data tables

  2. With Merge data table activity you can put all these data into one table provided the schema is same

  3. Write the final merged data table into an output Excel

Hope this helps

Thanks
#nK

9

But I am not sure how many files are there in a folder?

so I have to loop through files to find those tabs.

How will I accomplish that?

10

Hey @Khooshbu_Jani1

If the number of files and sheets inside it is also dynamic then you need to use Directory.GetFiles with XLS filter parameter to get all the Excel files & Get Workbook Sheets activity which helps you to get all the sheets from the file.

Then you iterate files and Foreach file you iterate sheets & then perform above discussed logic inside it.

Hope this helps.

Thanks
#nK

11

I referred to this flow but it gives me Data Link Property to connect.
I never got this error while working with Excel before

12

@Khooshbu_Jani1 Can you share the error screenshot

13

Hi @Usha Kiranmai

I could resolve the problem. I am so thankful to you for your guidance. After running the flow, I was getting error of Data link Property screen. If I was cancelling that, flow was stopping. I found one post which was talking about thumb.db so, I tried to use the C# syntax of file extension. so now it is just finding .xlsx and ignoring .db extension files. Tried to delete or stop creating thumb file, but due to group policy, couldn’t do that.
So my workflow is working now
again, thank you very much

14

Great ! @Khooshbu_Jani1 . Please close the topic by marking the post as solution which you felt helped in resolving your issue . This way it helps others looking for the same solution