This article describes what is Dirty Data and how to Deal with it
What is Dirty Data anyway?
Other common causes of dirty data are:
Most of the problems come when working with Text or Excel Files
In order to load data, we need to make sure that the format of Amount and Order_Date fields is consistent.
For the amount field, we need to get rid of dollars, pounds and commas.
It could easily be done by using the replace function of Advanced ETL Processor.
For ORDER_DATE field we will apply multiple date formats.
The result of the Date Format function is a string in 'YYYY-MM-DD HH:NN:SS.ZZZ' format
Full Data Transformation:
The result of Data Transformation:
About Advanced ETL Processor
Advanced ETL Processor is an ETL tool designed to automate extracting data from ANY database, transform, validate it and load into ANY database. Typical usage of it would be extract data from Excel File, Validate Date Formats, Sort data, deduplicate it and load it into the Oracle database, run stored procedure or SQL script, once loading is completed. Unlike Oracle SQL loader, BCP, DTS or SSIS Advanced ETL Processor can also add new and update old records based on the primary key.