Convert CSV Files and CSV Data to a Microsoft Excel (XLSX) File

Convert CSV Files and data to Excel

Back in early 2020 we released the ‘Convert Excel‘ action for Power Automate, which provides the capability to convert spreadsheet file formats to another format. For example, XLS to XLSX, CSV to PDF, CSV to XLSX, etc. The following post provides some basic examples: Convert Excel and CSV Files in Power Automate. Over the past few months, we’ve received several support tickets and multiple queries on the Power Automate forums asking how CSV files and CSV data can be converted into a Microsoft Excel (XLSX) file, and in this post, we’re going to cover both scenarios.

Video Guide: Convert CSV Files and CSV Data to a Microsoft Excel (XLSX) File

What’s the difference between CSV data and a CSV file?

It is very important to distinguish whether you are working with a file or just data, as Power Automate handles these differently. Power Automate exchanges files as ‘File Content’ properties which contain a binary file encoded in base64 format, thus sending a CSV file would look similar to Flow Configuration:

The Input data sent to the Encodian action:

Compare this with CSV Data:

The Input data sent to the Encodian action:

Now, typically, you do not need to worry about whether you’re passing plain text data or a base64 encoded file, as Power Automate contains automated data formatting capabilities which dynamically send/receive data in the correct format pertaining to the data type of the sending / receiving properties, however upon occasion it may be beneficial to forcefully apply the data type by converting plain text data to a base64 encoded file to avoid data processing issues.

Tutorial: Convert CSV Data to a Microsoft Excel (XLSX) File

Building upon the previous example, I’ll update the flow so that the ‘CSV Data‘ string variable is forcefully converted to a base64 encoded file and add Microsoft Excel (XLSX file) created by the ‘Convert Excel‘ action to OneDrive. 1. Update the ‘Convert Excel‘ action 1.a. File Content: Remove the existing ‘CSV Data‘ string variable before re-adding wrapped within the base64() expression

2. Add the OneDrive ‘Create File‘ action

2.a. Folder Path: Select the location to store the file

2.b. File Name: Select the ‘Filename‘ property provided by the Encodian ‘Convert Excel‘ action

2.c. File Content: Select the ‘File Content‘ property provided by the Encodian ‘Convert Excel‘ action

The Microsoft XLSX file created from the CSV Data provided:

Tutorial: Convert a CSV File to a Microsoft Excel (XLSX) File

Building upon the previous example, I’ll update the flow so that the Microsoft Excel (XLSX file) created by the ‘Convert Excel‘ action is added to OneDrive. The initial flow is configured as follows: a ‘File Content‘ property representing the CSV file is selected when the flow is triggered. Noting the file could come from any source (OneDrive, HTTP, SharePoint, Teams, Box, Azure Storage, etc), you just need to pass the ‘File Content’ property to the Encodian ‘Convert Excel‘ action.

2. Add the OneDrive ‘Create File‘ action

2.a. Folder Path: Select the location to store the file

2.b. File Name: Select the ‘Filename‘ property provided by the Encodian ‘Convert Excel‘ action

2.c. File Content: Select the ‘File Content‘ property provided by the Encodian ‘Convert Excel‘ action

The Microsoft XLSX file was created from the CSV file provided:

Final thoughts…

Hopefully, this post will help you to build solutions converting CSV files and data using our ‘Convert Excel‘ action. If you have any technical queries, please visit our customer support portal.

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

Get data from HTML tables in Power Automate

Get data from HTML tables in Power Automate

Post #19 of our Build Flows Faster series covering our new utility actions for Power Automate. Today’s lesson is about extracting data contained within HTML tables in Power Automate with Encodian utility actions.

Our utility actions are targeted at helping you build flows more quickly with less complexity, and they brilliantly only use 0.05 actions from your Encodian Flowr and Vertr subscription allowance. For example, under a standard plan that permits 500 actions per month (uses), you could use the utility actions 10,000 times!

Introducing the ‘Parse HTML Table’ Utility Action

Our ‘Parse HTML Table‘ utility action for Power Automate allows data to be extracted from HTML tables formatted as JSON, thus the JSON data can be parsed and easily consumed within your Power Automate flows.

Video Guide: Get data from HTML tables in Power Automate

Instructions: Get data from HTML tables in Power Automate

For this post to work, we’ll work through a very simple scenario where I will manually pass a basic HTML table, parse the data, and email each row contained within the table.

For a more advanced scenario, please review the ‘Video Guide’, which details how to build a flow which extracts the Football Premier League table from the BBC Sport website before adding the data to a SharePoint list.

The trigger

Consider the following basic and manually triggered flow:

