Data cleansing sample

Overview

Data warehouses and data marts require data from a variety of systems to be cleansed prior to importing.

Please see our online demonstrations

• Convert Unix files to DOS format
• Viewlet - Solving surname capitalization in customer databases
• Other viewlets

Problems viewing the demos? If you are running a firewall such as ZoneAlarm or privacy software that prevents popup windows, it may be inserting JavaScript code into the top of every page you view. The demonstration display software cannot cope with this alteration, so please disable this feature of your software in order to view them.

Example

Imagine the following situation: You are converting customer data from a legacy, non-Y2K compliant application, and the address information is malformed. You have GPO Boxes (General Post Office) that have been entered inconsistently.

For example, you have

GPO Box 337, Melbourne 3001
G.P.O. Box 228, Sydney 2000
Gpo box 816, central park 6754
G.PO box 3, Marble Bar 4000
…etc

Your import conversion needs these to be cleaned up.

Without TextPipe, you could possibly achieve this with an exhaustive set of search and replaces. But you really need an editor that provides scripting, can work with very large data files, and handles pattern matching. TextPipe provides all of these capabilities. It's simple to search for GPO in a case-insensitive manner, with optional periods between letters, and only where there is a word break on either side (ie 'GPO' is not part of a word).

You can manually check the first few replaces or even every change to verify correct operation.

In the same sweep through the data, TextPipe can perform multiple search/replaces with or without pattern matching, conversion between EBCDIC and ASCII, adding or removing text and much more.