Screenscraper Parsing Machine

I use a family ancestry service and often encounter transcriptions formatted as a table with lots of data items and values. I only want some of this detail and so I created a ‘parsing machine’ 🙂  to pull out the bits I want to keep and ignore the rest.

I decided to do the project as a skills test too so it could be more streamlined than I’ve created by writing everything in PHP but instead I’m collecting data in PHP, passing part parsed data to Python which then passes it back to PHP. The final step abbreviates Data Item text and corrects Place names; these are stored in a MySQL database.

Source data example

The items in blue are the only Data Items I want to collect. The items in red are ones I want to correct.

First name(s)William
Address49 Bolas Heath
Last nameSmith
Full address as transcribed49 Roles Heath, Wellington Salop
SexMale
ParishBolas Magna
Birth year1868
Registration districtWellington (Shropshire)
Census date19-Jun-21
Registration district number355
Age in years53
Sub-districtWellington (Shropshire)
Age in months5
Sub-district number1
Age as transcribed53 y 5 m
Enumeration district number18
Relationship to headHead
CountyShropshire
Marital statusMarried
CountryEngland
Birth placeShropshire, England
Parliamentary borough or divisionShropshire PC, The Wrekin Div.
Birth countyShropshire
ArchiveThe National Archives
Birth countryEngland
Archive seriesRG 15
Birth place as transcribedBolas, Shropshire
Piece number12477
OccupationForman Farm
Schedule number48
Occupation code15
District referenceRD 355 RS 1 ED 18, 19, 20, 21
Occupation code with suffix015/6
Schedule type codeE
EmployerBurlugh, J J Davies Esg
Schedule typeEngland household, single page, 10 entries
Employer code10
Other household member’s first name(s)Mary, Agnes, John
Place of workBurleigh Farm, Willington Salop
Other household member’s last nameSmith
Number of minor dependants1
Record set1921 Census Of England & Wales
Language used to complete formEnglish
CategoryCensus, land & surveys
Person making returnWm Smith
SubcategoryCensus
House number49
Collections fromGreat Britain, England
StreetBolas Heath

The data items I’m interested in are stored in a table called categories which holds string pairs of what the data item is called in the source data and what I want to abbreviate it to. Priority is the order I want the resulting items to be in.

  • rowid int(11) auto_increment
  • fromtext varchar(80)  /* eg Place of work */
  • totext varchar(20) /* eg. work */
  • priority decimal(10,1)  /* priority eg. 3.4 */

The values table is more or less a spelling corrector but can be used to change Greater Manchester to Lancashire for example.

  • rowid int(11) auto_increment
  • fromtext varchar(80)  /* eg Willington */
  • totext varchar(20) /* eg. Wellington */

Form to accept paste input

Just use a copy-paste into the box…

The code is below. Essentially just a textarea box with some javascript to ensure HTML isn’t stripped as it usually would be.

input.php

<!DOCTYPE html>
<!-- DJB Dec 2023 -->
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<html>
<body>

<!-- catch pastes and ensure the HTML isn't stripped -->
<script>
    document.addEventListener('paste', function(e) {
        e.preventDefault();
        var pastedText = ''
        pastedText = e.clipboardData.getData('text/html');
        document.getElementById('pastein').innerHTML = pastedText
    });
</script>
<h2>Paste input</h2>

<form action="/cgi-bin/parse.py" method="POST">
  <label for="pastein">Paste In:</label><br>
  <textarea id="pastein" name="pastein" rows="20" cols="80">
  </textarea>
  <br>
  <input type="submit" value="Submit">
</form>
<br><br><p>To clear the box, please refresh the page.</p>

</body>
</html>

Python first stage parser

This needn’t be Python code of course and would be more elegant as one parsing program but I wanted to do it this way to play with some of the python functionality. The input form posts to /cgi-bin/parse.py (privs for the program need to be set to 755).

It passes its ‘work in progress’ content data as a flat file to the final parsing step which is executed by clicking on a link displayed. However, the link is probably not seen as it is autoclicked by javascript so it ‘feels’ like one seamless program.

parse.py

#!/usr/bin/python
# DJB Dec 2023
# Receive HTML paste of transcription text and parse it using semicolon as the delimeter 
# writing to flat file content.txt
print "Content-type: text/html\n\n"

import cgi, re, sys, os.path
from sys import platform

def remove_html_tags(text):
  inside_tag = False
  add_delim = False
  result = ''
  for char in text:
    if char == '<':
      inside_tag = True
      if add_delim:
        result += ";"
        add_delim = False
    elif char == '>':
      inside_tag = False
    else:
      if not inside_tag:
        add_delim = True
        result += char
  return result

def remove_non_ascii(text):
    return re.sub(r'[^\x00-\x7F]', ' ', text)

# RUN
# Read in paste from input form
form = cgi.FieldStorage()
if form.getvalue('pastein'):
    content = form.getvalue('pastein')
else:
    content = "No paste received"

# Debugging - write paste out to a file
#f = open("paste.txt", "w")
#f.write(content)
#f.close()
####

# remove oddball characters
regex = re.compile('[^a-zA-Z0-9,.<>#] ')
content = regex.sub('', content)
# top and tail to just get table rows 
content =  content[content.find("<tr"):len(content)]
content =  content[0:content.find('/tbody')-1]
# clean up
content = remove_html_tags(content).strip()
content = remove_non_ascii(content).strip()
#Move content into a list
contentlist = []
contentlist = content.split(';')

