Merge Excel and CSV Files with Power Automate

Merge Excel and CSV Files with Power Automate

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.

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

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

3. Add a ‘Initialize variable‘ action

3.a. Name: Enter ‘Files’

3.b. Type: Select ‘Array‘ 

4. 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:

5. 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)

6. 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')}
}

7.  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.

8. 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

Convert Visio Files in Power Automate

Convert Visio Files in Power Automate

We have recently released a new ‘Convert Visio‘ action which provides the capability to convert Microsoft Visio file formats to the following file formats PDF, BMP, GIF, HTML, JPG, PNG, SVG, TIFF and VSDX.

NOTE: This action may not yet be available within your region, Microsoft commenced worldwide deployment April 2022 and are due to complete by June 2022

The supported input file formats are:

  • VSD
  • VSDX
  • VSX
  • VTX
  • VDX
  • VSSX
  • VSTX
  • VSDM
  • VSSM
  • VSTM

The ‘Convert Visio‘ action covers these very common file format conversion scenarios, with example configurations depicted below:

The ‘Convert Visio‘ action determines the input file format from the ‘Filename‘ value provided (albeit we do some additional validation), i.e. by checking the file extension. Typically, you would pass filenames via dynamic data, for example;

However for these examples we’ll simply manually type the input filename for ease of reading:

Converting VSD to VSDX with Power Automate

The following example converts a Visio ‘VSD‘ file to ‘VSDX‘ format:

For a detailed explanation of the advanced features please refer to the documentation.

Converting VSDX to PDF with Power Automate

The following example converts a Visio ‘VSDX‘ file to ‘PDF‘ format:

For a detailed explanation of the advanced features please refer to the documentation.

Converting VSDX to PDF/A with Power Automate

The following example converts a Visio ‘VSDX‘ file to ‘PDF/A‘ format:

For a detailed explanation of the advanced features please refer to the documentation.

Converting VSDX to TIFF with Power Automate

The following example converts a Visio ‘VSDX’ file to ‘TIFF‘ format:

For a detailed explanation of the advanced features please refer to the documentation.

Converting Visio File to an Image with Power Automate

The following example converts a Visio ‘VSDX’ file to an image format (BMP, GIF, JPG, PNG or SVG)

NOTE: When converting to these image file formats by default the the first page within the source document will be converted, alternatively set the ‘Page Index‘ property to the desired page number. For multiple pages please convert to TIFF.

For a detailed explanation of the advanced features please refer to the documentation.

Final thoughts…

Hopefully, this post has provided a high-level overview of the capabilities of our new ‘Convert Visio‘ action, if you have any technical queries please visit our customer support portal.

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

Power Automate Actions Slow to Error or Timeout

Power Automate Actions Slow to Error or Timeout

Unfortunately and hopefully seldomly! an action within your Power Automate flow could produce an error. We see a number of posts on the community forums, our own support service and general conversations with customers who report that errors take ‘forever’ to return, or that a flow becomes unresponsive and ‘times out’.

Note: For this post I have used Encodian actions (and forced the errors with bad data), but this post applies to all connectors and actions across Power Automate.

Flow run timed out. Please try again.

Consider the following flow which appears to have timed out:

Flow run timed out. Please try again.

The ‘Flow run timed out. Please try again.‘ error message is misleading, the flow has not timed out it is still executing. The error message means the user interface has ‘Timed Out’, but the actual flow is still executing which you can see in the run history of the flow:

Flow execution taking a long time to error

Eventually the flow will complete and fail:

Now, if you open the execution from your run history you’ll see that the action appears to have taken 20 minutes to fail!

But if you click on the action you’ll see that it has actually failed multiple times and the duration is extensive because the default Power Automate ‘Retry Policy‘ has not been changed:

The Power Automate ‘Retry Policy‘ controls how many times to retry when an action fails returning a 408, 429 or 5** HTTP status code. This can be useful to assure your flow does not totally fail if an intermittent issue occurs, but if left unchanged during flow development or troubleshooting it will result in a lot of lost time!

