How to Process Excel Data as Plain Text in Power Automate

In Power Automate, we can read and utilize Excel data dynamically in a number of different ways.

We can, for example, convert Excel to a lightweight data type —like JSON or CSV — and create a schema for our outputs.

To involve even less processing, however, we can also convert our Excel data directly to plain text and subsequently divide that text into separate lines.

Process Excel Data as Plain Text

Plain text data can be extremely useful in certain workflows thanks to its simplicity and flexibility. Imagine, for example, we have an “Annual Expenses” report like the below example:

And from this report, we’d only like to return the expense value for a specific year (e.g., 2022):

After converting our spreadsheet to text & dividing that text into independent lines, we could find the string “2022” and return all data from only that line.

We could also find the string “780,000” and return the exact same line of text. Our result won’t be structured in a particularly pretty way, but it’ll get the job done perfectly.

Example Flow

We’ll walk through a quick example Instant cloud flow that 1) converts the above Excel spreadsheet data to text, 2) splits that text into separate lines, 3) composes (beautifies) the text, and 4) returns Total Expenses ($) data from only one specific year.

We’ll use a pair of Cloudmersive Document Conversion connector actions to handle our Excel conversion and text-splitting steps respectively.

Get an example Excel file

We’ll start by using a Get file content action to retrieve our example Excel file.

Find Cloudmersive Connectors

Next, we’ll add a new action and search for Cloudmersive connectors. We’ll find the Cloudmersive Document Conversion connector on the resulting list with a green logo.

Locate the Excel to Text conversion action

After we click “See more” to view the entire actions list, we’ll locate an action titled Convert Excel XLSX Spreadsheet to Text (txt).

Create & authorize a Cloudmersive Document Conversion connection

We’ll select this action, and we’ll create our Document Conversion connection directly after. We only need to do this once for both actions in this flow. We’ll authorize our requests using a free Cloudmersive API key, which we can get by registering a free account on the Cloudmersive website (free API keys allow a limit of 800 API calls per month).

Configure the Excel to Text request

We’ll then pass Excel file bytes and a random file name (e.g., “file.xlsx”) to each of our two request parameters. The real file name doesn’t matter here because we’re only using it to label our API request.

Find the Split Text action

Now that we have a text version of our Excel content available, we’ll head back to the Document Conversion connector actions list and find the Split a single Text file (txt) into lines action.

Configure the Split Text request

We’ll configure this request exactly the same way as before — only this time, we’ll use the body/TextResult from our Convert Excel to Text action in our input file field.

We’ll also use the trim() function to remove leading and trailing whitespace from body/TextResult. Text strings from file conversions can get a little messy if we don’t use trim().

Loop through each text line with an Apply to each control

We’ll now incorporate an Apply to each control, and we’ll configure this action to take the body/ResultLines value from our Split text into lines response schema.

Compose each text line

Within the Apply to each control, we’ll now add a Compose action to clean up the LineContents value from our Split text into lines action.

Use a Condition to check the composed text for a specific string

Beneath Compose, we’ll add a Condition control that checks the composed Outputs for a specific string value. In this case, our value will be the string “2022” (note — it’s important that we read any input we check as a string to avoid errors in our condition).

Send a Teams message with the selected text data

In the True branch of our condition, we’ll send a Teams message to some team member (in this case ourselves). We’ll include a simple message — like “Spending in the year” + Output + “dollars”.

Test the flow and review the output

When we run this flow, we’ll receive a single Teams message for the total spending in 2022.

All values other than the one containing “2022” will have been ignored in our condition.

Conclusion

In this walkthrough, we learned how to process Excel files as plain text using Cloudmersive connectors in Power Automate. We converted an Excel file to text, split that text into a series of separate lines, and used a condition to query each line for a specific value.

This is one easy way to leverage Excel data in a quick flow without converting to JSON or CSV first!

Remember to check back in on this blog for more Power Automate walkthroughs.