6

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: enter image description here 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?

|improve this question|||||
  • My colleague did this in the past when we had to import sheets like this into a database, but that was through programming: search for a specific (header) cell where you know the data starts. Then walk though the expected area of data and extract it. It all depends on what you can assume about the data (the code regularly broke because people changed too much to the Excel sheets). – user4293 Aug 14 '15 at 11:36
  • If you don't directly write code to convert to CSV or insert to DB from Excel, it really depends on which database. (oracle, for example, has this built in with SQLDeveloper - link). For MySQL - see here. – philshem♦ Aug 16 '15 at 10:21
  • 1
    I think this is a programming/DB question, so more appropriate for Stack Overflow. – philshem♦ Aug 16 '15 at 10:22
  • Coding is about the only option you have. – pndfam05 Aug 17 '15 at 20:09
  • This question would get closed immediately on Stack Overflow. On the other hand, it would be on-topic on Software Recommendations so feel free to copy/paste your question there: softwarerecs.stackexchange.com Cheers! – Nicolas Raoul Aug 19 '15 at 9:53
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.

|improve this answer|||||

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.