1

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)
  • 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.

Thanks!

  • i wonder how the clients use such huge Excel files, and what they will do when they eventually become too slow to open & manoeuvre. At some point, a move to an RDBMS might be a god idea – Mawg Aug 16 at 11:45
  • @Mawg The Excel files are data exports from a RDBMS. We do this at an early stage when working with the clients. Not everyone is willing to just hand us the keys to their servers to go in and look in the databases themselves. – Dizzy49 Aug 16 at 13:33
  • So, why do they give you .XLSX and not .CSV? In any case, if you convert to CSV, you can import directly into MySql, for instance. Personally, I would code some Python to lint the CSV fiels – Mawg Aug 18 at 8:36
  • 1
    Most of the time it's because they don't know how to Export and/or Save to CSV. Yeah, I know, it's ridiculous. I was looking at PySpread and Pandas. I have never coded in Python before, but I know like 8 other programming languages so I imagine it shouldn't be too hard to pick up. – Dizzy49 Aug 19 at 3:24
  • You could code an Auto It script to automate export to CSV and give them that. The script could even email it to you – Mawg Aug 20 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.