I have multiple workbooks in a folder. Each workbook has three tabs. each tab has same column with different data.
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
What do you mean by tab here please ?
Hello @Khooshbu_Jani1 Please refer to the attached workflow
Example.zip (35.9 KB)
Tab mean Sheet1 sheet2 sheet3
Did you get the solution ?
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?
Use Merge Data Table activity
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?
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.
I referred to this flow but it gives me Data Link Property to connect.
I never got this error while working with Excel before
@Khooshbu_Jani1 Can you share the error screenshot
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
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