Parse Exact Online bank statements retrieved through documents table

Questions
, ,
1

Good evening,
I need a little boost here. I would like to retrieve and parse the REAL bank statements from Exact Online.

Not the accounting lines in bankentries@eol, because I feel this is accounting data, that can be erased, modified, inserted etc… I’ll wrote another topic on my feelings later in the forum.

I’ve written that:

select * 
from   exactonlinerest..documentsBulk@eol dct 
join   exactonlinerest..documentattachmentfiles@eol dae
on     dct.id = dae.document
where  dct.type = 40 
and    dct.Created > to_date('01032021', 'DDMMYYYY')
limit 50

I get nice:

  • DocumentViewUrl
    Capture d’écran 2021-03-10 à 20.05.58, and
  • Attachment from URL and Extension
    Capture d’écran 2021-03-10 à 20.06.07

Bank files are all text files like this:

MT940 contents from Exact Online
MT940 contents from Exact Online874×440 32.5 KB

So I would like to join with a function the content of the DocumentViewUrl and then use a REGEX to parse the data… but I’m stuck in getting the way for getting the content of the DocumentViewUrl as text.

2

The bank statements are in MT940 format. This is somewhat a standard, but not really. Each bank has it’s own deviations, depending on the record type.

Invantive SQL provides a MT940 driver, which can handle Rabobank files, but also has some quirks from ING, ABN and other large Dutch banks. It has been tested across approximately 1.000 companies and seems robust.

The recommended approach is to export the MT940 bank statement files to a folder using the Invantive Script statement local export documents.

After that, make sure to have a data container defined for the database you are using based upon the MT940 driver (driver code SwiftMt940Rabo).

In the connection string set directories to the list of folders containing the bank statements ad set extension to the file extension (default is *.swi).

Then use Invantive SQL queries on:

The various MT940 record types are folded into these. Text split across lines and other parsing issues are resolved internally in the driver.

3

Thanks, almost close to the recommended approach…

now only having difficulties with the connection chain for the SwiftMt940Rabo described here.

My connection chain is

<database order="16" provider="SwiftMt940Rabo" 
 alias="Bankfiles"
 connectionString="directories={c:\temp};extension={*.bas}"
/>

but on doing a

select * 
from   BankStatementsLines@Bankfiles

QueryTool says : Could not find connector for connection with alias Bankfiles

which obviously points out that I made a young Padawan’s connection string (also not shown in the Querytool available objects).

What is wrong with my connection string?

4

Hmm, hard to say so. What is the error code in front of the message (something like itgen...)?

5

sorry

itgencmr088: Could not find the connector for connection with alias ‘Bankfiles’.

Message ID: 04524277-9edd-4c9a-8ee4-3acfa0fbdd42Occurred (UTC): 17/03/2021 13:17:52select * from BankStatementsLines@BankfilesValidationException à Invantive.Basics.ValidationException…ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable1 uid, Boolean isRecoverable, String poolIdentityId) à Invantive.Data.ConnectionManager.GetActiveProviderByConnectionAlias(GlobalState owner, ExecutionOptions executionOptions, String alias, Boolean failWhenNotFound) à Invantive.Sql.V1.DataSourceOrFunctionTree.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, TableOrFunctionSpecContext input) à ISQL.MT1.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree context) à Invantive.Sql.V1.QueryTree.Prepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree parseTree) à Invantive.Sql.V1.QueryTree.CreateFrom(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, IParseTree parseTree, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Sql.V1.SelectStatementTree.VisitDataSource(DataSourceContext context) à Invantive.Sql.V1.SelectStatementTree.ExtractSelectStatement(GlobalState owner, ExecutionOptions executionOptions, UniqueSelectStatementContext context, CombineMethod combineMethod, AliasedColumn distinctOnColumns) à Invantive.Sql.V1.SelectStatementTree.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, SelectStatementContext context) à ISQL.MT1.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree context) à Invantive.Sql.V1.QueryTree.Prepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree parseTree) à Invantive.Sql.V1.QueryTree.CreateFrom(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, IParseTree parseTree, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Sql.V1.SqlEngine.YL(GlobalState , ExecutionOptions , IProviderManager , ParameterList , ExecutionEnvironment , IParseTree , LW ) à Invantive.Sql.V1.SqlEngine.WT.K(IParseTree ) à System.Linq.Enumerable.WhereSelectArrayIterator2.MoveNext() à System.Linq.Buffer1..ctor(IEnumerable1 source) à System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source) à Invantive.Sql.V1.SqlEngine.Execute(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Sql.V1.SqlEngine.Parse(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters) à Invantive.Producer.Windows.Forms.QueryToolResults.WOE(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) à Invantive.Producer.Windows.Forms.QueryToolResults.IOE(GlobalState , ExecutionOptions , String , ParameterList ) à Invantive.Producer.Windows.Forms.QueryToolResults.NK.W() à System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) à System.Threading.ThreadHelper.ThreadStart() à Invantive.Data.ConnectionManager.GetActiveProviderByConnectionAlias(GlobalState owner, ExecutionOptions executionOptions, String alias, Boolean failWhenNotFound) à Invantive.Sql.V1.DataSourceOrFunctionTree.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, TableOrFunctionSpecContext input) à ISQL.MT1.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree context) à Invantive.Sql.V1.QueryTree.Prepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree parseTree) à Invantive.Sql.V1.QueryTree.CreateFrom(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, IParseTree parseTree, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Sql.V1.SelectStatementTree.VisitDataSource(DataSourceContext context) à Invantive.Sql.V1.SelectStatementTree.ExtractSelectStatement(GlobalState owner, ExecutionOptions executionOptions, UniqueSelectStatementContext context, CombineMethod combineMethod, AliasedColumn distinctOnColumns) à Invantive.Sql.V1.SelectStatementTree.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, SelectStatementContext context) à ISQL.MT1.OnPrepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree context) à Invantive.Sql.V1.QueryTree.Prepare(GlobalState owner, ExecutionOptions executionOptions, IParseTree parseTree) à Invantive.Sql.V1.QueryTree.CreateFrom(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, IParseTree parseTree, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Sql.V1.SqlEngine.YL(GlobalState , ExecutionOptions , IProviderManager , ParameterList , ExecutionEnvironment , IParseTree , LW ) à Invantive.Sql.V1.SqlEngine.WT.K(IParseTree ) à System.Linq.Enumerable.WhereSelectArrayIterator2.MoveNext() à System.Linq.Buffer1..ctor(IEnumerable1 source) à System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) à Invantive.Sql.V1.SqlEngine.Execute(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Sql.V1.SqlEngine.Parse(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, LW queryPlanState) à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters) à Invantive.Producer.Windows.Forms.QueryToolResults.WOE(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) dans C:\Users\gle3.WS212\Documents\ws-master\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolAvailableObjectsViewModel.cs:ligne 55 à Invantive.Producer.Windows.Forms.QueryToolResults.IOE(GlobalState , ExecutionOptions , String , ParameterList ) dans C:\Users\gle3.WS212\Documents\ws-master\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolAvailableObjectsViewModel.cs:ligne 30

