Computers and Technology
Extract, Transform, Load - ETL Data Conversion
Introduction
ETL stands for Extract, Transform and Load, the processes that enables the move of data from multiple sources, reformat and cleanse it, do whatever data conversions are necessary, and load it into another file, database, a data mart or a data warehouse for analysis, or onto another system, for instance a CMS environment like Drupal or Mambo/Joomla.
We all know that there are valuable data lying around throughout our systems that would be very useful if it could be reused in another program, or we'd like to upgrade our system to a different software package.
The problem often is that the data lies in formats that cannot be readily used by other applications.
Solutions
To solve the problem, you can use extract, transform and load (ETL) software, which includes reading data from its source, cleaning it up and formatting it uniformly, and then writing it to a target format to be exploited.
The data used in ETL processes can come from any source: a flat file, a mainframe application, an ERP application, a CRM tool, an Excel spreadsheet, an extraction program, anything really.
Extracting the data
Extraction can be done via a variety of methods. Often, the environment or program in which the data is currently held will have an export function that can be used to get the data into a format that can be easily transformed and processed. There are also specialized tools available to take data from a database environment.
After extraction, the data is transformed, or modified, depending on the specific business logic involved so that it can be sent to the target data store.
There are a variety of ways to perform the transformation, and the work involved varies. The data may require reformatting only, but most ETL operations also involve cleansing the data to remove duplicates and enforce consistency.
Transformation
In addition, the ETL process could involve transforming from a fixed-record format to a variable one, or vice versa, standardizing name and address fields, verifying telephone numbers or expanding records with additional fields containing demographic information or data from other systems.
The transformation occurs when the data from each source is mapped, cleansed and reconciled so it all can be tied together.
After reconciliation, the data is transported and loaded into the data warehouse for analysis.
Online data transformation
There are many tools available that help in the ETL process. Most of them mandate an investment in software that needs to be installed on your computer. There are also online functions available here can be very useful if you cannot, or don?t want to install any software on your computer. You will still need to extract the information from your existing environment into a file, but the transformation process can in many instances be done online.
John Rukkers is a seasoned data conversion expert. Free online data conversion routines can be found at his web site data conversion online. |
John Rukkers
Similar articles
Electronic Discovery Software
Electronic discovery is the retrieval of useful documents from different electronic sources. With the increasing use of electronic devices for creating, storing and transferring data, the retrieval of useful and valuable information has become a challenge. Read more →EMI and RFI Filters -- Why You Need Them
What is an EMI / RFI filter? An EMI RFI filter is designed to be added to your incoming power line (in series). It removes unwanted signals and noise from the outside power grid. Read more →Far From Friends
In this rapid century, spending most of our time at work we lack of contacts with our relatives and friends, especially with those living in another city, country or even continent. Read more →Finding A Bar Code Printer For You
For years, I used handmade price tags in my small business. This was not only time consuming in terms of creating the price tags, it also made it difficult to keep track of my inventory. Read more →Aphorism
The press, the machine, the railway, the telegraph are premises whose thousand-year conclusion no one has yet dared to draw.