#if platform == "linux":    #ie. RPi
#  for x in range(len(contentlist)):
#      print(contentlist[x],end="")
#      if x % 2 == 1:
#        print(" ")
#if platform == "linux2":   # ie Apache etc
#  for x in range(len(contentlist)):
#      print(contentlist[x])
#      if x % 2 == 1:
#        print("<br>")

# Write the parsed content string out to flat file for PHP read
f = open("content.txt", "w")
f.write(content)
f.close()
####

print('<a id=nextstep href="../output.php">Click to process</a>')

print('<script>window.onload=function(){document.getElementById("nextstep").click();};</script>')

PHP second stage parser

This is standard PHP. It reads in the ‘work in progress’ content data, together with the Data Items and Values tables from SQL and processes all the data in multidimensional arrays.

output.php

<html>
<head>
<title>
Output
</title>
</head>
<body>
<?php
// DJB Dec 2023
// Read in Categories and Places tables into multidimension arrays
// Read in initially parsed HTML screen scrape into 1D contents array (category||value)
session_cache_limiter('private');

// Connect to database
require 'phpgen_settings.php';
$res = GetGlobalConnectionOptions();
$dsn = "mysql:host=".$res['server'].";dbname=".$res['database'].";charset=utf8;port=".$res['port'];
$options = [
    PDO::ATTR_EMULATE_PREPARES   => false, // Disable emulation mode for "real" prepared statements
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Disable errors in the form of exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Make the default fetch be an associative array
  ];
try {
    $pdo = new PDO($dsn, $res['username'], $res['password'], $options);
    if ($pdo) {
        #echo "Connected successfully!<br>";
        }
    } catch (PDOException $e) {
        echo $e->getMessage();die();
    }
// Read Categories table into multidimension array categories(fromtext, totext, priority)
$stmt = $pdo->query("SELECT replace(rtrim(`fromtext`),'?',''),`totext`,`priority` FROM `j23-categories`"); // Singlequotes are for values, backticks for table/column-names
$categories = $stmt->fetchAll(PDO::FETCH_NUM);

// Read Places table into multidimension array places(fromtext, totext)
$stmt = $pdo->query("SELECT `fromtext`,`totext` FROM `j23-places`"); 
$places = $stmt->fetchAll(PDO::FETCH_NUM);

// Read content file as a string 
$contentstring = file_get_contents(__DIR__. '/cgi-bin/content.txt');
// parse content splitting by semicolon into array contents(category||value)
$contents = explode(";",$contentstring);
// initialise output array
$output = [];
// process each element of the contents array which could be a category or a value 
// (categories are even elements and values are odd elements)
for ($x = 0; $x < count($contents); $x++) {
    if ($x == 0 || $x % 2 == 0) { /* even = fromtext */
        $from = $contents[$x];
    }
    else { /* odd = value */
        $searchValue = rtrim($from);
        // check if the contents category is found in the categories array
        $key = array_search($searchValue, array_column($categories, 0));   
        if (!empty($key) || $key === 0) {  
            // contents category found and so push category newtext and other items into 
            // 3D subarray of priority,totext,value
            array_push($output, array($categories[$key][2],$categories[$key][1],$contents[$x]));
        } 
        $from = "";
        $searchValue = "";     
    }
  }
// sort output array by categories.priority which is 1st element in the subarray
$key_values = array_column($output, 0); 
array_multisort($key_values, SORT_ASC, $output);
$lastcat = "";
// Loop through output array 
for($x = 0; $x < count($output); $x++){
    $OutputCat = $output[$x]['1'];
    $OutputVal = $output[$x]['2'];
    $NewOutVal = "";
    // Assemble category and value pair 
    // Check if value has a substring which is found in the places array and 
    // if found replace the value with the corrected place value
    foreach ($places as $valuePlaces) {
        $PlacesFromtext = $valuePlaces[0];
        $PlacesTotext = $valuePlaces[1];
        if (strpos($OutputVal, $PlacesFromtext) !== false) {
        $NewOutVal = str_replace($PlacesFromtext,$PlacesTotext,$OutputVal);
        }
    }
    // Check if duplicate OutputCats and if so ignore lower priority ones.
    if ($OutputCat != $lastcat) {
        // Print a category, value pair 
        // if the value has been corrected print that instead 
        if (strlen($NewOutVal) > 0) {
            echo $OutputCat.$NewOutVal;
        } else {
            echo $OutputCat.$OutputVal;
        }
        // unless last element of output array print a semicolon after the category, value pair
        if ($x < count($output)-1) {
            echo "; ";
        }
    }
    $lastcat = $OutputCat;
  }
?>
<style>
button, input {
  background-color: rgb(240, 240, 240);
  color: black;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  font-weight: bold;
  display: inline-block;
  border: 1px solid #333333;
</style>

<p>&nbsp;</p>
<input type="button" id="myBtn" value="Return to input" onfocus="window.location.href = 'https://play.squirrelhouse.biz/input.php';">

<script>
document.addEventListener("keypress", function(event) {
    if (event.keyCode === 13) {
        event.preventDefault();
        document.getElementById("myBtn").focus();
        document.getElementById("myBtn").click();
    }
});
</script> 
</body>
</html>

The fully processed output is shown below. It has been abbreviated, corrected and reordered based on the MySQL data.