6

Well, I am definitely a young Padawan, not yet a Jedi.

To make Query Tool load changes in settings.xml, disconnect/reconnect is not sufficient! Need to quit and restart the Query Tool. Now it works like a charm !!!

7

Good morning,

today I’ll share my full script, it could be useful for others.

Prerequisite:
Include in your settings.xml the connection to the local folder where you’ll export the files from Exact Online.

...
<database order="16" provider="SwiftMt940Rabo" 
        alias="Bankfiles"
        connectionString="directories={c:\temp};extension={*.bas}"
...

Note1: Don’t forget to restart Query Tool. a simple disconnection/reconnection is not sufficient, Query Tool will not take in account any change to the settings.xml.
Note 2: Exact Online will give you all bank files, including PSP files like Mollie. In my case I only want my ABN AMRO files that comes as *.bas files. Mollie returns JSON files.

Let’s start!
First we get documents from Exact Online

create or replace table statements@inmemorystorage 
as
select * 
from   exactonlinerest..documentsBulk@eol dct 
join   exactonlinerest..documentattachmentfiles@eol dae
on     dct.id = dae.document
where  dct.type = 40 
and    dct.Created > to_date('20032021', 'DDMMYYYY')

Notes:

Then we do a local export

local export documents in AttachmentFromUrl to "c:\temp" filename automatic

Then we gonna use the SWIFT MT940 driver of Invantive described by @guido.leenders.

In my case I adapted and filtered the query to view:

  • only incoming SEPA overboeking,
  • excluding some specific contents like ADYEN, BRINK, MOLLIE for incoming money ,
  • only on one bank account myIBANaccountnumber.

I have added some little REGEX to beter parse the content as it seems that SwiftMt940Rabo does not understand everything.

select FileName
,      StatementAccountidentification
,      StatementStatementNumber
,      StatementTransactionReferenceNumber
,      StatementClosingBalanceEntryDate AS Date_Received
,      ValueDate
,      Amount
,      EndToEndReference
,      ReferenceAccountOwner
,      RemittanceInformation
,      regexp_substr(InformationToAccountOwner,'(?<=\/NAME\/)(.*)(?=\/REMI)') AS Name
,      regexp_substr(InformationToAccountOwner,'(?<=\/IBAN\/)(.*)(?=\/BIC)') AS Sender_IBAN
,      Unparseable
,      InformationToAccountOwner
from   BankStatementLines@Bankfiles
where  StatementAccountIdentification LIKE '*myIBANaccountnumber*'
and    DebitCredit LIKE 'C'
and    regexp_substr(InformationToAccountOwner,'(?<=\/NAME\/)(.*)(?=\/REMI)') NOT LIKE '%ADYEN%'
and    regexp_substr(InformationToAccountOwner,'(?<=\/NAME\/)(.*)(?=\/REMI)') NOT LIKE '%BRINK%'
and    regexp_substr(InformationToAccountOwner,'(?<=\/NAME\/)(.*)(?=\/REMI)') NOT LIKE '%MOLLIE%'
order 
by     StatementStatementNumber DESC

Result :

SWIFT MT940 transactions from Exact Online
SWIFT MT940 transactions from Exact Online2864×916 942 KB

8

Please note that the new BETA releases after January 21, 2023 contain a Mollie driver: New SQL and Power BI driver: Mollie Payment Provider. Documentation is available on https://documentation.invantive.com/2017R2/mollie-data-model/webhelp/index.html.