Automatically sending CollectionHQ MARC files

Someone requested the process we use to send CollectionHQ files automatically. This process assumes you have access to your SimplyReports server directly, if you don’t then you’ll need to take another approach.

To automatically send our CollectionHQ files, we do the following:

  1. Set up a SimplyReports ITEM MARC EXPORT
  2. Schedule that job to run weekly or whatever schedule CollectionHQ requests.
    • If you have more than one library, you should set up multiple exports multiple jobs and schedule them to run at different hours of the day.
    • Exporting MARC records is a system intensive task in Polaris, therefore we do NOT run it on our production system, but run it on a training system that gets a daily copy of the data from production.
    • Make sure to use a special account to schedule this that will NEVER be disabled or deleted in Polaris. For example, don’t tie it to your personal account.
  3. Create a Powershell script, our script is below.
    • You’ll need to adjust file paths, file names and timings to match your library(ies).
    • If you only have one library, you can probably simplify this down to just the FT P sending portion.
    • This is modified from what we actually run in production, if it doesn’t seem to be running, past any errors on this thread and we’ll try to help troubleshoot.
  4. We use Windows Task Scheduler to schedule the script to run nightly from the SimplyReports server. Make sure the Task Scheduler job starts AFTER the final CollectionHQ export is finished.
  5. We monitor for the presence of 4 files in the $path shown below. If it doesn’t have 4 files we get alerted that something went wrong.
# Start transcription to log the script's activities
Start-Transcript -Path "C:\transcripts\transcript0.txt" -Force

# This script deletes old MARC export files, renames them, and sends them to Collection HQ

# Define paths and configuration variables
$path = "C:\ProgramData\Polaris\SRServiceRoot\TrainSR\129\scheduledjobs"
$adhocpath = "C:\ProgramData\Polaris\SRServiceRoot\TrainSR\129\adhocreports"
$scriptpath = "C:\Programdata\clc_send_CollectionHQ_data"
$hours = 120 # Files older than this will be deleted (5 days)
$ext = ".mrc"
$ftpurl = ""
$ftpuser = "ENTER-FTP-USER"
$ftpremotepath = "/"
$dayofweektorun = "Tuesday"

# Check if today is the specified day of the week to run the script
if ((get-date).DayOfWeek -ne $dayofweektorun) {
    Write-Output "Not the proper day of the week to run... exiting"

# Check if the scheduledjobs folder is empty, if so switch to the adhoc folder
if ((Get-ChildItem -Path $path | Measure-Object).Count -eq 0) {
    Write-Output "Nothing in scheduledjobs folder, switching to adhoc folder."
    $path = $adhocpath

# Create necessary directories if they do not exist
if (!(Test-Path "$scriptpath\log")) {
    New-Item -Path "$scriptpath\log" -ItemType Directory

# Create a new log file
$log = "$scriptpath\log\$(Get-Date -format 'yyyyMMddTHHmmss').log"
if (!(Test-Path $log)) { New-Item -Path $log -Force | Out-Null }

# Delete all non-MRC files regardless of age
Get-ChildItem -Path $path -File | Where-Object { $_.Extension -ne $ext } | Remove-Item

# Delete all files older than the specified number of hours
Get-ChildItem -Path $path | Where-Object { $_.LastWriteTime -lt (Get-Date).AddHours(-$hours) } | Remove-Item

# Get remaining files in the folder
$files = Get-ChildItem -Path $path

Write-Output "Checking for files in $path" | Tee-Object -File $log -Append

# Rename files based on the hour they were created
foreach ($file in $files) {
    $hourcreated = $file.CreationTime.ToString("HH")
    $fileext = $file.Extension

    switch ($hourcreated) {
        "01" { $libraryname = "CollectionHQ-clc-LIBRARYHOUR1-" }
        "02" { $libraryname = "CollectionHQ-clc-LIBRARYHOUR2-" }
        default { $libraryname = "CollectionHQ-clc-library-unknown-" }

    Rename-Item -Path $file.FullName -NewName "$libraryname$($file.LastWriteTime.ToString('yyyyMMddTHHmmssffff'))$fileext"

# Change to the script directory
Set-Location $scriptpath

Write-Output "Setting up FTP session" | Tee-Object -File $log -Append

# Get the renamed MARC files
$files = Get-ChildItem -Path $path | Where-Object { $_.Extension -eq $ext }

# Upload files via FTP
foreach ($file in $files) {
    Write-Output "Uploading Marc $file..." | Tee-Object -File $log -Append

    echo open $ftpurl > chq.txt
    echo $ftpuser >> chq.txt
    echo $ftppass >> chq.txt
    echo lcd $path >> chq.txt
    echo put $file.FullName >> chq.txt
    echo quit >> chq.txt
    ftp -s:chq.txt
    Remove-Item chq.txt

# Close the FTP session
Write-Output "Closing FTP Session" | Tee-Object -File $log -Append

# Stop transcription