Cleaning your files With TextPipe
By Ron Nixon, Star Tribune (Minneapolis)
Here are three challenges familiar to journalists who have worked with dirty data:
1. Your data exists in a text file, but when you try to import the file into your program, there's a problem: the data lines up incorrectly. There's a tab or comma delimiter that's out of place or a space that you can't see. 2. You need to search and replace hundreds of times in your data, sometimes using exact searches and others with pattern searches. 3. You need to extract information out of a text file that's unstructured or pull data out of a HTML or XML file.
For any of these challenges you could use a programming language such as Perl, Python, PHP, or even Visual Basic to craft a solution. There are several advantages of using any of these. Among them is the power and, with the exception of Visual Basic, the availability of free downloads on the Web. But there's a downside: each comes with a steep learning curve.
Even Python, which can be easy to use, has a learning curve. An alternative tool is a utility called TextPipe Pro. TextPipe costs $299 and you can download a free 30-day trial from www.crystalsoftware.com.au/index.html
Made by DataMystic, an Australian company formerly known as Crystal Software, the program has an easy-to-use interface. Users can have it up and running in a few minutes and it is especially well-suited for the non-programmer.
Don't let the ease of use fool you - it's still a powerful text manipulation tool. TextPipe comes with 100 filters that can be applied to everything from a simple search and replace to extracting telephone numbers from a text file. What really makes it shine, though, is its ability to do hundreds, even thousands of search-and-replace routines at the same time. And that's just the beginning.
Along with the searching and replacing, you can add filters to remove white space, add a new line or add a line feed, all in one session. You can even do targeted searching and replacing by column or even by row. You can even use a Microsoft Excel spreadsheet or a text file as a lookup table for TextPipe's searching and replacing. Simply type the strings to find in one column and the replacement in another and import the data.
What's more, TextPipe is fast and can handle files of several hundred megabytes. I've used it on files 2 gigabytes or more. TextPipe can be set up to run as a scheduled program, so you can have it running and working while you do other things. You can also run it from the command line.
Other TextPipe text file capabilities include merging dozens of files, sorting information and converting text files from mainframe, PC and Unicode data formats.
TextPipe keeps an audit log of all your changes so you can document your work. The software has a feature called the Scratch Pad where you can try your filter on practice text before using it on your real text file.
Those who need to do even more complex text processing can use Perl-style regular expressions. For those who are intimidated by regular expressions, TextPipe offers EasyPatterns, a text processing tool that has all the power of regular expressions but is easy to understand.
For example, to describe a phone number in regular expressions you'd write: \d\d\d-\d\d\d-\d\d\d\d In Easy Patterns it's: [PhoneNumber].
DataMystic provides an introduction to Easy Patterns at www.crystalsoftware.com.au/easypatterns.html.
Even though there are programming languages available for free download, TextPipe's ease of use and flexibility make it well worth the price.
Contact Ron Nixon by e-mail at rnixon@startribune.com.