POP3

  •  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 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 Validator object

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

    • Order NO
    • Order Data
    • Product code
    • Customer details
    • Order amount
    Download Free Trial Learn More About Our ETL Software
  • 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?

      Answer
      : Yes, it is possible. You can filter incoming emails using sender email or subject and execute 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?

      Answer:
      Yes, it is possible. Every Excel sheet, print area or a 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?

      Answer:
      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

      Answer:
      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)

      Answer:
      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)

      Answer:
      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 record:
      (Simon, $0.03, T) (Simon, $0.03, U) (Simon, $0.03, P)

      Answer:
      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?

      Answer:
      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 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?

      Answer:
      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 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.

      Answer:
      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 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?

      Answer:
      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 for you.

    Download Free Trial Learn More About Our ETL Software
  • 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, 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 ware 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 which sits in the middle connected to all our systems.

    For example, FOR expenses form the process as follows.

    • 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,
    Accountant

    Download Advanced ETL Processor Enterprise now!

     

  • 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

    Download Free Trial Learn More About Our ETL Software

We have 935 guests and no members online