Showing posts from March, 2022

ETransL Technical: Process Transactions

  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

ETransL Technical: Load and Validate the Template Workbook

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 

Technical: ETransL High Level Technical Overview

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


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

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

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