Parse HTML Table

Let’s configure the flow to process the ‘HTML’ table value provided and send each result back within an email.

1. Add the ‘Parse HTML Table‘ utility action

1.a HTML Data: Select the ‘HTML‘ property provided by the trigger action

We now need to trigger the flow, but why? The next step within the flow will be to parse the JSON data returned by the ‘Parse HTML Table‘ utility action. We need some sample data to generate the schema, which we can obtain by running the flow.

This flow will process an HTML table conforming to the following data structure:

<table>
  <tr>
    <th>Company</th>
    <th>Contact</th>
    <th>Country</th>
  </tr>
  <tr>
    <td>Alfreds Futterkiste</td>
    <td>Maria Anders</td>
    <td>Germany</td>
  </tr>
  <tr>
    <td>Centro comercial Moctezuma</td>
    <td>Francisco Chang</td>
    <td>Mexico</td>
  </tr>
</table>

Remember, if the HTML table structure changes, you will need to re-create the JSON schema used to parse the JSON data returned by the ‘Parse HTML Table‘ utility action.

Test the Flow

2. Manually test the flow:

Retrieve data

3. Obtain the data returned by the ‘Parse HTML Table‘ utility action

Parse JSON

4. Add the ‘Parse JSON‘ action and configure it as follows

4.a. Content: Select the ‘Result’ property provided by the ‘Parse HTML Table‘ utility action

4.b. Schema: Generate the schema using the data obtained from step 3 and as illustrated below

Send an email

4. Add the ‘Send an email notification (V3)‘ action

4.a. To: Configure the ‘To’ value as required

4.a. Subject: Configure the email subject value as required

4.b. Body: Configure the email body as required, selecting the relevant properties provided by the ‘Parse JSON‘ action

NOTE: Power Automate will automatically wrap the ‘Send an email notification (V3)’ action in a ‘For each’ loop because the ‘Parse JSON’ action will provide an array.

The result

Upon execution, the following emails are be generated:

If you require further advice or support regarding Encodian utility actions, please email our support team at support@encodian.com where the team would be delighted to help.

Get the file extension of a filename in Power Automate

Get the file extension of a filename in Power Automate

Post #17 of our Build Flows Faster series covering our new utility actions for Power Automate. Today’s lesson is about obtaining a file extension from a filename in Power Automate.

Our utility actions are targeted at helping you build flows more quickly with less complexity, and they brilliantly only use 0.05 actions from your Encodian Flowr and Vertr subscription allowance. For example, under a standard plan that permits 500 actions per month (uses), you could use the utility actions 10,000 times!

Introducing the ‘Get File Extension’ Utility Action

Our ‘Get File Extension‘ utility action for Power Automate obtains the file extension as a string value from the filename string value provided. In simple terms, the action returns the text located after the last ‘.’ found within the string value provided, thus ‘this.is.an.example.value‘ would return ‘value‘.

Video Guide: Get the file extension of a filename in Power Automate

Instructions: Get the file extension of a filename in Power Automate

As with many of our utility actions, there are a myriad of scenarios where you may need to obtain the file extension of a filename. Therefore, we’ll focus on how the ‘Get File Extension‘ utility action works instead of focusing on a specific scenario.

The trigger

Consider the following basic and manually triggered flow:

Get the file extension

Let’s configure the flow to obtain the file extension and send the result back within an email.

1. Add the ‘Get File Extension‘ utility action

1.a Filename: Select the ‘Filename‘ property provided by the trigger action

Send an email

2. Add the ‘Send me an email notification‘ action

2.a. Subject: Configure the email subject value as required

2.b. Body: Configure the email body as required, including the ‘Result‘ property provided by the ‘Get File Extension‘ utility action

The result

Upon execution, the following email will be generated:

If you require further advice or support regarding Encodian utility actions, please email our support team at support@encodian.com where the team would be delighted to help.

Remove diacritics from text in Power Automate

Remove diacritics from text in Power Automate

Post #18 of our Build Flows Faster series covering our new utility actions for Power Automate. Today’s lesson is about removing diacritic marks from a text in Power Automate.

Our utility actions are targeted at helping you build flows more quickly with less complexity, and they brilliantly only use 0.05 actions from your Encodian Flowr and Vertr subscription allowance. For example, under a standard plan that permits 500 actions per month (uses), you could use the utility actions 10,000 times!

Introducing the ‘Remove Diacritics’ Utility Action

Our ‘Remove Diacritics‘ utility action for Power Automate removes all diacritic marks from the text (string) value provided.

Video Guide: Remove diacritics from text in Power Automate