Change Power Automate Retry Policy

The simple fix is to disable the retry policy, which can be done as follows:

Now, after completing this change to the ‘Retry Policy‘ and re-executing the flow, the action fails in 2 seconds and not 20 minutes:

Disabling the ‘Retry Policy‘ for a production scenario is not a great idea as there will be intermittent issues where the policy prevents a complete failure of your flow execution. You may wish to create a custom policy to control retry counts and intervals.

Create a custom Power Automate Retry Policy

You may have noticed that the ‘Retry Policy‘ description states the default policy will retry 4 times:

But, this has been changed to 8 times and this description simply hasn’t been updated!:

If the default policy (8 executions) takes to long time to fail, you may wish to configure your own custom policy. The following configuration performs three retries with a fixed interval of five seconds:

The following configuration performs three retries on an exponential interval between twenty seconds and five minutes:

The internal format adheres to the ISO 8601 duration format.

Final thoughts…

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

Get Image File Information using Power Automate

Our ‘Get PDF Document Information‘ action has been available for a while. The action exposes information about a PDF file within Power Automate, enabling automation logic to be executed considering information about the PDF document provided, for example validating whether a file contains a text layer before performing OCR.

Today we’re really pleased to introduce our new ‘Get Image Information‘ action which exposes information about the image file provided covering: image format, size, width, height, orientation, EXIF Data and much more.

At the time of writing this new action is now available on Encodian’s (Azure PaaS hosted) global cloud platform, and Microsoft are updating Power Automate to make this new action available to all regions; this is due to complete sometime April 22

For this post let’s explore a few example scenarios for using the new ‘Get Image Information‘ action:

Conditional Image Resize with Power Automate

For this scenario the flow we are going to create will validate the dimensions of an image to determine whether the image should be resized. I have created a very simple flow which is triggered when a new file is added to a SharePoint library; you may wish to add trigger conditions to ensure the flow is only triggered for files with a certain extension (images), check out: Create Power Automate Trigger Conditions Simplified

The ‘Get Image Information‘ action returns the following properties about the image provided:

  • Image Format – The file format of the image
  • File Size (MBs) – The size of the supplied image in MBs
  • Width – The width of the image (Pixels)
  • Height – The height of the image (Pixels)
  • Orientation – The orientation of the image
  • Bits per Pixel – The image bits per pixel count
  • Horizontal Resolution – The horizontal image resolution (DPI)
  • Vertical Resolution – The vertical image resolution (DPI)
  • Has EXIF Data – Confirms whether the image contains EXIF Data
  • EXIF Data (JSON) – Image EXIF Data provided as a JSON string
  • Has XMP Data – Confirms whether the image contains XMP Data

Consider the following updates to the flow which uses a condition to validate the width of the image, and then if required, resizes the image using the ‘Resize an Image‘ action before updating the original file with the resized image:

This of course is a simple example and any conditional logic can be performed on the data returned by the ‘Get Image Information‘ action.

Remove Image EXIF Data with Power Automate

In addition to releasing the new ‘Get Image Information‘ action, we’re also really pleased to introduce our new ‘Remove EXIF Tags from Image‘ action which will remove EXIF tags from the image provided.

I have created a very simple flow which is triggered when a new file is added to a SharePoint library; you may wish to add trigger conditions to ensure the flow is only triggered for files with a certain extension (images), check out: Create Power Automate Trigger Conditions Simplified

Consider the following updates to the flow which uses a condition to evaluate the ‘Has EXIF Data‘ property returned by the ‘Get Image Information‘ action, and then if required, removes the images EXIF data using the new ‘Remove EXIF Tags from Image‘ action prior to updating the original file.

Reading Image EXIF Tags in Power Automate

