top of page
Writer's pictureIlia Zelenkin

How to extract data from Bank Statements in Sharepoint to Excel with Power Automate - part 1

Introduction

This instruction will guide you through the process of extracting data from bank statements that are loaded into Sharepoint and transferring it to Excel. By following these steps, you can automate the extraction process and quickly analyze the data in Excel. The biggest difference between a simple bank statement OCR is that with Bitskout you get structured information ready to be used. In the second part, you can learn how to automatically import bank transactions into your spreadsheet.



Video Instruction



Step 1: Set up SharePoint Flow:

Once you've clicked New Flow, the first thing you'll need to do is to choose a trigger. In our case, we'll use Sharepoint. Use the search to find Sharepoint triggers and choose "When a file is created or modified".



Choose the whole site URL in the trigger settings. Select the library name where the bank statements are stored. Load the files into the root folder of the library.





Step 2. Get file content

The next step is to add the action from Sharepoint called "Get file content using path".



Make sure to choose the full path from the previous step.


Step 3. Create Bitskout plugin

Go to and search for the Bank Statement template.

Click Use Template and then select Power Automate as the output.




Step 5: Add Bitskout action to Power Automate Flow

Now, let's go back to your Power Automate flow and add the Bitskout action. Choose "Run plugin for a file" and then select your plugin in the list of plugins.





Select the bank statements plugin created using Bitskout templates.


Then in the "File URL" we'll need to add an expression:


You do it like this - choose "Expressions" and type using your keyboard:


base64()

Then click inside the brackets switch to the Dynamic Content tab and choose File Content.

Once you click on File Content, it will automatically populate the value.



Step 4. Let's run a test now

To run a test, click "Test" at the top right corner. Then you'll need to add the file.

Once the test has finished, you'll see the results.




Step 7: Copy the JSON Output

Once the test is complete, copy the "outputs" by clicking on it and pressing Ctrl+C. We will need this information in the next step of our flow.



Step 8: Parse the JSON Output

Click on Edit in the top right corner to exit the test and add a "Parse JSON" action. Microsoft Power Automate needs to know what kind of information it will receive from the Bitskout plugin. Then click on "Generate from Sample" and paste the output there:




Step 9: Export Data to Excel

Add a "Add a Row into a Table" action and select your OneDrive file and table. As the JSON module has provided us with parsed information, you just need to map the fields to the correct columns.





Conclusion

As you can see the setup of bank statement data extraction is pretty straightforward. There are some steps that are somewhat complex, however, we a few tries you'll get a grasp of MS Power Automate quirks.


In the next part, we will show how you can process transactions. We will be updating the same flow and you'll find the exported flow attached to that article.


56 views0 comments
bottom of page