Instructions: Remove diacritics from text in Power Automate

As with many of our utility actions, there are a myriad of scenarios where you may need to remove diacritics from a text (string) value. Therefore, we’ll focus on how the ‘Remove Diacritics‘ utility action works instead of focusing on a specific scenario.

The trigger

Consider the following basic and manually triggered flow:

Remove Diacritics

Let’s configure the flow to remove diacritics from the ‘Data’ value provided and send the result back within an email.

1. Add the ‘Remove Diacritics‘ utility action

1.a Text: Select the ‘Data‘ property provided by the trigger action

Send an email

2. Add the ‘Send me an email notification‘ action

2.a. Subject: Configure the email subject value as required

2.b. Body: Configure the email body as required, including the ‘Result‘ property provided by the ‘Remove Diacritics‘ utility action

Executing the flow with the following example value:

Generates the following email:

If you require further advice or support regarding Encodian utility actions, please email our support team at support@encodian.com where the team would be delighted to help.

Get the difference between two date or datetime values in Power Automate

Get the difference between 2 Date or DatTime values in Power Automate

Post #16 of our Build Flows Faster series covering our new utility actions for Power Automate. We will publish many more posts and accompanying videos over the coming weeks, but today’s lesson is about generating and determining the difference between two date or datetime values in Power Automate.

Our utility actions are targeted at helping you build flows more quickly with less complexity, and they brilliantly only use 0.05 actions from your Encodian Flowr and Vertr subscription allowance. For example, under a standard plan that permits 500 actions per month (uses), you could use the utility actions 10,000 times!

Introducing the ‘Get Date and Time Difference’ Utility Action

Our ‘Get Date and Time Difference‘ utility action for Power Automate calculates the difference between the two date (and time) values provided.

Video Guide: Calculate date and time differences in Power Automate

Instructions: Calculate date and time differences in Power Automate

As with many of our utility actions, there are a myriad of scenarios where you may need to calculate the differences between two date and time values. Therefore, we’ll focus on how the ‘Get Date and Time Difference‘ utility action works instead of focusing on a specific scenario.

The Trigger

Firstly, consider the following basic and manually triggered flow:

Get the Date and Time difference

After setting your trigger, let’s configure the flow to calculate the difference between the date values provided and send the result back within an email.

1. Add the ‘Get Date and Time Difference‘ utility action

1.a Start Date: Select the ‘Start Date’ property provided by the trigger action

1.b. End Date: Select the ‘End Date’ property provided by the trigger action

1.c. Interval: Select and interval

Send an email

2. Add the ‘Send me an email notification‘ action

2.a. Subject: Configure the email subject value as required

2.b. Body: Configure the email body as required, including the ‘Result‘ property provided by the ‘Get Date and Time Difference‘ utility action

Upon execution, the following email will be generated:

Please email our support team if you require further advice or support regarding Encodian utility actions. You can do so at support@encodian.com; the team would be delighted to help.

Generate a Random Number in Power Automate

Generate a random number in Power Automate

Post #15 of our Build Flows Faster series covering our new utility actions for Power Automate. We will publish many more posts and accompanying videos over the coming weeks, but today’s lesson is about generating random numbers in Power Automate.

Our utility actions are targeted at helping you build flows more quickly with less complexity, and they brilliantly only use 0.05 actions from your Encodian Flowr and Vertr subscription allowance. For example, under a standard plan that permits 500 actions per month (uses), you could use the utility actions 10,000 times!

Introducing the ‘Generate Random Number’ Utility Action

The ‘Generate Random Number‘ utility action for Power Automate formats generates a random number confirming the configuration supplied. The action allows a minimum and maximum number to be set.

Video Guide: Generate a Random Number in Power Automate

Instructions: Generate a Random Number in Power Automate

The Trigger

As with many of our utility actions, there are a myriad of scenarios where you may need to generate a random number. Therefore, we’ll focus on how the ‘Generate Random Number‘ utility action works as opposed to focusing on a specific scenario. Consider the following basic and manually triggered flow:

That’s it! We’re just going to start the flow manually and generate a random number before emailing the value to the flow initiator.

Generate a Random Number

1. Add the ‘Generate Random Number‘ utility action to the flow, accepting the defaults

Send an email

2. Add the ‘Send me an email notification‘ action

2.a. Subject: Configure the email subject value as required

2.b. Body: Configure the email body as required, including the ‘Result‘ property provided by the ‘Generate Random Number‘ utility action

Upon execution, the following email will be generated:

If you require further advice or support regarding Encodian utility actions, please email our support team at support@encodian.com where the team would be delighted to help.