Posts

bnkSEPA : Excel to SEPA Credit Transfers (SCTE) Electronic Payment

Image
Last update: 202205 Version - 1.37.00 Project Repository:  https://www.github.com/chribonn/bnkSEPA Project Overview This is an engine written in Python that takes a Microsoft Excel spreadsheet and converts into a password-protected file that will be processed by Financial Institution. Other financial institutions that use the same format may also benefit from this  ETL  ( Extract Transform Load ) engine. The Excel workbook consists of the following worksheets: Payment Information Record - This sheet holds information of the payee account. This will be entity that will upload the file to the bank Credit Instruction Record - This sheet contains the transactions that are to be processed. The maximum number of transactions can be increased (this has already happened). An increase in the number of transactions will require changes to both the Excel and the Python code. Control - This worksheet performs the following functions: It stores within it the password provided by the bank to encrypt
Post a Comment
Read more

ETransL Technical: Update MetaData

Image
Quick overview This module is invoked by all Operations except  SK  (which does nothing). The invocation takes place before the actual operation is evaluated. It evaluates the  MetaData  string, and replaces any reference to a Title within the Template with its data. The RegEx pattern the solution looks for is '<;<(.+)>;>'. There can be multiple Titles. The module looks up Titles that precede the one being processed. If a Title is not found, the string is treated literally and a warning is written to the log file. Example <;<Fare_1912>;> x <;<Inflation>;> Before Operation  CO  is evaluated, this module will replace  <;<Fare_1912>;>  and  <;;>  with the results obtained in those Titles. If the result of Title  Fare_1912  was 7.63 and the result for  Inflation  was 29.98, the MetaData string would be updated as shown below before the module related to Operation  CO  is called. 7.63 x 29.98 Flow Diagram Solution walk through Fo
Post a Comment
Read more

ETransL Technical: Process Transactions

Image
  Quick overview The module performs the transformation. Each row in the transactions file will correspond to an appended entry in the transformation file. Any errors or warnings are written to the log file. It can be further subdivided into three: PreProcessing tasks Operations executions PostProcessing tasks Flow Diagram Solution walk through Initialise the Transformation file Initialise the  Transaction Counter  to zero Initialise the  Current Row  to the row reference in the transaction file While  Current Row  is less than or equal to the  Last Transaction Row  or the row referenced by  Current Row  is not blank Increment the  Transaction Counter  by 1 Initialise the  Output String  that will eventually be written to the transformation file Start Processing the Template file starting at column B onwards until there is a blank column. If the Operation is not one of " SK ,  CO ,  MC ,  TX ,  RW ,  IC ,  CM ,  CF " log this as an error and stop processing. If the Operation
Post a Comment
Read more

ETransL Technical: Load and Validate the Template Workbook

Image
Quick overview The template workbook is the document that drives the ETransL engine. It is possible to have different templates processing the same transaction file. ETransL computing engine will, for each row in the transaction file, process the template, commencing at column B onwards until it encounters the first blank column. This module will: size arrays for the different parameters that will be read from the template file validate that all the columns in the template are valid populate the arrays with the read data. Flow Diagram Solution walk through Determine the total number of columns that are make up the Template Workbook. The starting position is user defined. Create arrays to store the different parameters that make up a single template column. The following arrays will be created: Title - Each column must have a unique title. This is because an operation may reference another column and the title is the way these can be uniquely identified. Operation - This describes the 
Post a Comment
Read more

Technical: ETransL High Level Technical Overview

Image
ETransL execution is as follows: Initialises the environment, reads from the Inputs  Worksheet the inputs and logs everything: Mandatory parameters: Transaction Workbook Name Transaction Workbook Sheet Template Workbook Name Template Workbook Sheet First Transaction Row Parameters that will be auto filled if left blank: First Transaction Row - Assumed to be ' 1 ' Last Transactions Row - Assumed to be ' 1048576 ' Template Start Row - Assumed to be ' 1 '  Transformation File - ETransL will set the name to execution date and time ( yyyyMMdd-hhmm.txt )  Log file name - ETransL will set the name to execution date and time ( yyyyMMdd-hhmm.log )  Append to Existing Log File - Assumed ' Y ' Read in and process the Template file. The template file is laid out in columnar fashion with each column representing a single operation. Operations are described in detail elsewhere. Different operations can take additional parameters.  The following parameters are associat
Post a Comment
Read more

ETransL

Image
ETransL is  ETL  engine that converts data imported into Microsoft Excel to fixed or comma delimited text. ETL stands for extract, transform, load. It is the general procedure of copying data from one or more sources into a destination system which represents the data differently from the source(s) or in a different context than the source(s). It is often used in data warehousing.   Wikipedia page ETransL is an Excel VBA script that applies a template workbook to a transaction workbook to produce a text file that can be processed further on. The ETransL workbook is the computing engine of the solution. Through it one specifies: The Transaction Workbook & Worksheet The Template Workbook & Worksheet ETransL reads the Template file and applies the instructions in it on the Transaction File. The Transactions The Transaction Worksheet contains the data that will be transformed. The data can be any source that can be pulled into Excel. The name of the Transaction Workbook and Sheet d
Post a Comment
Read more

Join the effort ,;, Use the tool ,;, Spread the word

Image
ETransL is  ETL  engine that converts data imported into Microsoft Excel to fixed or comma delimited text.   We would like to develop this tool further and can only do so with   YOUR   help. Read the tutorials and Beta test ETransL by applying it to your data transformation projects. Is there a function not catered by ETransL. If its not on the wish list suggest it. We will evaluate al requests. Want to join the development effort. We are looking for: Github gurus VBA developers Documentation authors Social media influencers ETransL specialists to create use cases. Want more information? Stuck? Reach out. Visit:  ETransL GitHub page
Post a Comment
Read more