Best Tool for Analyzing Multi-Tab Excel Files with 100-500k Records

Question

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!

Answer 1

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:

A1=file("d:\\old.xls").xlsimport@t() 
B2=file("d:\\new.xls").xlsimport@t()
A2=A1.sort(userName,date)
B2=B1.sort(userName,date) 
new=[B2,A2].merge@d(userName,date)   
delete=[A2,B2].merge@d(userName,date)    
diff=[B2,A2].merge@d(userName,date,saleValue,saleCount) 
update=[A5,new].merge@d(userName,date)   
return update