Building upon the previous scenario where Image EXIF tags were removed using the new ‘Remove EXIF Tags from Image‘ action, the ‘Get Image Information‘ action returns EXIF image tags as a JSON string which can be parsed allowing access to all the contained data:

The ‘EXIF Data (JSON)’ properties value can be parsed using Power Automate’s ‘Parse JSON‘ action, for example:

You can create the schema using the ‘Generate from sample‘ function or by using the schema below:

{
    "type": "object",
    "properties": {
        "apertureValue": {
            "type": "number"
        },
        "artist": {             
            "type": "string"         
         },
        "bodySerialNumber": {             
            "type": "string"         
        },
        "brightnessValue": {
            "type": "number"
        },
        "cameraOwnerName": {             
            "type": "string"         
        },
        "colorSpace": {
            "type": "string"
        },
        "compressedBitsPerPixel": {
            "type": "integer"
        },
        "compression": {
            "type": "integer"
        },
        "contrast": {
            "type": "string"
        },
        "customRendered": {
            "type": "string"
        },
        "dateTime": {
            "type": "string"
        },
        "dateTimeDigitized": {
            "type": "string"
        },
        "dateTimeOriginal": {
            "type": "string"
        },
        "digitalZoomRatio": {
            "type": "integer"
        },
        "exposureBiasValue": {
            "type": "integer"
        },
        "exposureIndex": {
            "type": "integer"
        },
        "exposureMode": {
            "type": "string"
        },
        "exposureProgram": {
            "type": "string"
        },
        "exposureTime": {
            "type": "number"
        },
        "fileSource": {
            "type": "string"
        },
        "flash": {
            "type": "string"
        },
        "flashEnergy": {
            "type": "integer"
        },
        "fNumber": {
            "type": "number"
        },
        "focalLength": {
            "type": "number"
        },
        "focalLengthIn35MmFilm": {
            "type": "integer"
        },
        "focalPlaneXResolution": {
            "type": "integer"
        },
        "focalPlaneYResolution": {
            "type": "integer"
        },
        "gainControl": {
            "type": "string"
        },
        "gamma": {
            "type": "integer"
        },
        "gpsAltitude": {
            "type": "number"
        },
        "gpsAltitudeRef": {
            "type": "string"
        },
        "gpsDateStamp": {
            "type": "string"
        },
        "gpsDestBearing": {
            "type": "number"
        },
        "gpsDestBearingRef": {
            "type": "string"
        },
        "gpsDestDistance": {
            "type": "integer"
        },
        "gpsDestDistanceRef": {             
            "type": "string"         
        },
        "gpsDestLatitudeRef": {             
            "type": "string"         
        },
        "gpsDestLongitudeRef": {             
            "type": "string"         
        },
        "gpsDifferential": {
            "type": "integer"
        },
        "gpsDop": {
            "type": "integer"
        },
        "gpsImgDirection": {
            "type": "number"
        },
        "gpsImgDirectionRef": {
            "type": "string"
        },
        "gpsLatitudeRef": {
            "type": "string"
        },
        "gpsLongitudeRef": {
            "type": "string"
        },
        "gpsMapDatum": {},
        "gpsMeasureMode": {},
        "gpsSatellites": {},
        "gpsSpeed": {
            "type": "integer"
        },
        "gpsSpeedRef": {
            "type": "string"
        },
        "gpsStatus": {             
            "type": "string"         
        },
        "gpsTrack": {             
            "type": "string"         
        },
        "gpsTrackRef": {             
            "type": "string"         
        },
        "imageUniqueId": {             
            "type": "string"         
        },
        "isBigEndian": {
            "type": "boolean"
        },
        "isoSpeed": {
            "type": "integer"
        },
        "isoSpeedLatitudeYyy": {
            "type": "integer"
        },
        "isoSpeedLatitudeZzz": {
            "type": "integer"
        },
        "lensMake": {
            "type": "string"
        },
        "lensModel": {
            "type": "string"
        },
        "lensSerialNumber": {             
             "type": "string"         
        },
        "lightSource": {
            "type": "string"
        },
        "make": {
            "type": "string"
        },
        "maxApertureValue": {
            "type": "integer"
        },
        "meteringMode": {
            "type": "string"
        },
        "model": {
            "type": "string"
        },
        "orientation": {
            "type": "string"
        },
        "photographicSensitivity": {
            "type": "integer"
        },
        "pixelXDimension": {
            "type": "integer"
        },
        "pixelYDimension": {
            "type": "integer"
        },
        "recommendedExposureIndex": {
            "type": "integer"
        },
        "relatedSoundFile": {},
        "saturation": {
            "type": "string"
        },
        "sceneCaptureType": {
            "type": "string"
        },
        "sensingMethod": {
            "type": "string"
        },
        "sensitivityType": {
            "type": "integer"
        },
        "sharpness": {
            "type": "integer"
        },
        "shutterSpeedValue": {
            "type": "number"
        },
        "spectralSensitivity": {             
            "type": "string"         
        },
        "standardOutputSensitivity": {
            "type": "integer"
        },
        "subjectDistance": {
            "type": "integer"
        },
        "subjectDistanceRange": {
            "type": "string"
        },
        "subsecTime": {             
            "type": "string"         
        },
        "subsecTimeDigitized": {
            "type": "string"
        },
        "subsecTimeOriginal": {
            "type": "string"
        },
        "userComment": {             
            "type": "string"         
        },
        "whiteBalance": {
            "type": "string"
        }
    }
}

