Extracting table nested within div of HTML

CommonEngineer
July 24, 2024
179 views
0 votes
2 Answers

I am trying to extract some machine data from one of our servers. Server gives me file format HTML, end goal is to extract the table data and export to CSV to work with further.

I am having a difficult time actually extracting the table data. I am a ME and by no means a programmer but can make some basic python scripts.

I have spent 2 days now researching html tags, how to parse with BeautifulSoup etc. But it’s time to raise my hand and ask for some help.

The table data seems to be nested within a div, which is either a sibling or child of style.
If I open the html raw with notepad it appears that div is a sibling to style. Open it with notepad++ and it looks to be a child to style

However, if I pass html through BeautifulSoup and soup.prettify() the seems to be at a parent level indentation within . (Sorry I may not be describing this correctly)

There are no classes to gather, so I have been using soup.find_all(‘tag’, class_=None) to aid with finding the tags

from bs4 import BeautifulSoup
from bs4 import SoupStrainer

If I pass the html through BS and prettify, I can see the div is shown at the highest indentation.

'html ="<style> .text { mso-number-format:@; } </script> <table><tr><td colspan=10 style='font-weight:800;font-size:x-large;text-align:center;'>HISTORICAL SEARCH</td></tr><table><br/>
<div>
    <table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
        <tr>
            <th scope="col">PLANT</th><th scope="col">SHIFT_DATE</th><th scope="col">MACHINE</th><th scope="col">PN</th><th scope="col">TOOL</th><th scope="col">PR ID</th><th scope="col">OPERATOR</th><th scope="col">SHIFT</th><th scope="col">START_TIME</th><th scope="col">END_TIME</th><th scope="col">CAVITIES CURR</th><th scope="col">CAVITIES IDEAL</th><th scope="col">GOOD PARTS</th><th scope="col">SCRAP PARTS</th><th scope="col">TOTAL</th><th scope="col">GOOD_CONTNRS</th><th scope="col">SCRAP_CONTNRS</th><th scope="col">SCRAP_COST</th><th scope="col">FTQ</th><th scope="col">MACH_CYCLES</th><th scope="col">RUN  TIME</th><th scope="col">DOWN  TIME</th><th scope="col">CO  TIME</th><th scope="col">IDLE  TIME</th><th scope="col">CYCLE  IDEAL</th><th scope="col">CYCLE_AVG</th><th scope="col">CALC TEEP</th><th scope="col">CALC OEE</th><th scope="col">MSPEC</th><th scope="col">LAST MODIFIED</th>
        </tr><tr>
            <td>0000</td><td>07/21/2024</td><td>0007</td><td>PN123456</td><td>DIE NUMBER 1</td><td>307892</td><td>0</td><td>PM</td><td>7/21/2024 3:00:00 PM</td><td>7/21/2024 11:00:00 PM</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>479</td><td>0</td><td>0</td><td>1200</td><td>0</td><td>0</td><td>0</td><td>M7602L48</td><td>7/21/2024 11:02:47 PM</td>
        </tr><tr>
            <td>0000</td><td>07/21/2024</td><td>0007</td><td>PN123456</td><td>DIE NUMBER 1</td><td>307892</td><td>0</td><td>AM</td><td>7/21/2024 7:00:00 AM</td><td>7/21/2024 3:00:00 PM</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>479</td><td>0</td><td>0</td><td>1200</td><td>0</td><td>0</td><td>0</td><td>M7602L48</td><td>7/21/2024 3:02:46 PM</td>
        </tr>
    </table>
</div><table><tr><td colspan=10></td></tr><table>"
soup = BeautifulSoup(open(html), 'lxml')
print(soup.prettify())

If I try and search for the table directly I am left with a empty list.

soup.find_all('table')
#[]

If I try and search for the div I also get an empty list.

soup.find_all('div')
#[]

