In the #PowerShot, I will show you how to format unstructured data, CSV data to an appropriate table in Power Automate using Win Automation and PowerShell. Currently, Power Automate does not directly support parsing CSV data or converting it to excel so that it can be further used in the consecutive actions along with the excel connector.
Let's Get Started!
Consider a scenario where an analyst or another system sends an automated weekly report in the form of CSV. This file needs to be processed and further data needs to be read in Power Automate. Currently, there is no direct action that supports this and the actions that do are from connectors that might require an external subscription to the service provider. If you already have a license to run flows in Power Automate with Attended or Unattended RPA, you can use the actions from Win Automation to structure the excel data into an appropriate table that can then be read via the Excel Online Connector in Power Automate.
Win Automation
Step 1: We will get the path of the temporary Excel file from the main flow (in Power Automate) via command line arguments. Therefore, the first step in the process is to Get the Command Line Arguments.
Step 2: Once the file path is retrieved (note that the command line arguments return a list of inputs. We need the first one only as the file path is being passed from the flow), we will add an action to Launch excel and open the file.
Step 3: After the excel instance is open and the context is set, we need to get the first free column from the excel spreadsheet. This is to get the number of columns in the sheet.
Step 4: After retrieving the number of columns, we will add a step to get the actual number of columns that is by subtracting one from the outputs of step 3.
Step 5: Once the column number is retrieved, we need to read the header row (to be later passed in the PowerShell script) and so we add the step to read from the excel worksheet and configure the inputs based on the previous actions.
Step 6: Finally once all the relevant data is stored in the variables, close the excel instance.
Step 7: The last step is to run the PowerShell script and first import the data as a data table with relevant headers and then create a formatted excel sheet with the data as a table.
PowerShell
$ImportedData = Import-XLSX -Path %CommandLineArguments[0]% -Header %ExcelData[0]%
$ImportedData | Export-XLSX -Path C:\yourpath\FormattedExcel.xlsx -Table -Autofit -Force
Reference documentation for the PSExcel module in PowerShell.
Power Automate
We will create a UI Flow to call the Win Automation process and then invoke the UI flow in a main flow.
UI Flow
Step 1: Create a UI Flow for a Desktop app and configure a simple text input. This input will contain the path of the temporarily created Excel files that will be used to create the formatted data.
Step 2: Add the Run Win Automation action and provide the path of the process. In the Command Line Arguments property of this action, directly pass the inputs. Save and close the UI flow.
Flow
Step 1: Trigger- When an email arrives: To trigger the flow when a new email arrives with the subject "Format Excel". Enable the Include Attachments and Only with Attachments properties in the action.
Step 2: Action- Get Attachment: To get the excel file attached in the email. Expression used in attachment id: first(triggerBody()?['attachments'])?['id']
Step 3: Action- Create File: to create the temporary excel file using the file system connector. The path of this file will be passed as an input parameter to the win automation process.
Step 4: Action- Delay: to wait until the file is created. (A 3 second delay has been added in this flow)
Step 5: Action- Run a UI Flow for Desktop: to invoke the Win Automation process from the UI Flow created in the previous section. Pass the file path from the create file action as an input parameter to this action.
Step 6: Action- Delay: to wait until the file is created by the PowerShell script and the Win Automation process is completed. (A 5 second delay has been added in this flow)
Step 7: Action- Get file metadata using path: to get the file metadata of the newly created formatted excel file from the file system connector.
Step 8: Action- Get file content: to get the file content of the of the newly created formatted excel file from the file system connector.
Step 9: Action- Create file: to create a new file in SharePoint document library with content from the newly created formatted excel file.
Step 10: Action- List rows present in a table: to get the rows from the formatted excel worksheet.
Setup in Action
In this post, we saw how to process an excel sheet that is in an email attachment and convert the data to a properly formatted table. This process is useful when you want to retrieve unstructured data from excel or parse CSV data and convert it to excel to use it along with the Excel Online connector.
I hope you found this interesting and it helped you. Thank you for reading!