I have started a new job and I need to analyze client's databases. Many times when we first meet with them we just get a huge Excel file with a dozen or so tabs and a partial to full dump of various tables. The smallest I've had contained about 100k total records, the largest had almost a million.
Excel itself is a bit clunky for analyzing the data, and it's slow with files so large. I am not sure if exporting to CSV files and loading into another tool to handle is better, or if loading them into some kinda of database tool would make more sense.
I check for things like:
- Incomplete Records
- Duplicate Records (Exact)
- Duplicate Records (Fuzzy Matching: ie Avenue vs Ave vs Ave., Robert vs Bob vs Bobby)
- Inconsistent Data (Inc vs Inc. , # vs LB vs lb, Road vs Rd vs Rd. vs rd, etc)
- Other Ideas/Suggestions are Appreciated
Eventually I'll likely either be cleaning all their data, or reformatting it to migrate to a new system. Given that, and the fact that I'd like to see if I can script/automate some of the analysis I think a database approach would make more sense. I don't know what options would make sense.
Option to visualize the data to show the client would be a bonus. Free is always good, but I don't mind paying as it will be used daily and likely well worth the money.