Read Excel XML .xls file with pandas

Question

I'm aware of a number of previously asked questions, but none of the solutions given work on the reproducible example that I provide below. I am trying to read in `.xls` files from http://www.eia.gov/coal/data.cfm#production -- specifically the Historical detailed coal production data (1983-2013) `coalpublic2012.xls` file that's freely available via the dropdown. Pandas cannot read it. In contrast, the file for the most recent year available, 2013, `coalpublic2013.xls` file, works without a problem: ``` import pandas as pd df1 = pd.read_excel("coalpublic2013.xls") ``` but the next decade of `.xls` files (2004-2012) do not load. I have looked at these files with Excel, and they open, and are not corrupted. The error that I get from pandas is: ``` --------------------------------------------------------------------------- XLRDError Traceback (most recent call last) in () ----> 1 df = pd.read_excel("coalpublic2012.xlsx") /Users/jonathan/anaconda/li…

Accepted Answer

You can convert this Excel XML file programmatically. Requirement: only python and pandas.

```
import pandas as pd
from xml.sax import ContentHandler, parse

# Reference https://goo.gl/KaOBG3
class ExcelHandler(ContentHandler):
    def __init__(self):
        self.chars = [  ]
        self.cells = [  ]
        self.rows = [  ]
        self.tables = [  ]
    def characters(self, content):
        self.chars.append(content)
    def startElement(self, name, atts):
        if name=="Cell":
            self.chars = [  ]
        elif name=="Row":
            self.cells=[  ]
        elif name=="Table":
            self.rows = [  ]
    def endElement(self, name):
        if name=="Cell":
            self.cells.append(''.join(self.chars))
        elif name=="Row":
            self.rows.append(self.cells)
        elif name=="Table":
            self.tables.append(self.rows)

excelHandler = ExcelHandler()
parse('coalpublic2012.xls', excelHandler)
df1 = pd.DataFrame(excelHandler.tables[0][4:], columns=excelHandler.tables[0][3])
```

Read Excel XML .xls file with pandas

Solution

Problem

Related problems