• Automating sending HTML emails

    There two ways of sending HTML emails with Advanced ETL Processor by using the package or as part of the transformation

    Automation Package sample: Sending price list to the customers

    Email Automation

    Enabling support for HTML email is easy:just tick a checkbox

    Send Email Action

    A more advanced way of sending emails is using transformation: 

    1. Anything can be used as a data source to send emails files tables or databases
    2. It is possible to send multiple emails in one go
    3. Complex transformations can be performed on the data before sending it


    Email Transformation


    Please use our support forum if you have any questions

    Direct link, no registration required.
  • Email to Database transformation

     A simple example shows how  Advanced ETL Processor can automatically extract data from POP3 servers

    Loading Emails into Database

    In fact, the picture above demonstrates how we process Email orders every day.

    It is a very simple procedure

    The reader connects to the POP3 server using SSL and extracts the following information

    • Email No
    • UID
    • Date Sent
    • From
    • Priority
    • Subject
    • Message

    Since we know the sender it is easy to identify order type using the Validator object

    Then, individual orders are transformed using the Transformation object and the following information extracted:

    • Order NO
    • Order Data
    • Product code
    • Customer details
    • Order amount
    Direct link, no registration required.
  • Inserting context of the file inside email message text

    Here is a very basic example of email automation

    This is an important feedback message stored on the file which must be passed to the supplier

     Insert file1

    Automation Package

    Insert file2

    Script Package Action

    Insert file4

    Send Email Action

    Insert file5

    Variable Value after script execution is completed

    Insert file3

    About Variables

    Variables are used to pass parameters between various objects.
    For example, SQL script has failed and you would like to email the SQL to the developer.
    Insert <Sql Script> Into the email message and it will be replaced with actual SQL.


    There are several ways to create variables
    1. Using script action
    2. Using transformation action
    3. Using calculation object within transformation action or package
    4. Using Set Variable Action
    5. Manually using Global Variables

    Direct link, no registration required.
  • Loading Excel files from emails - questions from the customer

    I've been looking at your Advanced ETL Processor for taking data from Excel sheets into a MySQL database. We receive regular Excel sheets from about 20-25 suppliers quoting rates for services; each supplier has a different format (although some commonalities between them).

    Some questions:
    • Can loading definitions be set up to run automatically / semi-automatically? i.e. when a particular supplier provides their new Excel sheet can the previous loading definition set up for this supplier be accessed and used to load data via some schedule?

      : Yes, it is possible. You can filter incoming emails using the sender email or subject and execute the appropriate data transformation script.
      Business Automation Package
    • Can it take data from different worksheets within a single Excel file and join to load into MySQL tables?

      Yes, it is possible. Every Excel sheet, print area or filter is treated as a separate table and so can write an SQL statement to join the tables
      Excell File
      Data Reader
    • Can it be configured to ignore a defined number of header lines? Or look for a specified text to identify the next line to start importing data from?

      Yes, it is possible. You can use a validator to filter records header/footer records out

    • Can it handle logic to identify records that shouldn't be imported? e.g. if one column contains a numeric currency value and it finds "NA" can it ignore these records? i.e. not attempt to load them

      Yes, it is possible. Again you can use a validator to filter records out
      Validation EditorIs Equal Properties
    • Some Excel sheets contain a record where a cell contains a list of values (generally comma separated)... can it break this up and store multiple records? i.e. cell A may contain "John", cell B "$0.015" and cell C "A, X, 56, Z".... can this be imported to store four records:
      (John, $0.015, A) (John, $0.015, X) (John, $0.015, 56) (John, $0.015, Z)

      Yes, it can be done using an UnPivot object.
      UnPivot PropertiesUnPivot Results
    • Sometimes have a variation on the above, e.g. cell A may contain "Susan", cell B "$0.017" and cell C may be empty this time.... can this be imported to store one record:
      (Susan, $0.017, NULL)

      Yes, it is possible. You can use a combination of unpivot and validator to filter null records out
    • Another variation on the above #5 is that the multi-cell content may exist on another worksheet, e.g. on worksheet X, cell A may contain "Simon", cell B "$0.03" and then on worksheet Y, cell A would contain "Simon" and cell B "T, U, P".... can this be imported to store three records:
      (Simon, $0.03, T) (Simon, $0.03, U) (Simon, $0.03, P)

      Yes, it can be done using separate transformations
      Data Transformation
    • Excel sheets can also contain footnotes; i.e. records at the bottom of the data requiring loaded that should be ignored. Can a footer definition be set up to be ignored? e.g. text to specify the start of the footer or a certain number of blank rows in the worksheet to define a point at which import stops?

      Sure, use the validator to achieve that.
    • Can a timestamp be added by the import tool and stored with all records as an extra field into the MySQL table? This timestamp may either be sysdate or taken from a specified fixed cell in the Excel sheet - can both of these options be supported?

      Yes, it can be done by using a transformation object.
    • As a variation to the above #9, can the timestamp come from different locations depending upon the value of a field within a row being imported? e.g. if cell C was "Increase" on the row currently being imported then obtain a timestamp from cell A3 otherwise take from cell A4. Then as each row is imported, the timestamp value being stored comes from either A3 or A4 depending upon content.

      Yes, it is possible. It can be done in several ways, for example, you can validate timestamp format and if there is something wrong with it, you can use the current date and time or you can write your own calculation transformation and use any logic you wish.
      Is Date Properties
      calculation properties
    • Can it read in content within a single cell such as "18:00-07:59" and break into two fields as "18:00" and "07:59" at times?

      Yes, you can achieve this by using a splitter object.
      Splitter Properties
      Transformation Editor
    • Is any modification/preparation of the Excel file required before your product can process it?

      Answer: In some cases, it might be necessary. It is important to keep the format the same, so no future modifications would be required.

    Additional information:

    It is very interesting what you are trying to achieve and we would be happy to assist you in difficult cases. If some functionality is missing we will add it for you.

    Direct link, no registration required.
  • Loading Financial Data

    We are a small Accounting Company based in Melbourne. Over the last year, our customer base had grown twice and it is still growing. We spend a lot of time thinking about how we can optimise our business processes. The first thing we did create standard Excel templates for everything. For example, the standard expenses form will consist of customer id, employee id, expense date, category and amount. Using standard forms saved us a lot of time but once all the forms are filled in they had to be processed manually. Our people were constantly under the pressure and we had to employ agency staff on a temporary basis.

    Loading Data into General Ledger

    Not any more.

    During routine "Google search" we discovered Advanced ETL Processor Ent which quickly becomes a cornerstone for automating our business processes.

    By using Advanced ETL Processor Ent we were able to eliminate most of our manual tasks

    It is like a spider that sits in the middle connected to all our systems.

    For example, Here is our expenses form process.
    • A customer emails us expenses form
    • Advanced ETL Processor Ent package connects to mail server, downloads attachment,
    • Loads Excel file into our accounting system,
    • Runs SQL script to validate submitted data,
    • Emails report back to the person who submitted expenses from
    • At the end of the month summary report emailed to the company automatically.

    Carl Barret,

    Direct link, no registration required.
  • Sending emails with embedded pictures from etl workflow

    Sending emails in HTML format does not sound like a massive achievement, however, for most ETL tools it requires a lot of preparation.  

    Today we have a pleasant surprise for our customers:

    In the latest version of our ETL software, we added a fully functional HTML editor. This makes sending HTML emails much easier. It is also possible to embed pictures

    Sending emails with embedded pictures from etl workflow

    Direct link, no registration required.

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