Introduction: In this instructional guide, you will learn how to extract all the transactions from a bank statement into an Excel file using Power Automate. This method eliminates the need for external modules and allows you to parse the CSV file using only Power Automate tools.
This How-to instruction is the second part of our series regarding Bank Statement parsing. The first part for Outlook and SharePoint focused on extracting the key information from the statement.
In this instruction, we'll continue working on the same flow adding more actions.
Extract Bank Statement Transactions Video
Step 1. Parse the Bank Statement
Watch Part 1 of the video to understand the initial steps.
Now let's add the "Compose" component to split the CSV file into individual lines.
In the "Compose" component, use the "split" function to break the text into lines.
Pass the text from the JSON step called "transactions" as an input. Use the "code URI" function to represent a new line symbol ("%0a"). This step splits the CSV file into an array of lines.
This is how the final expression should look like.
split(body('Parse_JSON')?['Transactions'],decodeUriComponent('%0A'))Step 2. Break Lines into Columns
Now we have our lines. The next step is to go over each line and split it:
Find and add "Apply to each" component to iterate over each line.
Within the "Apply to each" component, add another "Compose" action.
Use the "split" function again to break each line into columns.
Specify the "item" as the input, which represents the current line. Use a semicolon as the delimiter.
This step breaks each line into an array of columns.
Step 3. Write Data into Excel
This is the trickiest part as we'll have to use a weird way to specify the values.
But first, create a spreadsheet or table in Excel (or any other tool of your choice).
Then add an action called "Add a row into a table":
To write the output, you'll need to specify the value of each column via an array expression. It is very simple - Click on Expression and type outputs()
Then click inside the brackets and write ('Compose_2') so it will look like this:
outputs(outputs('Compose_2'))Then add the following:
outputs('Compose_2')?[0]In this case, the value of 0 in the square brackets means the first column, and to input the second column the expression would look like this:
outputs('Compose_2')?[1]
So the final setup will look like this.
Step 4. Run the Test
Save the workflow and run a test. Use the previously used trigger or select an appropriate trigger. The data should appear in your Excel.
Conclusion
In this example, we tried to avoid using extra modules for CSV parsing. If you have any connector purchased, we recommend you use it. And once the flow is configured you can start processing bank transactions in a very efficient manner to your spreadsheets.