Importing, Validating and Processing CSV Bank Statements to SQL Server Database

Bank Statement Import CSV


Case Study

PART 1 -Solution Demo - Importing the Bank Statement

PART 2 -Solution Demo - Matching to Client Debt Records

Goodbye Manual Processes, Hello Productivity!


The Story

David Evans

David Evans, 


Addlestone Keane Solicitors

Shauna Appleby, 


Addlestone Keane Solicitors
Richard Briggs

Richard Briggs, 

Software Engineer

Lean Software Ltd
Simon Stell

Simon Stell, 

Managing Partner

LCF Law Solicitors

Lean Software Ltd were recommended to Addlestone Keane by Bradford based company LCF Law in the UK.  LCF Law have used Lean Software consistently over a number of years to help with their own data processing goals.

David Evans, Partner at Addlestone Keane Solicitors, turned to Lean Software Ltd and interviewed Richard Briggs founder of Lean Software - who proposed the new Excel Database Tasks (EDT)  based system as described in this case study.  Confident in the company's own software, Richard estimated the described activity (Importing and Processing Debt payments, Reporting and data Cleansing) could be delivered in just 10 days using the EDT software.


Who are Addlestone Keane, what do they do?

Addlestone Keane are a commercial law firm based in the City center of Leeds, UK.  Part of their operation handles debt collection for their large commercial clients.

A significant number of debt repayment transactions appear on the company's current bank account statement on a daily basis - a system was required to (as far as possible) automate the accounts handling of the payments-in and payments-out from the debt department on a daily basis.


What were the customer's goals?

Existing office procedures consisted of a largely manual process of manually entering every transaction against the debtors record, these records are held in a Case Management System called Partner for Windows, based on SQL Server supplied by Tikit PLC.  This being a time consuming and error prone process.

David Evans had a vision of automating the entire existing process, but despite previous IT company's efforts achieving the goal remained elusive.  The system required interwoven special conditions that proved too challenging for previous automation attempts and software.


What were the customer's needs?

Crucially a system was required where a user could confirm matching and processing, no fully automated process without user interaction could ever be used.

Lean Software promised EDT could deliver these requirements - 

  • To import (with user interactive validation) a daily CSV (comma separated value) transaction file, downloaded from the client's bank website
  • Credit transactions usually (but not always) contain one of two different reference numbers styles, each had to be matched using complex SQL to a debtor record and presented to the user as a match list for the user to confirm or edit the match
  • On allocation the system would automatically post a debtor payment credit to the debtors matter to the SQL database of the Case Management system Partner for Windows
  • A system of then authorising payments out to the company they are collecting money for was required.   Special fairly complex rules existed as to when payments out should be made.
  • Payments-out had to group payments-in together for particular lender-departments and create an amalgamated cheque/BACS request in the SQL Case Management system
  • Several integrated reports were required, data from the reports had be easily saved as Excel, Word , or PDF format
  • a special 'Data Cleansing' task was required to sort out historical mistyped data where invalid client references are stored

 Can you think of any software that could possibly handle all of this complexity, yet be really user friendly and the solution be delivered in just 10 days?

We don't think you will be able to - but now you can - Welcome to Excel Database Tasks (EDT)  Version 4.0 ...

Solution Components

There are various additional EDT Software Tasks delivered for Addlestone Keane - - these highlight features of the EDT software that provide such a smart solution.


 TASK 1 - Import Daily Bank Transactions

TASK 2 - Allocate Credit Transactions, Post Credits

TASK 3 - Amalgamated Transactions, Post Credits

TASK 4 - Authorise Payments Out

TASK 5 - Payments Out, Post Client Debits

Other tasks that produce reports and tasks that maintain standing data were also provided,

 Bear in mind this is just an example of one company's needs - once you realise how powerful the EDT software is - particularly when working with SQL Stored Procedures - there is no limit but the imagination as what EDT can do in terms of processing batches of data.  

 Remember Lean Software can handle building the EDT Tasks for you if you require.

  • Lets us work it out for you..

    validate import excel to sql server
    • Lean Software specialise in building Data Processing Tasks for companies.    
    • We will build a proof of concept task for you
    • Just contact us now and get the perfect solution in record time ... you will be surprised what we we can do for you!