title: Using machine learning to parse excel file and extract table data with no named tables involved? If so, how do I get started?
body: <p>I have read several articles on machine learning.</p>
  
  <p>An example is this <a href="https://medium.com/technology-invention-and-more/how-to-build-a-simple-neural-network-in-9-lines-of-python-code-cc8f23647ca1" rel="nofollow noreferrer">https://medium.com/technology-invention-and-more/how-to-build-a-simple-neural-network-in-9-lines-of-python-code-cc8f23647ca1</a> and was wondering if it's possible to modify the code to do the use case below.</p>
  
  <p>Let's say the machine is given an excel file (assuming .xlsx) that has only 1 sheet.</p>
  
  <p>Inside the sheet there will be a table (defined as having a header row and at least 2 data rows and will be agreed as a table by common sense humans who read the file. Not a named table or object as defined by MS Excel)</p>
  
  <p>The following is unknown before hand:</p>
  
  <ul>
  <li>the position of the table</li>
  <li>the number of rows and columns</li>
  <li>the data type in the table</li>
  </ul>
  
  <p>This table in the excel file is also <strong>NOT</strong> <a href="http://www.contextures.com/xlExcelTable01.html#rename" rel="nofollow noreferrer">named or identified beforehand</a> in any way. <strong>There are no objects in this excel file. No named tables.</strong> </p>
  
  <p>If I were to attempt to use VBA to list objects, I will get this error message</p>
  
  <p><a href="https://i.stack.imgur.com/YQr2D.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YQr2D.png" alt="enter image description here"></a></p>
  
  <p>There will be at least 1 other cell outside of this table that will contain some text or value.</p>
  
  <p>How or what algorithm using what software library can I train a machine to identify where does the table start and end?</p>
  
  <p>E.g.</p>
  
  <p>The table first cell (identified as the top left most cell in the header row) is A4 and the table last cell (identified as the bottom right most cell) is G12</p>
  
  <p>The machine is able to read the excel file and say that the table is at A4:G12 even if there are other cells outside the table that has data.</p>
  
  <p>My various google search involving "excel and machine learning" or "identify tables in excel" tends to give me articles on how to feed data to machine learning software using excel files or how to find/name data tables that are named in the Excel. <strong>I want to emphasize the table data is NOT named.</strong></p>
  
  <p>Also come across <a href="https://stackoverflow.com/questions/28532770/extract-identify-tables-from-pdf-python">answers</a> talking about extracting such table data from PDFs. But my focus is on Excel.</p>
  
  <p>I have read that machine learning can be used to read images. So I imagine trying to "read" an excel file which is a highly structured file just to find where a table is should be possible.</p>
  
  <p>Here are some sample screenshots</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 are the links to the Excel files</p>
  
  <p><a href="https://www.dropbox.com/s/l3vjjsgunp0zu23/A4toG12.xlsx?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/l3vjjsgunp0zu23/A4toG12.xlsx?dl=0</a></p>
  
  <p><a href="https://www.dropbox.com/s/nwzw0211ruhwvf0/G7toN19.xlsx?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/nwzw0211ruhwvf0/G7toN19.xlsx?dl=0</a></p>
  
  <p>Please note that these are dummy files to illustrate my point about identifying the table data.</p>
  
  <ul>
  <li>The first one should be identified as A4 to G12</li>
  <li>The second one should be identified as G7 to N19</li>
  </ul>
  
  <p>The comment by <a href="https://stackoverflow.com/users/5457259/janlauge">JanLauGe</a> about <code>ctrl</code> + <code>a</code> is interesting See <a href="https://stackoverflow.com/questions/47595396/using-machine-learning-to-parse-excel-file-and-extract-table-data-with-no-named?noredirect=1#comment82149511_47595541">Using machine learning to parse excel file and extract table data with no named tables involved? If so, how do I get started?</a></p>
  
  <p>I have googled for the equivalent in VBA but to no avail.</p>
  
  <h2>What I have tried</h2>
  
  <p>I wrote a script as suggested by Scott Craner to find all the cells with values I get this.</p>
  
  <p>Thanks to his comments, I got it to work.</p>
  
  <pre><code>Public Sub LookForCells()
      For Each block In ActiveSheet.Columns("A:Z").SpecialCells(xlCellTypeConstants, 23).Areas
         MsgBox block.Count
      Next block
  End Sub
  </code></pre>
  
  <p>And you should see the following</p>
  
  <p><a href="https://i.stack.imgur.com/434Vk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/434Vk.png" alt="enter image description here"></a></p>