Cleaning up data sources

I do this all the time and, like Tom, does it on an SQl server using DTS or SSIS, depending on the version of the target database.

Some things I highly recommend:

Archive all files received prior to processing, especially if you receive this data from external sources, you may have to investigate the old import and revert to the original data. After a successful archive copy the file to the processing location.

For large files, it is especially useful to get some kind of flag file that is copied only after the completion of another file or even a better one, which contains the number of entries in the file. This can help prevent problems with damaged or incomplete files.

Keep a log of the number of entries and start crashing jobs if file size or number of entries are suspicious. Put the way to handle it anyway, if you find that the change is correct. Sometimes they really wanted to cut the file in half, but most of the time they didn't.

If possible, get the column headings in the column. You would be surprised how often data sources change columns, column names, or column order without first warning and interrupting the import. It's easier to check before processing the data if you have column headings.

Never import directly into a production table. It is always best to use a staging table where you can validate and clear data before entering it into the product.

Write down each step of your process so you can easily find the cause of the failure.

If you are clearing a lot of files, consider creating functions for specific types of cleaning (like formatting a phone number), then you can use the same function in multiple imports.

Excel files are evil. Find the places where leading zeros were removed during the import process.

I write my processes so I can run them as a rollback test at the end. It's much better to do this than realize that your developer data is so hopelessly messed up that you can't even do a proper test to make sure everything can be moved to prod.

Never do a new import on prod without doing it first on dev. Eyeball records directly when you start a new import (not all of them, if it's a large file, of course, but a good selection). If you think you need to get 20 columns and it imports the first time as 21 columns, look at the entries in that last column, many times, which means that the tab delimited file had a tab somewhere in the data and the column data is off for this record.

Don't assume the data is correct, check it first. I had first names in the last name column, phone numbers in the zip column, etc.

Check for invalid characters, string data where only numbers should be, etc.

Whenever possible, get an ID from the people providing the data. Place this in a table that references your ID. This will save you from duplicating records multiple times because the last name or address has changed.

There's a lot more out there, but that should get you started thinking about creating processes to protect your company data without importing bad stuff.

In situations like this, I've personally found Emacs and Python to be powerful, but I think any text editor with good search capabilities and a language with powerful string manipulation functions should do the job. I first convert the data to flat text files and then

• Eyeball is either the entire dataset or a representative true random sample of the data.
• Based on what assumptions are made about the different columns ("does not allow null", "contains only" Y "and" N "values," start date "always precedes" end date ", etc.).
• Write scripts to test hypotheses.

Obviously, this technique tends to focus on one table at a time and therefore only complements the checks made after the data is loaded into the relational database.