The image’s EXIF data can then be used within your flow:

Final thoughts…

Hopefully, this post demonstrates how the new ‘Get Image Information‘ action can be used to obtain image information and perform conditional logic within Power Automate flows.

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

Convert a PDF file to images with Power Automate

We’ve been working really hard over the past few months adding performance optimizations and 10+ new actions to the Encodian connector. At the time of writing these new actions are now available on Encodian’s (Azure PaaS hosted) global cloud platform, and Microsoft are updating Power Automate to make these new actions available to all regions; this is due to complete sometime April 22. Over the coming weeks we’ll be releasing new blog posts providing guidance and examples for using these new actions.

Firstly, we’re really pleased to introduce two new actions which enable the conversion of a PDF file into either an array of images (one image per page) or a single multi-page TIFF:

Convert a PDF file to an array of images

The ‘Convert PDF to Images‘ action accepts a single PDF file and returns an array of images pertaining to the configuration supplied. Each page within the PDF document will be converted to a single image, which is why a collection (array) of images files is returned. Let’s set up a really simple flow showcasing how to use this action; for this example we’ll setup a flow to automatically perform the conversion when a PDF document is added to a specific folder.

1. Create a new ‘Automated cloud flow

1.a. Flow name: Provide a name for your flow

1.b. Trigger: Select the ‘When a file is created in a folder‘ SharePoint trigger action

1.c. Click ‘Create

2. Configure the ‘When a file is created in a folder‘ SharePoint trigger action as required.

3. To ensure the flow is only triggered when a PDF document is added we need to add a trigger condition to the ‘When a file is created in a folder‘ SharePoint trigger action. We’ll add this trigger condition: @endswith(triggerOutputs()?[‘headers/x-ms-file-name’], ‘pdf’)

Reference: Create Power Automate Trigger Conditions Simplified

4. Add the Encodian Convert PDF to Images action

4.a. File Content: Select the ‘File Content‘ property provided by the ‘When a file is created in a folder‘ SharePoint trigger action

4.b. Image Format: Select the required image format

4.c Filename Prefix: Optional – Add a filename prefix for the filename value of the image files generated. I have used the following expression which essentially takes the source filename and removes the file extension: replace(triggerOutputs()?[‘headers/x-ms-file-name’],’.pdf’,”)

5. Add the SharePoint ‘Create File‘ action

5.a. Site Address: Set as per the trigger actions value or the target site which will store the image files

