Scrape HTML table data and create XML or JSON doc

Question

I need to scrape some website data from a table on a website and create an XML or JSON document that will be used by an app. and i have some problem getting the below data.

The table looks like this:

<table border="0" cellpadding="3" cellspacing="0" bgcolor="#DDEEFF" width="100%">
<tr>
<td width="20%" ><font face="Verdana, Arial" size="1">SRC</a></td></font>
    <td width="58%" ><font face="Verdana, Arial" size="1"><a href="http://example.com/this/news?id=1&by=today" onMouseOver="a('Open Bulletin');return true" onMouseOut="b()">Welcome</font></a></td>
<td width="17%" align="center"><font face="Verdana, Arial" size="1">Event</td></font>
    </tr>   

<tr>
<td width="20%" ><font face="Verdana, Arial" size="1">FMD</a></td></font>
    <td width="58%" ><font face="Verdana, Arial" size="1"><a href="http://example.com/this/news?id=2&by=today" onMouseOver="a('Open Bulletin');return true" onMouseOut="b()">Another News</font></a></td>
<td width="17%" align="center"><font face="Verdana, Arial" size="1">Updates</td></font>
    </tr>   
</td>

And I would like to create an XML feed or JSON that looks like this:

<bulletins>
    <title>Welcome</title>
    <id>1</id>
    <type>News</type>
</bulletins>

<bulletins>
    <title>Another News</title>
    <id>2</id>
    <type>Updates</type>
</bulletins>

Here is my current code :

<?php
$body = explode('<table border="0" cellpadding="3" cellspacing="0" bgcolor="#DDEEFF" width="100%">', $html);

$xml = simplexml_load_string("<?xml version='1.0' encoding='utf-8'?><xml />");

$rows = array();
foreach (array_slice(explode('<tr>', end($body)), 1) as $row)
{   
    preg_match('#<a.*?href="(.*?)".*?>(.*?)</a>#i', $row, $title);
    preg_match('/<a.*href="(.*)".*>(.*)<\/a>/iU', $row, $id);
   // preg_match('/type">([^<]+)<\/td>/', $row, $type); 

    $node = $xml->addChild('bulletins');

    $node->addChild('title', $title[1]);
    $node->addChild('id', $id[1]);
   // $node->addChild('type', $due[1]);
}

header('Content-Type: text/xml');
echo $xml->asXML();
?>

But the problem i got this

<xml>
    <bulletins>
        <title>http://example.com/this/news?id=1</title>
        <id>http://example.com/this/news?id=1</id>
    </bulletins>
    <bulletins>
        <title>http://example.com/this/news?id=2</title>
        <id>http://example.com/this/news?id=2</id>
    </bulletins>
</xml>

So, what's the problem you're having? *It doesn't work?* Is that it? — , Jun 05 '12 at 15:45
[It's much easier to parse HTML with DOM](http://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags/1732454#1732454) ... regex is a waste of time in this matter. — , Jun 05 '12 at 15:53

score 1 · Answer 1 · answered Jun 05 '12 at 17:51

The main problem is that the HTML is invalid to begin with, so most PHP XML/HTML parsers will fail when trying to parse out this particular HTML table. I used PHP Simple HTML DOM Parser to convert your table into JSON. This code assumes that that table.html contains only the table you've specified above.

<?php
include 'simplehtmldom/simple_html_dom.php';

$html = file_get_html('table.html');

$row_count = 0;

foreach($html->find('tr') as $row) {
  $row_count++;
  foreach($html->find('td') as $cell) {
    $cell_text = $cell->plaintext;
    // There are no CSS classes or IDs to differentiate between columns in the
    // table, so we're using the width instead
    switch ($cell->attr['width']) {
      case '58%':
        $bulletins[$row_count]['title'] = $cell_text;
        break;
      case '17%':
        $bulletins[$row_count]['type'] = $cell_text;
        break;
    }
    $bulletins[$row_count]['id'] = $row_count;
  }
}

// Remove the invalid </a> tags from the cell text, and convert to JSON
$json = str_replace('<\/a>', '', json_encode($bulletins));

// Output:
// {"1":{"id":1,"title":"Another News","type":"Updates"},"2":{"id":2,"title":"Another     News","type":"Updates"}}
echo $json;

?>

Simple html dom is actually more likely to fail than libxml2 based libs such as the one built in to php. — pguardiario, Jun 06 '12 at 00:21

score 1 · Accepted Answer · answered Jun 06 '12 at 00:40

Here's a quick example to get you started using only dom functions:

$dom = new DOMDocument();
@$dom->loadHTMLFile(url);
$xpath = new DOMXPath($dom);

$xml = new DOMDocument();
foreach($xpath->query('//table/tr') as $tr) {
  $bulletin = $xml->appendChild($xml->createElement("bulletin"));
  $title = $xpath->query('.//td[2]//a', $tr)->item(0)->nodeValue;
  $bulletin->appendChild($xml->createElement("title",$title));
  $type = $xpath->query('.//td[3]/font', $tr)->item(0)->nodeValue;
  $bulletin->appendChild($xml->createElement("type",$type));
}
echo $xml->saveXML();

score 0 · Answer 3 · answered Nov 15 '13 at 01:33

0

I've written a script that will do this for you, assuming you have a valid HTML table: https://github.com/tremblay/HTML-Table-to-JSON

With table formatted like that, you will run htmlToJSON('url.com', false, null, null, null, true);

answered Nov 15 '13 at 01:33

Colin Tremblay

357
1
9

Scrape HTML table data and create XML or JSON doc

3 Answers3