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.


  • 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 says reinstate Monica Aug 16 '19 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 '19 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 says reinstate Monica Aug 18 '19 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 '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 says reinstate Monica Aug 20 '19 at 7:37

You should write a program processing those Excel files. Though many programming languages provide API to read data from Excel files, you still need to write code to open each file, traverse cells to read desired data, input all data into memory and store it in one data set object, usually use Python to convert a sheet into a dataframe with pandas. But using Python needs a lot of preparation and coding work too. It would have been much simpler if you had used esProc to do this.

For example,two xsl file have columns userName,date,saleValue,saleCount. To get the newly-added, modified and deleted rows, you can use this esProc script like this:

return update

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.