If I try and search for style I can see the with table.
`soup.find_all(‘style’)

I also attempted use SoupStrainer to only parse for the div

only_div = SoupStrainer('div', {'class': None})
soup = BeautifulSoup(open(html), 'lxml', parse_only=only_div)
print(soup)
#empty

With my limited knowledge, I am unsure where to proceed from here and looking for help/advice.

My best guess is that assuming div is a child of style, I need to do search within style within div for table. But I am not exactly sure if div is even a child or not.

Apologies in advance for any formatting issues, this is my first post on SO.

Answers

Please note that your given code example is not reproductive. Your provided HTML code is invalid. In the example below, I will use your HTML code which I validated. My example might not work with your data, since it is only based on the small piece of code you gave (which is invalid). For further help, please provide valid HTML code or the full data.

My example to parse your HTML to a csv file:

from bs4 import BeautifulSoup
import json
import pandas as pd #pip install pandas
from io import StringIO

#Correct HTML
html = '<table><tr><td colspan=10 style=font-weight:800;font-size:x-large;text-align:center>HISTORICAL SEARCH</tr><table><br><div><table border=1 cellspacing=0 rules=all style=border-collapse:collapse><tr><th scope=col>PLANT<th scope=col>SHIFT_DATE<th scope=col>MACHINE<th scope=col>PN<th scope=col>TOOL<th scope=col>PR ID<th scope=col>OPERATOR<th scope=col>SHIFT<th scope=col>START_TIME<th scope=col>END_TIME<th scope=col>CAVITIES CURR<th scope=col>CAVITIES IDEAL<th scope=col>GOOD PARTS<th scope=col>SCRAP PARTS<th scope=col>TOTAL<th scope=col>GOOD_CONTNRS<th scope=col>SCRAP_CONTNRS<th scope=col>SCRAP_COST<th scope=col>FTQ<th scope=col>MACH_CYCLES<th scope=col>RUN TIME<th scope=col>DOWN TIME<th scope=col>CO TIME<th scope=col>IDLE TIME<th scope=col>CYCLE IDEAL<th scope=col>CYCLE_AVG<th scope=col>CALC TEEP<th scope=col>CALC OEE<th scope=col>MSPEC<th scope=col>LAST MODIFIED<tr><td>0000<td>07/21/2024<td>0007<td>PN123456<td>DIE NUMBER 1<td>307892<td>0<td>PM<td>7/21/2024 3:00:00 PM<td>7/21/2024 11:00:00 PM<td>1<td>1<td>0<td>0<td>0<td>0<td>0<td>0<td>0<td>0<td>0<td>479<td>0<td>0<td>1200<td>0<td>0<td>0<td>M7602L48<td>7/21/2024 11:02:47 PM<tr><td>0000<td>07/21/2024<td>0007<td>PN123456<td>DIE NUMBER 1<td>307892<td>0<td>AM<td>7/21/2024 7:00:00 AM<td>7/21/2024 3:00:00 PM<td>1<td>1<td>0<td>0<td>0<td>0<td>0<td>0<td>0<td>0<td>0<td>479<td>0<td>0<td>1200<td>0<td>0<td>0<td>M7602L48<td>7/21/2024 3:02:46 PM</table></div></table></table>'

soup = BeautifulSoup(html, 'lxml')

#Getting the first table
table = soup.find('table', attrs = {})
#Getting table row (<tr>)
tr = table.find('tr', attrs = {})
#Getting table data (<td>) with the specified attributes and read its text
table_title = tr.find('td', attrs = {
    'colspan': '10',
    'style': 'font-weight:800;font-size:x-large;text-align:center'
}).text

#Getting the second table2 from the first table
table2 = table.find('table')

#Getting the third table3 from the second table2
table3 = table2.find('table')

#Defining an array for the results
obj_result = []

#Array to store the table heads of table3
obj_table_heads = []

#loop through each table row in table3
for tr in table3.findAll('tr'):
    #Getting all table heads in table3...
    for th in tr.findAll('th', attrs = {'scope': 'col'}):
        #... and add them to obj_table_heads
        obj_table_heads.append(th.text)
    

    
    #creating a local object to add the current data of td
    obj_td = {}
    count = 0
    #Getting all table data...
    for td in tr.findAll('td', attrs = {}):
        #... and creating a node to add to obj_td
        obj_td[obj_table_heads[count]] = td.text
        count += 1
    
    #add obj_td to obj_result if obj_result has at least one node
    if len(obj_td) > 0:
        obj_result.append(obj_td)

#convert to csv file using pandas import
data = pd.read_json(StringIO(json.dumps(obj_result)))
data.to_csv(table_title + '.csv', encoding='utf-8', index=False, sep='|')

u can use pandas

import pandas as pd


html = '''
<table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
        <tr>
            <th scope="col">PLANT</th><th scope="col">SHIFT_DATE</th><th scope="col">MACHINE</th><th scope="col">PN</th><th scope="col">TOOL</th><th scope="col">PR ID</th><th scope="col">OPERATOR</th><th scope="col">SHIFT</th><th scope="col">START_TIME</th><th scope="col">END_TIME</th><th scope="col">CAVITIES CURR</th><th scope="col">CAVITIES IDEAL</th><th scope="col">GOOD PARTS</th><th scope="col">SCRAP PARTS</th><th scope="col">TOTAL</th><th scope="col">GOOD_CONTNRS</th><th scope="col">SCRAP_CONTNRS</th><th scope="col">SCRAP_COST</th><th scope="col">FTQ</th><th scope="col">MACH_CYCLES</th><th scope="col">RUN  TIME</th><th scope="col">DOWN  TIME</th><th scope="col">CO  TIME</th><th scope="col">IDLE  TIME</th><th scope="col">CYCLE  IDEAL</th><th scope="col">CYCLE_AVG</th><th scope="col">CALC TEEP</th><th scope="col">CALC OEE</th><th scope="col">MSPEC</th><th scope="col">LAST MODIFIED</th>
        </tr><tr>
            <td>0000</td><td>07/21/2024</td><td>0007</td><td>PN123456</td><td>DIE NUMBER 1</td><td>307892</td><td>0</td><td>PM</td><td>7/21/2024 3:00:00 PM</td><td>7/21/2024 11:00:00 PM</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>479</td><td>0</td><td>0</td><td>1200</td><td>0</td><td>0</td><td>0</td><td>M7602L48</td><td>7/21/2024 11:02:47 PM</td>
        </tr><tr>
            <td>0000</td><td>07/21/2024</td><td>0007</td><td>PN123456</td><td>DIE NUMBER 1</td><td>307892</td><td>0</td><td>AM</td><td>7/21/2024 7:00:00 AM</td><td>7/21/2024 3:00:00 PM</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>479</td><td>0</td><td>0</td><td>1200</td><td>0</td><td>0</td><td>0</td><td>M7602L48</td><td>7/21/2024 3:02:46 PM</td>
        </tr>
    </table>
'''
df = pd.read_html(html)
df[0].to_csv('csv_name.csv')

If u need to print it to console use:

print(df[0].to_string())

OUTPUT:

PLANT  SHIFT_DATE  MACHINE        PN          TOOL   PR ID  OPERATOR SHIFT            START_TIME               END_TIME  CAVITIES CURR  CAVITIES IDEAL  GOOD PARTS  SCRAP PARTS  TOTAL  GOOD_CONTNRS  SCRAP_CONTNRS  SCRAP_COST  FTQ  MACH_CYCLES  RUN TIME  DOWN TIME  CO TIME  IDLE TIME  CYCLE IDEAL  CYCLE_AVG  CALC TEEP  CALC OEE     MSPEC          LAST MODIFIED
0      0  07/21/2024        7  PN123456  DIE NUMBER 1  307892         0    PM  7/21/2024 3:00:00 PM  7/21/2024 11:00:00 PM              1               1           0            0      0             0              0           0    0            0         0        479        0          0         1200          0          0         0  M7602L48  7/21/2024 11:02:47 PM
1      0  07/21/2024        7  PN123456  DIE NUMBER 1  307892         0    AM  7/21/2024 7:00:00 AM   7/21/2024 3:00:00 PM              1               1           0            0      0             0              0           0    0            0         0        479        0          0         1200          0          0         0  M7602L48   7/21/2024 3:02:46 PM

Please signup or login to give your own answer.

Click here to cancel reply.