1
$\begingroup$

I am an Ex Service Veteran and need assistance with a Small Program to use with my Rats of Tobruk Project, for the purpose of evaluating Archives Information, which I normally do by Manual Means, but my List has grown to 11,663 records that need Verification.

I have put this together as a Guide to what needs to happen. I hope someone with experience can assist me with a Program.

Automate Finding Service Records & Evaluation

  1. I produce an Excel List from my National Rats of Tobruk Database, consisting of two (2) Columns – Service Number and NAA.

    • a) Service Number will contain the List of Service Numbers to be found and evaluated, in sequence.

    • b) NAA will initially be Blank, but will be changed to (Y) if a Condition is found “True” for the Loaded Service Number.

  2. Open Browser (Firefox or Chrome)

  3. From Excel Service Number List, Enter the first “Service Number” in the Pad = “Keywords” and Press “Enter” or Click “Search”

    • a) If the Column “Digitised Item” is Blank – Loop Back – load the next Service Number from the Excel Service Number List and evaluate.

    • b) If the Column “Digitised Item” shows a "Document Icon" or Other Content, the Condition = True – Go To Excel Service Number List, and show “Y” in the NAA Column, for that Loaded Service Number.

  4. On the Web Page, Click “New Search” and Load the next Service Number from the Excel Service Number List and Repeat the Evaluation Process for each Service Number, till the end of the Excel Service Number List is exhausted.

  5. When the Service Number List is completed, Save the Excel File to Desktop/ROTWEB/Service Number NAA Test. (ROTWEB is a Folder on my Desktop)

I hope you are able to follow my thoughts - if not please contact me, either with a Reply Post or by my Email ( ocar23@iinet.net.au ) Best Regards Owen Carlton ( www.ratsoftobruktribute.com )

$\endgroup$
1
$\begingroup$

You ask for quite a bit. The webpage is dynamic, so you would need to run an automated browser, navigate to the search page, enter the service number, press "search" and see if there is any item indicating a digital copy.

One way to do this is in Python, a free programming language. Find Python here and a "howto install" tutorial here.

Make sure you install Python with pip, since it lets you install additional free modules very easily. To install additional packages you simply need to type cmd in the search field in windows. This shows you the command line interface (a black window). When you have Python with pip installed, you can simply type pip install XYZ (press enter), to install the package XYZ (where XYZ is the name of the package).

You also need to download the Selenium Chrome driver (the version must match the version number of you current Chrome browser). This free software allows Python to automatically use Chrome to browser pages. The Chrome driver is a single file, which must be copied to the place where you have Python on your computer.

The script below does exactly what you want. It searches a list of "numbers" from an excel file one by one on the NAA page. For each request, the script looks for digital documents and "notes" the number of found digital documents. Finally, the results are saved to an excel file. My dummy result would look like:

     servnr  digicopy
0      1234         2
1      6516        15
2     51651         2
3  51651651         0

Maybe you have a friend or someone in your family who can help you install Python. Usually this is a little daunting for people with no programming experiance. If you elaborate my script below, you could even download the digital documents or extract information (e.g. by using OCR software like tesseract).

Basic code for the webcrawler:

# Import required additional packages (to be installed via pip first)
from bs4 import BeautifulSoup 
from urllib.request import urlopen 
import re, time, datetime, json, requests
import pandas as pd
# Chrome Webdriver https://chromedriver.chromium.org/downloads
from selenium import webdriver
from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.by import By

# When you see something like "ModuleNotFoundError: No module named 'openpyxl'"
# You need to install the pip package, e.g. pip install openpyxl
# Install pip packages in the console window (type cmd in the search field in Windows bottom left, open it, type pip...)

# Load Excel containing service numbers 
# Change the path to the file is needed
df = pd.read_excel('D:/service_numbers.xlsx')

# Start chrome driver https://chromedriver.chromium.org/downloads
options = webdriver.ChromeOptions()
#options.add_argument('headless') # in background option
options.add_argument('log-level=3')
browser = webdriver.Chrome(chrome_options=options)

# Set up empty data frame to store results
results = pd.DataFrame([])

# Loop over each number (aka row) in the excel file (column name 'snr')
for s in df['snr']:
    browser.get("https://www.naa.gov.au/")
    time.sleep(0.5)
    # Go to record search 
    browser.find_element_by_xpath("//*[@id=\"block-naa-mainpagecontent\"]/div/div[4]/div[2]/div/div/div/div/a").click()
    time.sleep(0.5)

    # Find searchfield and send number "s", press enter
    print("Loading number %s" %s)
    searchfield = browser.find_element_by_xpath("//*[@id=\"ContentPlaceHolderSNR_tbxKeyword\"]") # find field    
    searchfield.send_keys(s) # enter searchterm (the service number)
    searchfield.send_keys('\n') # press enter
    time.sleep(0.5)

    # Load the content of the current page (I do not iterate over all resultrs but only look at the first page here)
    soup = BeautifulSoup(browser.page_source, "html.parser")
    table = soup.find("table", id="ContentPlaceHolderSNR_tblItemDetails")
    # You can also load/print the entire table, you can even go directly to the digital contant (if present)
    #print(table.text)

    # See how many "images" indicating digital content are there
    imgs = soup.find_all('img', {'class':'digital_copy'})
    try:
        numberofdigiitems = len(imgs)
    except:
        numberofdigiitems = 0
    print("There are %s digital copys" %numberofdigiitems)

    # Append result to data frame
    results = results.append(pd.DataFrame({'servnr': s, 'digicopy': numberofdigiitems}, index=[0]), ignore_index=True)

print(results.head())

# Save to excel
results.to_excel("D:/myresults.xlsx", index=False)
| improve this answer | |
$\endgroup$
  • $\begingroup$ Hi Peter, Can you please advise how I can Name this Script, so that it can be Stored and Recovered and Edited, as I have no hope of doing this in one day. Thank You. $\endgroup$ – user222642 Jun 2 at 6:33
  • $\begingroup$ Do you mean the excel file? You can just rename the file where it is saved at the very bottom, e.g. myresults2.xlsx, when you start a new session. $\endgroup$ – Peter Jun 2 at 10:58
  • $\begingroup$ Hi Peter, No I meant how to Name the Created Script and Store It, Recover it, Edit it and Run it. As I would have no hope of completing my Script in one Session and would need to come back to Edit after Teastin.I am familiar with Excel. $\endgroup$ – user222642 Jun 3 at 0:28
  • $\begingroup$ You mean the code? You can simply save it as a plain text file (type notebook in searchbar) copy paste code into editor. Make sure the file ending is .py since this is the ending any Python editor will recognise. $\endgroup$ – Peter Jun 3 at 9:41
  • $\begingroup$ To try to speed this up, due to my Lack of Python Knowledge, I downloaded and Installed PyCharm. which has a GUI, to make it easier for me. $\endgroup$ – user222642 Jun 6 at 1:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.