Tutorial

  • 00 Introduction to Active Table Editor

    Introduction to Active Table Editor

    We have delivered a lot of data transformation and integration projects and one day we realized, that we need to put something on the end-user computer, which allows editing lookup tables, running reports and performing calculations. Our users did not want to run SQL plus or Microsoft SQL Server Management Studio. It is just too complex for them.

    This is how Active Table Editor was born.

    All our ETL tools have no limitations, which means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • 0001 How to rename the file using the date as part of the filename

    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new package and open .wfp file

     How to rename file using current date as part of file name

    •  Amend File Operation Properties and run the package by pressing the green arrow.

     File Operation

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0002 Automate Downloading IP Location's data file

    This Example Demonstrates how to Download IP Locations data file and how to prepare it for range lookup
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new directory and call it IP Locations
    • Create a new transformation and open .ats file
    • Create a new package and open .wfp file

    Directory 

    Getting Refresh token

    dowload ip location

    package

    • Double click on Set Variables package action and amend values of DOWNLOAD_TOKEN and DATABASE_CODE

    set variables

    • Double click on transformation and select 0002 IP Locator Transformation

    transformation action properties

    •  Run the package by pressing the green arrow.

    Note: transformation is used to prepare data for the range lookup transformation function

    transformation

    About IP2Location

    IP2Location™ is a non-intrusive geo IP solution to help you to identify visitor's geographical location, i.e. country, region, city, latitude, longitude, ZIP code, time zone, connection speed, ISP and domain name, IDD country code, area code, weather station code and name, mobile carrier information, elevation &amp; usage type<br>using a proprietary IP address lookup database and technology without invading the Internet user's privacy.

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0003 Replace LF character with space within the file

    This Example Demonstrates how to Replace LF character with space within the file(s)
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new transformation and open .ats file

    Directory 

    • Double click on the reader and point the directory at the source directory

    Directory

    • Repeat the process for the data writer (Point it at the target directory)
    • Run the transformation by pressing the green arrow.
    How it works:
    • Data reader reads the entire content of the file into the memory
    • The transformation runs Replace characters transformation function
    • The writer writes the result into the file within the target directory

    Directory

    Directory

    Source file example

    Directory

    Result file

    Directory

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0004 How to read a single excel cell value

    This Example Demonstrates how to read a single excel cell value and email it
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new package and open .wfp file

     How to rename file using current date as part of file name

    • Double click on Excel Operation Action Properties and change the file name, sheet and cell address 

    How to rename file using current date as part of file name

    •  Check cell variable (note variable #TOTAL_SALES#  name)

    How to rename file using current date as part of file name

    •  Select existing or create a new SMTP connection.

    How to rename file using current date as part of file name

    •  Update email text (note #TOTAL_SALES# variable)

    How to rename file using current date as part of file name

    •  Run the package by pressing the green arrow.

    More Information

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0005 How to Transform a Simple XML file

    This Example Demonstrates how to Convert simple XML into a delimited file
    Source XML

    Here is an XML example, it is very well structured therefore there not need to perform additional transformations.

    <Table>
    <Record>
    <ID>1</ID>
    <Company>James Bond Production</Company>
    <Amount>13</Amount>
    </Record>
    <Record>
    <ID>2</ID>
    <Company>Green Cloud</Company>
    <Amount>14</Amount>
    </Record>
    </Table>

    Steps to follow
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new transformation and open the .ats file

    XML ETL Transformation 

    • Double click on the reader and select source.xml file

    XML Reader

    • Repeat the process for the data writer (Point it at the result.csv file)

    ETL Data Writer

    • Run the transformation by pressing the green arrow.

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0006 How to Transform a Complex XML file

    This Example Demonstrates how to transform Complex XML into a simple, more readable format

    The problem with XML

    Here is an XML example, it is very well structured therefore there not need to perform additional transformations.

    <Table>
    <Record>
    <ID>1</ID>
    <Company>James Bond Production</Company>
    <Amount>13</Amount>
    </Record>
    <Record>
    <ID>2</ID>
    <Company>Green Cloud</Company>
    <Amount>14</Amount>
    </Record>
    </Table>

    However there is no guarantie that next file will have exactly the same structure, for example this XML has a different tags order.

    <Table>
    <Record>
    <Company>James Bond Production</Company>
    <ID>1</ID>
    <Amount>13</Amount>
    </Record>
    <Record>
    <ID>2</ID>
    <Company>Green Cloud</Company>
    <Amount>14</Amount>
    </Record>
    </Table>

    This XML has additional YEAR tag

    <Table>
    <Record>
    <ID>1</ID>
    <Company>James Bond Production</Company>
    <Year>1956</Year>
    <Amount>13</Amount>
    </Record>
    <Record>
    <ID>2</ID>
    <Company>Green Cloud</Company>
    <Amount>14</Amount>
    </Record>
    </Table>

    This kind of of problems can be easily addressed by using XSLT:

    <?xml version=“1.0” encoding=“UTF-8”?>
    <xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
    <xsl:output method=“xml” indent=“yes” version=“1.0”/>
    <xsl:template match=“Table”>
    <Table>
    <xsl:for-each select=“Record”>
    <Record><ID><xsl:value-of select=“ID”/></ID>
    <Company><xsl:value-of select=“Company”/></Company>
    <Amount><xsl:value-of select=“Amount”/></Amount>
    </Record>
    </xsl:for-each>
    </Table>
    </xsl:template>
    </xsl:stylesheet>

     Note: XSLT is a way of transforming XML into a different format

    Steps to follow
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new transformation and open the .ats file

    XSLT ETL Transformation 

    • Double click on the reader and select source XML file

    XML Data Reader

    • Repeat the process for the data writer (Point it at the result.csv file)

    ETL Data Writer

    • Run the transformation by pressing the green arrow.

    Press magnifying glass button to amend the XSLT transformation 

    XSLT Editor

     

    Testing XSLT Transformation

     

    XSLT ETL Transformation Result

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0007 How to Convert XML Attributes into Elements

    This Example Demonstrates how to convert XML attributes into XML elements
     

    Here is an XML example, all the data we need is stored as XML attribute so we need to convert into simpler XML format

     <Table>
    <Record>
    <CompanyData ID="1" Company="James Bond Production" Amount="13"></CompanyData>
    </Record>
    <Record>
    <CompanyData ID="2" Company="Green Cloud" Amount="146"></CompanyData>
    </Record>
    </Table>

    Here is XSLTtransformation:

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes" version="1.0"/>
    <xsl:template match="Table">
    <Table>
    <xsl:for-each select="Record">
    <Record> <ID><xsl:value-of select="CompanyData/@ID"/></ID>
    <Company><xsl:value-of select="CompanyData/@Company"/></Company>
    <Amount><xsl:value-of select="CompanyData/@Amount"/></Amount>
    </Record>
    </xsl:for-each>
    </Table>
    </xsl:template>
    </xsl:stylesheet>

     Note: XSLT (Extensible Stylesheet Language Transformations) is a language for transforming XML documents into other XML documents or formats

    Steps to follow
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new transformation and open the .ats file

    Convert XML attributes to XML tags 

    • Double click on the reader and select source XML file

    XML Data Reader

    • Repeat the process for the data writer (Point it at the result.csv file)

    XML Data writer

    • Run the transformation by pressing the green arrow.

    Press magnifying glass button to amend the XSLT transformation 

    XSLT Transformation

     

    XML Editor

     

    XML Result

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0008 How to Convert XML into CSV

    This Example Demonstrates how to convert multiple XML files into CSV files
     Here is an XML source example

    <Table>
    <Record>
    <ID>1</ID>
    <Company>James Bond Production</Company>
    <Amount>13</Amount>
    </Record>
    <Record>
    <ID>2</ID>
    <Company>Green Cloud</Company>
    <Amount>14</Amount>
    </Record>
    </Table>

    Here is XSLT used transformation:

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" encoding="UTF-8"/>
    <xsl:template match="Table">
    <xsl:text>ID,Company,Amount</xsl:text>
    <xsl:text>&#xA;</xsl:text>
    <xsl:for-each select="Record">
    <xsl:value-of select="ID"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="Company"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="Amount"/>
    <xsl:text>&#xA;</xsl:text>
    </xsl:for-each>
    </xsl:template>
    </xsl:stylesheet>

     Notes: We kept XSLT simple sot to is easy to understand and modify if necessary. 

    Steps to follow
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new transformation and open the .ats file

    Convert multiple XML files into CSV format 

    • Double click on the reader and select source XML Directory

    XML Data Reader

    • Repeat the process for the data writer
    • Press the green arrow to run the transformation
     How it works
    1. Data Reader scans the source directory and loads files into memory one by one
    2. The transformer converts file in memory into CSV using XSLT
    3. Data Writer writes results into the target directory
    • To amend the  transformation double click on it

    The transformation uses XML XSLT Transformation Function to convert XML to CSV and it also changes file name extension 

    ETL Data Transformer

    • To change XML XSLT Transformation Function properties double click on it.

    XML Example

    •  Amend XSLT Transformation if necessary

    XSLT Editor

    •  Press transform to see the result

    XSLT Transformation Result

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0009 Running ETL workflows from third party software

    This Example Demonstrates how to run ETL processes from the third party software

    Quite often our user's want to trigger package execution from their own software. This can be easily done by adding a record to the QUEUE table, We have created a number of helpers stored procedures for Oracle, SQL Server PostgreSQL and MySQL. (Download source code)

    Here is an SQL Server example:

    CREATE PROCEDURE [dbo].[EXECUTE_OBJECT]
    (
    @ComputerName NVARCHAR(255) ='',
    @LogDirectory NVARCHAR(255) ='',
    @Platform NVARCHAR(255) ='',
    @User NVARCHAR(255) ='',
    @ObjectId Int,
    @UseAgent Int = 1 -- 1 = true, 0 = false
    )
    AS BEGIN BEGIN TRAN
    -- Calculating ID
    DECLARE @ID Int
    select @ID =max(ID)+1 from ID_GENERATOR where id_type=2
    update ID_GENERATOR set ID=@ID where id_type=2
    INSERT INTO QUEUE
    (
    QUEUE_ID,
    OBJECT_ID,
    OBJECT_TYPE,
    OBJECT_NAME,
    LOG_FILE,
    SUBMITED,
    STATUS,
    USE_AGENT,
    COMPUTER_NAME,
    APPLICATION_EXECUTED_PLATFORM,
    OSUSER
    )
    SELECT @ID as QUEUE_ID,
    OBJECT_ID,
    OBJECT_TYPE,
    NAME as OBJECT_NAME,
    @LogDirectory+'\Package_'+Cast(@ID as VARCHAR)+'.log' as LOG_FILE,
    getdate() as SUBMITED, 'P' as STATUS,
    @UseAgent as use_agent,
    @ComputerName as computer_name,
    @Platform as application_executed_platform,
    @User as osuser
    from objects_tree
    where object_id=@ObjectID
    COMMIT
    END
    GO

    EXEC EXECUTE_OBJECT 'DBSLCOMPUTER','C:\Logs','Windows (32-bit)','John',126,1

    Log Directory Location

     To access options dialogue, click System Menu→ File→ Options:
    ETL log directory

    Object ID

     Getting ETL Object ID

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0010 How to Convert XML into Delimited File

    This Example Demonstrates how to convert an XML file into a Delimited file

    In the previous example, we demonstrated how to transform multiple XML files into CSV. Using a transformer gives the users maximum flexibility, however not all users require such functionality. For them, we offer XSLT Transformation Package Action.

    Steps to follow

    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new package and open the .wfp file

    Convert XML file into Delimited format 

    • Modify "XSLT Transformation" object properties and run the package by pressing the green arrow.

    Convert XML file into Delimited format

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0011 How to Convert Hyper into QVD File

    This Example Demonstrates how to convert a Tableau Hyper file into Qlik QVD File
    What is Hyper?

    Hyper is Tableau's in-memory Data Engine technology optimized for fast data ingest and analytical query processing on large or complex data sets.

    What is QVD?

    A QVD (QlikView Data) file is a file containing a table of data exported from QlikView. QVD is a native QlikView format.

    Steps to follow

    • Download and install Advanced ETL Processor 64-bit [Link]
    • Download and Unzip example[Link]
    • Create a new transformation and open the .ats file

    Converting Hyper into QVD 

    • Double click on the Reader object and amend the source file path

    Hyper Data Reader

    • Double click on the Writer object and amend the target file path
    • Run the transformation by pressing the green arrow.

     

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0012 How to Cleanse the Data

    Data Cleansing is a process of correcting data errors and removing invalid information
    Bad data example

    data quality issues

    Inconsistent values: US and USA from a human point of view are the same but for computers they are different. This can happen when merging data from different data sources

    Missing values: UK and Germany values are missing. Most likely this data is incorrect and must be removed from the final dataset.

    Data entry errors: Spain and SPain are two different values

    Uniqueness: ORDER_ID must be unique

    Inconsistent Date Formats: It is a common problem when merging data from various countries

    Non-numeric characters inside numeric fields:Same as above, can be easily corrected using delete characters transformation function

    Leading and trailing spaces:Invisible enemy of a data analyst. Use trim transformation function to correct this error

    Data Cleansing Example

    Steps to follow

    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example[Link]
    • Create a new transformation and open the .ats file

    Data Cleansing 

    • Double click on the Reader object and amend the source file path
    • Double click on the Writer object and amend the target file path
    • Run the transformation by pressing the green arrow.
     How the Data Validation process works

    Data reader loads Excel file into memory, validator rejects rows with empty Country name field. 

    Removing Empty Values

    Removing Empty Values

    Cleansing the data.

    Once bad records are rejected the transformer performs additional cleaning

    • Delete Characters Transformation function deletes Dollar sign, Pound sign, Comma and Space characters from Amount field.
    • Date Format Transformation function reformats Order Date field into standard ODBC format.
    • Lookup transformation Function corrects Country Field values

    transforming and cleansing data

    delete characters

    date format properties

    lookup properties 1

    lookup properties 2

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0013 Automatically Update Windows Firewall Rules

    One of our customers is using Advanced ETL Processor to stop brute force attacks on their Windows SFTP server

    Here is how it works:

    • Every time malicious users tries to connect to the server, the IP address is written into the windows event log.
    • Advanced ETL processor scans event log and saves a list of IP addresses into the text file.
    • Advanced ETL processor executes PowerShell script and blocks the IP address in Windows Firewall.

    Windows event log message

    To view the example follow the steps below

    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example[Link]
    • Create a new directory and call it Scripts

    Directory 

    • Create a new transformation and open .ats file

    extract ip address

    • Double click on the Reader object and select computer name

    data reader

    • Double click on the Writer object and set the connection name to "Script"
    • Save transformation
    • Create a new package and open .wfp file

    update windows firewal workflow

     Double click on transformation and select 0013 Extract IP Addresses

    transformation properties

    •  Run the package by pressing the green arrow.

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0014 How to strip non-numeric characters automatically

    This example demonstrates how to remove non-numeric characters from the numeric field
    Source data example

    Data Cleansing

    To view the example follow the steps below

    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example[Link]
    • Create a new transformation and open the .ats file

    Data Cleansing 

    • Double click on the Reader object and amend the source file path
    • Double click on the Writer object and amend the target file path
    • Run the transformation by pressing the green arrow.
    Performance considerations

    Characters can be removed using the "Delete Characters" transformation function or the "Keep characters" transformation function. The shorter the "Characters to Delete" string is the better is the performance. The same applies to the "Characters to Delete" string. This is only relevant to working with very large datasets. For small datasets, the difference is minor.

    Data Cleansing
    Delete Characters transformation function

    Delete Characters ETL Function

    Keep Characters transformation function

    Keep Characters ETL Function

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 01 Editing QVD Files using Q-Eye

     

    If you are stuck post your question on our support forum and we will do our best to assist you

  • 01 How to import text file data into Access

    In this tutorial, you will learn how to import text file data into Access. The transformation takes data from a comma-delimited file and then inserts it into an Access table.

     

    All our ETL tools have no limitations, which means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

    Visual importer ETL is the first software product we created. Advanced ETL processor is fully compatible with it and it has much more features. 
    Most of Advanced ETL Processor videous are relevant to Visual Importer ETL except the ones desribing how to work with transformations.
  • 01 Introduction

    Introduction to Advanced ETL Processor

    Watch this video to get started with Advanced ETL Processor Enterprise and Professional.

     

    Please also watch the repository videos. Understanding working with the repository is extremely important.  

     Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you.

  • 01 Introduction

    A powerful Data transformation language similar to Pascal is embedded in all our ETL and Business automation tools.

    It can be used with Calculation Transformation Function, Package Script Action or inside any text object

    mt_ignore::Transforming data using calculation function


    {multithumb enable_thumbs=1 num_cols=3}Click on thumbnail to see the screenshot

    mt_gallery:Embedded Data Transformation Language

    Performance considerations

    New users tend to rush using the scripting language.  Integrated transformation and validation functions offer much better performance and a lot of flexibility. The scripting language must be used as a last resort. If some function is missing please post your suggestion on our support forum.We would be happy to add it for you.

    Next: The Basic Structure

  • 01 Lookup Editor

    How to edit data in Lookup Tables

    Active Table Editorcan edit data in any lookup table or database.

    In this tutorial we will be creating customer lookup, designing menu items and working with the data grid.

    All our ETL tools have no limitations, which means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

Page 1 of 11

 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies