Merge Excel and CSV Files with Power Automate

June 15th 2022

We’ve recently release several new capabilities for Encodian Flowr, including the new ‘Merge Excel Files‘ action. The ‘Merge Excel Files‘ flow action enables you to merge up to 1000 Microsoft Excel files (and related formats) into a single file of the selected output format. The following file formats are supported for merging:

  • XLSX
  • XLSB
  • XLST
  • XLSM
  • XLS
  • CSV
  • tabdelimited
  • ODS
  • spreadsheetml

The output file format can be set to either:

  • PDF
  • CSV (Available July 22)
  • TIFF
  • XLSX
  • XLS
  • XLSB
  • XLSM

Merge an array of Excel files to a single file

To showcase how to merge a collection of files using the ‘Merge Excel Files‘ flow action we’re going to create a manually triggered flow which obtains a collection of files from a SharePoint library and then adds the merged file back to SharePoint.

Please remember that the source location(s) of the files to be merged and the destination of the merged file can be anywhere Power Automate supports, such as Box, Azure Storage, OneDrive, Outlook, etc.

Step One

Create a new Flow using the ‘Instant cloud flow‘ option

Step Two

Enter a name for the Flow, select the ‘Manually trigger a flow‘ trigger action and click ‘Create

Step Three

Add a ‘Initialize variable‘ action

3.a. Name: Enter ‘Files’

3.b. Type: Select ‘Array

Step Four

Add a SharePoint ‘Get files (properties only)‘ action

4.a. Site Address: Enter the location of the target SharePoint site

4.b. List or Library Name: Select the target SharePoint document library

4.c. Limit Entries to Folder: Set to a specific folder if required

This configuration will obtain the properties for the following files:

Step Five

Add a SharePoint ‘Get file content‘ action

5.a. Site Address: As per step 4.a.

5.b. File Identifier: Select the ‘Identifier’ property provided by the ‘Get files (properties only)‘ action

Upon selection this will automatically place the ”Get file content‘ action into an ‘Apply to each‘ control, this is because the ‘Get files (properties only)‘ action returns an array of documents (one or more)

Step Six

Add an ‘Append to Array Variable‘ action

6.a Name: Select the ‘Files’ variable

6.b. Value: Add the following JSON to the ‘Append to array variable‘ variable

 {
   "fileName": ,
   "fileContent": 
 }

6.c. Append the following properties as per the animation below:

The competed JSON value is as follows:

{ 
"fileName": @{items('Apply_to_each')?['{FilenameWithExtension}']}, 
"fileContent": @{body('Get_file_content')}
}

Step Seven

Add the Encodian ‘Merge Excel Files‘ action underneath the ‘Apply to each‘ action

7.a. Filename: Enter a name for the output document

7.b. Click the ‘Switch to input entire array‘ icon

7.c. Pass the ‘Files’ variable created in step #3

The configuration to create the merged file is now complete! Next, you need to add actions to do something with the merged file.. for this example, we’re just going to add the file to SharePoint.

Step Eight

Add a SharePoint ‘Create file‘ action

8.a. Site Address: Enter the location of the target SharePoint site

8.b. Folder Path: Select the target SharePoint library/folder

8.c. File Name: Select the ‘Filename‘ property from the the ‘Merge Excel Files‘ action

8.d. File Content: Select the ‘File Content‘ property from the the ‘Merge Excel Files‘ action

Your Flow is now complete and should follow this construct… albeit you may have a different trigger action!

Next, test your Flow and validate the merged file has been created:

Validate the resulting file has been processed correctly:

Finally

We hope you’ve found this guide useful, and as ever, please share any feedback or comments – all are welcome!

You can find further documentation and guidance on the Encodian support portal: Merge Excel Files

Author
Jay Goodison

Managing Director

You might also be interested in...