How to Split and Reassemble Excel XLSX Workbooks in Power Automate

In Power Automate, we can slice and dice our existing Excel workbooks in a variety of interesting ways.

We can, for instance, split multi-worksheet Excel reports into a series of new workbooks, and then attach an additional report to each of those new documents. This allows us to quickly assemble new report combinations without any manual intervention.

Splitting and Reassembling Excel Documents in Power Automate

In this article, we’ll walk through a quick example flow that performs the Excel splitting and merging process as outlined above. We’ll use two simple Cloudmersive Document Conversion connector actions to make it happen.

We’ll start by creating a manually triggered instant cloud flow.

In our first two steps, we’ll use two Get file content actions to retrieve two separate Excel workbooks from our file system. Later in our flow, we’ll split the first workbook into separate worksheets and merge those with the second Excel document we retrieve.

In my example, the initial file I’m retrieving contains several worksheets of data related to a company’s financial expenditure this year. The secondary file contains annual reporting data from the previous decade.

Next, we’ll add a new action and type “Cloudmersive” into the connector search bar.

This brings up a list of several different Cloudmersive connectors. We’re looking for the Document Conversion connector, which is easily identifiable by its green Cloudmersive logo.

We’ll click “See more” to view the full actions list, and from there, we’ll CTRL+F search for an action called Split a single Excel XLSX into separate worksheets.

After we select this action, we’ll need to create our Cloudmersive Document Conversion connection. Since it’s a third-party connector, we’ll need a premium Power Automate license to access it in the first place; as long as we have that, however, we can use it for free by obtaining a free Cloudmersive API key. These allow a limit of 800 API calls per month & zero additional commitments.

After we’ve created and authorized our connection, we’ll provide our initial Excel file’s content bytes and file name in each of the two basic request parameters. Note that we do NOT need to use our file’s real name when we configure this request.

Next, we’ll click “Show all” to view the advanced parameters.

From here, we’ll click on the dropdown menu and select “Yes”. This specifies that our split worksheets will return to our flow as content byte strings (rather than temporary URLs).

Now that we’ve split our initial Excel document into separate worksheets, we’ll head back to the Cloudmersive Document Conversion connector actions list and search for an additional action.

This time, we’re looking for an action titled Merge Two Excel XLSX Together.

Since we already created our Document Conversion connection, we can select this action and begin configuring our request right away.

This Merge Excel action has two sets of file content byte & file name parameters. We’ll place our split worksheet content in the initial file byte parameter, and we’ll then follow that with a random name. Upon selecting the WorksheetContents value in the Input File parameter, Power Automate will automatically wrap our Merge Excel action in a For Each control.

Next, we’ll place dynamic content from our Get file content 1 action in the second Input File parameter, and we’ll follow that with another random name.

At this point, we’ve asked our flow to merge each split worksheet with a new Excel workbook.

All that’s left is to add a Create file action and write each of our new workbooks to a folder in our system. In my example, I’ll end up with three new workbooks — exactly one for each worksheet in my original Excel document. We can name our new workbooks by reusing the original worksheet names (and then including any additional text we feel may be helpful).

After we save and run our flow, we’ll find our new Excel workbooks in the folder we specified (note that the time it takes for this flow to run will largely depend on the traffic Power Automate is experiencing at any given time; in this case, it took over 5 minutes, but it typically takes 1 minute).

And just like that, we’ve generated a series of new Excel workbooks from two existing, separate documents in our file system.

This is a great way to quickly and dynamically assemble new reports!