Identify and concatenate data embedded in Excel Spreadsheets

Question

We're looking at open data published in multiple excel spreadsheets with no standard format. The data can be embedded in multiple tables within a given sheet, e.g.

|      |          |               |          |       |      |          |               |          |       |  | 
|------|----------|---------------|----------|-------|------|----------|---------------|----------|-------|--| 
|      | Day:     |  Friday       |          |       |      | Day:     | Saturday      |          |       |  | 
|      | Date:    | 08/24/2012    |          |       |      | Date:    | 08/25/2012    |          |       |  | 
|      | Weather: | 30°C, No Rain |          |       |      | Weather: | 30°C, No Rain |          |       |  | 
|      |          |               |          |       |      |          |               |          |       |  | 
|      | Hour     | In            | Out      | Total |      | Hour     | In Park       | Out      | Total |  | 
|      | 12:00 AM | 0             | 0        | 0     |      | 12:00 AM | 0             | 0        | 0     |  | 
|      | 1:00 AM  | 0             | 0        | 0     |      | 1:00 AM  | 0             | 0        | 0     |  | 
|      | 2:00 AM  | 0             | 0        | 0     |      | 2:00 AM  | 0             | 0        | 0     |  | 
|      | 3:00 AM  | 0             | 0        | 0     |      | 3:00 AM  | 0             | 0        | 0     |  | 

But these tables are not necessarily located in the same location, nor is the data generally structured exactly the same, though it will generally follow the following pattern:

| Timestamp | Count | Count |
|-----------|-------|-------|

I've identified Python libraries that can interact with Excel spreadsheets, but are there parsing libraries built on top of these to identify patterns within spreadsheets? We want to be able to parse multiple spreadsheets and get the data into one file for manipulation/cleansing before loading it into a database. Non-Python solutions welcome.

Answer 1

Yeah, I've imported data from Excel-based reports. They are truly a pain to parse. Every once in a while I'd have to go back in to the parsing code to make adjustments to account for changes upstream. That was using Microsoft Access.

I actually have a library that may help you: https://bitbucket.org/erosa/frostedsheets

It's a Groovy library, so it's for the Java ecosystem, but it utilizes Apache POI under the hood, which provides an interesting side-effect: empty cells are ignored.

For example, with Frosted Sheets your sample spreadsheet would parse into a structure like this:

[
    [Date:, 24-Aug-2012, Date:, 25-Aug-2012],
    [Weather:, 30c, No Rain, Weather:, 30c, No Rain],
    [Hour, In, Out, Total, Hour, In Park, Out, Total],
    [12:00 AM, 0.0, 0.0, 0.0, 12:00 AM, 0.0, 0.0, 0.0],
    [1:00 AM, 0.0, 0.0, 0.0, 1:00 AM, 0.0, 0.0, 0.0],
    [2:00 AM, 0.0, 0.0, 0.0, 2:00 AM, 0.0, 0.0, 0.0],
    [3:00 AM, 0.0, 0.0, 0.0, 3:00 AM, 0.0, 0.0, 0.0],
]

While that structure is still challenging to work with, it's more predictable:

• The dates are on the first row on even-numbered columns.
• Based on the length of the 4th row you can tell how many pieces of embedded content there are
• Etc...

So, give that a shot :)