5.b. Folder Path: Select the folder

5.c. File Name: Select the ‘Documents Filename‘ property provided by the Encodian Convert PDF to Images action

NOTE: Power Automate will automatically create an ‘Apply to each‘ loop and place the ‘Create file‘ action inside because the Encodian action is returning an array.

5.d. File Content: Select the ‘Documents File Content‘ property provided by the Encodian Convert PDF to Images action

The flow is now complete and should follow this construct:

You can now test your flow and validate that the PDF document added to the target SharePoint library has been converted into an array of images:

and post flow execution:

Convert a PDF file to a TIFF file

The ‘Convert PDF to TIFF‘ action accepts a single PDF file and returns a single TIFF file pertaining to the configuration supplied. Let’s setup a really simple flow showcasing how to use this action; for this example we’ll set up a flow to automatically perform the conversion when a PDF document is added to a specific folder.

1. Follow steps 1 > 3 in the ‘Convert a PDF file to an array of images‘ section of this post

2. Add the Encodian ‘Convert PDF to TIFF‘ action

2.a. File Content: Select the ‘File Content‘ property provided by the ‘When a file is created in a folder‘ SharePoint trigger action

2.b. Filename: Either select a dynamic property (the file extension will automatically change to TIFF) or manually enter a filename

5. Add the SharePoint ‘Create File‘ action

5.a. Site Address: Set as per the trigger actions value or the target site which will store the image files

5.b. Folder Path: Select the folder

5.c. File Name: Select the ‘Filename‘ property provided by the Encodian ‘Convert PDF to TIFF‘ action

5.d. File Content: Select the ‘File Content‘ property provided by the Encodian ‘Convert PDF to TIFF‘ action

The flow is now complete and should follow this construct:

You can now test your flow and validate that the PDF document added to the target SharePoint library has been converted into a TIFF file:

and post flow execution:

Final thoughts…

Hopefully, this post provides a few good examples of how you can utilise the new Encodian Convert PDF to Images and Convert PDF to TIFF actions within Power Automate to automatically convert PDF files to image file formats.

We hope you’ve found this guide useful. As ever please share any feedback or comments, all welcome!

Check whether a PDF Document requires OCR with Power Automate

The Encodian Flowr connector for Microsoft Power Automate provides the ‘OCR a PDF Document‘ action which will perform OCR on the supplied PDF document. Optionally, the action can also be configured to perform image clean-up operations such as auto-rotation, deskew, despeckle etc.

Applying a text layer to PDF documents is important, it ensures that PDF document content can be indexed by search engines and thus found through search, it can also ensure data loss prevention rules can act on actual document content, and much more! However, OCR is computationally expensive and therefor it is sensible to only perform OCR when a document does not contain a text layer.

Consider the following Power Automate Flow which is triggered every time a PDF document is added to a SharePoint library.

Note: The following trigger condition has been added to the trigger action to assure the flow only fires for newly added PDF documents:

@endswith(triggerOutputs()?[‘body/{FilenameWithExtension}’], ‘pdf’)

Check the following video which demonstrates how to create Power Automate trigger conditions the easy way!: Create Power Automate Trigger Conditions Simplified

Now back to OCR!

Currently every single PDF document added to the SharePoint library will be OCR’d regardless as to whether it has been OCR’d previously! To optimise the flow we can add the ‘Get PDF Document Information‘ action to check for the presence of a text layer within the document and then only perform OCR if it is required.

The ‘Get PDF Document Information‘ action returns a ‘Has Text Layer‘ boolean value (True or False) which can be evaluated, consider this updated flow which now only OCR’s PDF documents which do not contain a text layer.

This updated flow will now only OCR PDF documents which do not contain a text layer!

Finally…

Hopefully, this post outlines how you can use both the OCR a PDF Document action and Get PDF Document Information to perform conditional OCR. Please share your feedback and comments – all are welcome!