Learn how to solve the problem of getting a large quantity of bank transactions from multiple PDF file formats into Sage Accounting by importing a single file.
Once these bank transactions are in the Sage accounting software it can be allocated to the different categories of income, expenses, customers and suppliers.
Watch the below video where I prove why the Sage Accounting software is one of the easiest to work with and the most efficient accounting system on the market.
The Good, The Bad, and the Slow…
Year’s back when I did my accounting training, bookkeepers had to capture these types of transactions by hand on an accounting program.
Later in the video you will see that there are 580 transactions in this example.
It simply will just be too costly to manually capture this bank data, also considering the errors that are going to happen.
There is a much faster way of doing this task while also eliminating errors.
In this article and the accompanying video we will be following the steps listed:
- Start By using Dext Online Software to extract the bank data by uploading the PDF files.
- Then we are going to download the processed and converted excel bank statements from Dext.
- Our third step is to combine all 12 months bank data in a single file while ensuring that the bank balances on an on going basis.
I am going to show you a set of simple procedures to follow in order to achieve a seamless import that you can employ in your business to get fast, efficient results. - The next step will be to prepare the data for the Sage Accounting Import. I am going to share important information that will help you to avoid certain pitfalls and ensure a smooth import of your data into Sage Banking.
- Lastly in step 5: I am going to get all the transactions for the year into Sage Accounting with a single import.
Step 1: Uploading the bank transaction statements to Dext
Here are the 12 months PDF bank statements received and downloaded onto my PC ready to be uploaded to Dext.
I am now in my Dext online account. In the top navigation bar click on the green +add Documents button.
Another menu will open on the right. Click on the bank button at the top of the window.
Accept the charges which are charged per page converted from PDF into an EXCEL file format.
Select the bank account name from the list. If your bank is not listed add it to the bank list first and then go to the “Add a bank account” screen.
Drag and drop the PDF files from where you have saved it on the green block.
You will get a message: “document/s uploaded successfully and that you can find the extracted document in the “collected statement” area of the bank.
I am going to close this section and on the left-side navigation bar under the Bank heading you have the Collected Statements. There you can follow the progress of the extraction process. When the extraction is completed the file will be saved under the “processed statements” section also on the left navigation bar.
All the details of the file are revealed, such as:
- starting date and
- ending date of the transactions as well as
- the opening and
- closing balances of the bank
I have received the bank statements in monthly PDF files. I am now going to follow the same process with the other months files. Once that is completed all the months files for the year can be seen under the “processed statements” section.
Step 2: Downloading the Excel converted files from Dext
To combine all the data in a single file I am going to download each period’s file to my PC by clicking on the green Download button.
Here I am going to give you an important tip: Mark each file by clicking on the checkbox next to the blue file name.
This will ensure that you know which files you have already downloaded and not get confused by what you have already done and haven’t done yet. I am going to do the same for each month until I have downloaded all the files in the financial year which starts on the 1 March 2021 and ends on the last day of February 2022.
All the downloaded files are in excel format can be viewed in my downloads folder.
From excel we can easily change the file format to CSV which is the format needed for a Sage import.
I am going to open the first month’s file for March 2021. Then I am going back to the downloads folder and opening the next month’s file from April to 1 May 2021.
We select all the transactions for the period. Copy the selected area (Cntr+C).
Go back and select the previous month’s file (March 2021) and paste (Cntr+V) the transactions copied under the last transaction line.
Here comes an important part and that is to ensure that our balance agrees with the data copied into the file.
For us to check that I am simply going to recalculate the balance by taking the:
- Previous month’s closing balance = E72
- Add the receipts column + D73 and
- Deduct the payments column – C73
- Press enter
This calculated balance (R15,663.31)must agree with the actual bank statement balance in cell E73. As you can see the 2 balances agree.
Then we copy the formula right to the bottom so that we can note any discrepancies of the calculated balance vs the bank statement balances.
As we scroll right to the bottom of the data and the last day of bank transactions you can see that the balances agree.
This little step of checking the balance is very important as it removes any doubt about the integrity of the data and gives you confidence that there are no errors and that all the transactions are included.
It is important to obtain this assurance early on in the import process, because finding the cause of an error much later will be extremely difficult and time consuming to correct as you have to backtrack all the steps that you have taken. Many things can go wrong, for example you may exclude a line from being copied to your data.
You repeat the process again for all the months of the year that you wish to import until all your bank transactions are copied over into the single excel file.
Watch the video linked in this article to follow along.
Knowing that the data is accurate, valid and complete I can delete the column in which the bank balance was recalculated.
At this stage we have all the transactions for the year contained in 5 columns in excel.
- Date
- Description
- Debit
- Credit
- Balance
This is how you will receive your data from banks and other sources and how it will be categorised and displayed most of the time.
Unfortunately the data needs to be reorganised and combined in order to get it in shape for importing it into Sage Accounting. But don’t stress it.
I am going to reveal exactly how it is done so that your import can be successful.
I have also made another video previously on how to import a CSV bank transaction file if you want to check that out here.
Step 3: Get the Sage Bank Import Template
I prefer to work from templates when I can because I don’t need to reinvent the wheel so to speak. In step 1 I get the CSV file template for a Sage bank import. The template can be found on our website here.
- In the CSV file template there must only be 3 columns of data and nothing more. If your file has data somewhere else in the file, the import will fail.
- The 3 headings that you must have are: Date, Description and Amount.
- The date format of the column must be day/month/year.
- The Description and the Amount columns format must be general.
The template is already pre-designed and ready to be followed as a guideline for importing bank transactions in Sage Accounting.
The template has 4 lines of transactions which I will remove and replace with my own set of transactions.
Step 4: Reorganise your data according to the 3 columns of the Bank Import Template
In Step 4 I am going to reorganise my data according to the 3 columns of the Bank Import Template.
4.1 Sort the data according to the Debit column for all the payments.
4.2 Delete the balance column.
4.3 Adding payments column to receipts column
We only need 1 column for the amounts with the payments being a negative (they will be categorised as Spent in Sage) and the receipts as a positive. This is simple to achieve by copying the data that we want and adding a negative to the formula (=-C2).
We then copy the formula down in the Credit column to include all the debit transactions. I need to turn the formula into a value because I am going to delete the ‘Debit” column. This I do by Copying the data and pasting it as a value.
I then delete the third column with the heading “debits”
4.4 Sage Accounting Bank Imports only allows 100 characters in a cell
It is very important to know that your data import will fail if any of the cells has more than 100 characters. The description column of my data is the only one that I need to check because it is the only one that seems long. For that I am going to use the LEN formula in excel to give me the numbers of characters in each cell.
Starting at the first description cell =LEN(B2), I get 65 as the number of characters counted in that cell.
To give me the count of all the cells we copy the formula down to all the data.
I give the column a temporary heading to identify it using Length.
Then I sort the data in the length column from largest to smallest.
The values returned gives me 2 cells that are more than 100 characters, but it could have been many cells. I am going to give you a formula now that you can use on a large number of cells to reduce their length.
The formula is =LEFT (keep data from the left (B2 for the cell that I want to reduce the characters and then less the difference between the count D2 and a 100.
=LEFT(B2,(D2-(D2-100)))
In column F I am quickly checking that the formula worked and that there are only a 100 characters in the cell E2.
I now copy the formula to all the cells that have more than 100 characters and I replace the old description data with the new reduced data by copying and pasting the values. You can see in column D that the number of characters is now only a 100.
It is important to note that I have removed the excess characters from the right and only on 2 cells but there could have been hundreds of these cells. Hopefully by following this process you will be able to handle large amounts of data yourself.
I don’t need Columns E, F and D anymore and I delete them.
4.5 Count the number of transactions as an import check
Scroll to the bottom of the data in order to count the number of rows. There are 581 entries minus the heading which means that I must have 580 transactions imported into Sage Accounting. If I get that right my import will be successful.
You must be aware of the 2000 limit on unallocated transactions in Sage banking. If you have more than 2000 transactions you will need to first mark transactions as reviewed and have them removed from the new transactions screen before you can add more transactions again.
Step 5: Importing my data into the Sage Bank Import Template
In Step 5 I am going to select all my data excluding the headers, copy and replace the data in the Sage bank Import Template file. But keeping the template’s header row. I then save the file under a new name in order to keep my import template intact.
Now my file is ready for import.
The next step is a little bit scary because if the import fails, you have to backtrack your steps to find the error. This can be time consuming and the most frustrating part of doing bank imports to Sage Accounting, but if you have followed my Steps until now, you should be fine.
Here I am in the dashboard screen of Sage accounting. In the top navigation bar
- go to banking,
- Down to transactions
- And then to the right to Banking and click on it.
In the list of banks have your selected bank account. My default bank is already set.
Under the New Transactions screen:
- click in the blue Import Bank Statements button
Under the File Import details section and next to the Import File Type select CSV.
- Click on the Browse button next to the Import File.
- Select the CSV file you have prepared for the import, mine is the Sage YouTube Import from PDF file and
- Click on upload
- Your file has now been selected for import and click on
- The blue “Import File” button.
You will get the “please wait” widget.
Once the import is complete you will get the Information widget saying “Your CSV file imported successfully.”
As well as the quantity of transactions imported. The 580 rows imported agrees with the rows counted earlier in the video. This gives me assurance that all the transactions have been successfully imported and I can now proceed with confidence allocating the bank transactions.
When I scroll to the bottom of the screen of the transactions you can see that there are 580 transactions displayed in the “new transactions” area of Sage banking.
Sage banking is the most user friendly, efficient software that I have tested thus far.
Hopefully it is also clear to you why I prefer to use Dext and Sage to do all the difficult time consuming tasks for me and assist the business to be easy, quick and precise.
