Remove manual data entry in Spreadsheets using Power Automate

I have a customer who wants to remove manual data entry in spreadsheets. He has a daily monotonous task of manually entering bank reconciliation transactions from his bank account into his accounting system He knew this was a waste of his time and asked me if I could come up with a solution to automate this procedure.

Power Automate

Indeed, I could by using Power automate.

The customer provided me with

1. A spreadsheet template file that can be used to be imported into the financial system.

2 – A sample of the CSV file of the receipts that the bank provides on a daily basis.

This is the how I removed manual data entry using Power Automate.

1. Set up a SharePoint Document Library with 2 Folders.

   1. Input – A place the Bank CSV files will be placed every day.

   1. Output – The place for the completed financials spreadsheet

2. I created a Microsoft Power Automate flow with the following steps.

   1. Trigger – This is a daily trigger that automatically starts the following conversion process.

   1. Initialize a bunch of variables that includes:

      1. An Array for storing the CSV data so it can be “chopped up” to be placed in the spreadsheet

      1. Text Variables for each of the columns that need to be migrated.

Then create an “Apply Each” Loop to read through the array and grab each column

Then within the loop populated the spreadsheet with he matching variables.

This flow has given my client the ability to remove manual entry in Spreadsheets.