Finding tables in messy structured Excel / csv file to import them in DB

Question

we have a lot of messy structured excel files containing tables somewhere on a sheet and we want to import these tables into a database to do some analytic on it. There problem is that we don't have the time to run through the files and clean them and therefore we looking for a tool or algorithm to help us.

We had some ideas to export the excel files to csv and then analyse the structure of them by kicking out empty lines (only semicolons) and finding tables based on the count of semicolons and used columns in the lines.

The following files shows an example of what we have: The black and white table area is what we want to import into a DB or extract out of the file. The orange area is what we want to delete / skip.

And mostly every files looks different. Did somebody came across this and found a solution?

Answer 1

Yes, it could be done but converting to CSV will not help you since data in your Excel files identified by coloring rows.

I suggest to use software libraries that allow to parse Excel files and to write function that detects color of the row and extracts the data required.

Or you could convert all files to CSV file and to use simple linux commands "sed/awk/grep/" to filter irrelevant rows using keywords like "Last name" and "Total" to get all rows between them.