title: How to auto detect tables in Excel using Python
body: <p>I have hundreds of Excel files with hundreds of tables. </p>
  
  <p>I want to use python to automatically detect these tables and convert them to pandas dataframes. </p>
  
  <p>Some key facts about these tables:</p>
  
  <ul>
  <li>These tables are not named.  </li>
  <li>The positioning of these tables are also not known</li>
  <li>One sheet could have multiple tables (usually separated by a few blank lines )</li>
  <li>there could be annotations </li>
  <li>a table will always have a cluster of cells with values and rows and column labels </li>
  <li>the column labels might be on 1 row or 2 rows (think of nested headers in pandas df)</li>
  </ul>
  
  <p>Here is an example of what the table look like. </p>
  
  <p><a href="https://i.stack.imgur.com/ng6g1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ng6g1.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/LwWy8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LwWy8.png" alt="enter image description here"></a></p>
  
  <p>Here is <strong>my proposed solution</strong> (I would like your input on this):</p>
  
  <ol>
  <li>start from the bottom right hand corner of a given sheet in excel (so working from bottom up)</li>
  <li>search for a cluster of cells with values in it. Say you find 4 cells (2 above and 2 below) with numbers in it. This would indicate that you have landed on a table</li>
  <li>you would then figure out the dimensions of this table, so the top left and bottom right, this would be the values of the df</li>
  <li>you would then figure out how many levels there are in the columns/header and extract the labels</li>
  <li>then you would figure out how many levels there are in the rows/index and extract the labels</li>
  <li>then move to the next table and repeat</li>
  </ol>
  
  <p>What library to use?</p>
  
  <ul>
  <li>xlrd: very fast but does not read cell formatting for .xlsx files. It does provide cell format information for .xls files but not .xlsx. We are focused on .xlsx.</li>
  <li>openpyxl: very slow, uses tons of memory because it loads everything into memory. It does provide cell formatting information.</li>
  </ul>
  
  <p>During my research I across these two questions which are similar but the solutions proposed are VBA based, whereas I need a purely python solution.</p>
  
  <ol>
  <li><a href="https://stackoverflow.com/questions/47575762/how-to-automatically-detect-tables-in-excel">How to automatically detect tables in Excel</a></li>
  <li><a href="https://stackoverflow.com/questions/47595396/using-machine-learning-to-parse-excel-file-and-extract-table-data-with-no-named?noredirect=1&amp;lq=1">Using machine learning to parse excel file and extract table data with no named tables involved? If so, how do I get started?</a></li>
  </ol>