Objective: Enable payroll staff to upload monthly leave balances from Pronto (ODS format) to Humanforce via SFTP.

Prerequisites

  • Humanforce Access: Admin account with access to System Config, Integration Central, and Shift Types.

  • SFTP Credentials: Username and Public/Private Key pair provided by Humanforce Support.

  • Workstation: Windows PC with WinSCP and Microsoft Excel installed.

Phase 1: Humanforce Admin Configuration

Standardize the "Export Codes" so the script can map Pronto data correctly.

  1. Log in to Humanforce and navigate to Admin > Shift Types (or System Config > Shift Setup).

  2. Edit the following Shift Types and set their Export Code exactly as follows:

    • Annual Leave $\rightarrow$ Export Code: AL

    • Sick Leave $\rightarrow$ Export Code: SL

    • LSL Taken $\rightarrow$ Export Code: LSL

  3. Save all changes.

Phase 2: Integration Central Setup

Create the import task that will process the file once uploaded.

  1. Navigate to Integration Central.

  2. Click New > File Import.

  3. General Settings:

    • Name: Leave Balance Import SFTP

    • Data Type: Employee Leave Balance

    • File Source: Humanforce SFTP Import

  4. File Selection:

    • Upload a dummy file named humanforce_upload.csv to the SFTP folder first (so you can select it), or select any existing CSV to proceed.

  5. Mapping:

    • Map the columns as follows:

      • EmployeeCode $\rightarrow$ Source: Employee Code

      • ShiftType $\rightarrow$ Source: Shift Type | Lookup Type: ExportCode (Critical Step)

      • Hours $\rightarrow$ Source: Hours

      • Type $\rightarrow$ Source: Transaction Type

  6. Schedule: You can leave this blank (Manual Run) or set a schedule if the upload happens at a predictable time.

  7. Save the integration.

Phase 3: Workstation Setup (User's PC)

A. Script Deployment

  1. Create a dedicated folder on the user's PC (e.g., C:\Humanforce_Uploads\).

  2. Save the PowerShell script below as Convert-LeaveBalances.ps1 inside that folder.

Script Code:

PowerShell
# CONFIGURATION
$SourcePattern = ".\*.ods"
$TempCsv       = ".\temp_converted.csv"
$OutputFile    = ".\humanforce_upload.csv"

# --- PART 1: FIND NEWEST ODS FILE ---
$odsFile = Get-ChildItem $SourcePattern | Sort-Object LastWriteTime -Descending | Select-Object -First 1

if (-not $odsFile) {    Write-Error "No .ods file found. Please save the Pronto report here."    Read-Host -Prompt "Press Enter to exit"    exit
} 
Write-Host "Found newest file: $($odsFile.Name)"
Write-Host "Timestamp: $($odsFile.LastWriteTime)"

# --- PART 2: CONVERT ODS TO CSV (Using Excel) ---
Write-Host "Converting ODS to CSV using Excel..."
try {    $excel = New-Object -ComObject Excel.Application    $excel.Visible = $false    $excel.DisplayAlerts = $false    $workbook = $excel.Workbooks.Open($odsFile.FullName)    $workbook.SaveAs($((Get-Item .).FullName + "\temp_converted.csv"), 6)    $workbook.Close($false)    $excel.Quit() }
catch {    Write-Error "Failed to convert file. Ensure Excel is installed."    if ($excel) { [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null }    Read-Host -Prompt "Press Enter to exit"    exit
} 
# --- PART 3: PROCESS DATA ---
Write-Host "Processing leave balances..."
$prontoData = Import-Csv $TempCsv
$humanforceRows = @() 
foreach ($row in $prontoData) {    $empID = $row."Employee No."
    # ANNUAL LEAVE (Code: AL)    $al_bal = [float]$row."Annual lve entitlement" - [float]$row."Annual lve taken"    if ($al_bal -ne 0) {        $humanforceRows += [PSCustomObject]@{ "Employee Code"=$empID; "Shift Type"="AL"; "Hours"="{0:N4}" -f $al_bal; "Transaction Type"="Balance" }    }     # SICK LEAVE (Code: SL)    $sl_bal = [float]$row."Sick lve entitlement" - [float]$row."Sick lve taken"    if ($sl_bal -ne 0) {        $humanforceRows += [PSCustomObject]@{ "Employee Code"=$empID; "Shift Type"="SL"; "Hours"="{0:N4}" -f $sl_bal; "Transaction Type"="Balance" }    }     # LSL TAKEN (Code: LSL)    $lsl_bal = [float]$row."Ls lve entitlement" - [float]$row."Ls lve taken"    if ($lsl_bal -ne 0) {        $humanforceRows += [PSCustomObject]@{ "Employee Code"=$empID; "Shift Type"="LSL"; "Hours"="{0:N4}" -f $lsl_bal; "Transaction Type"="Balance" }    } }

# --- PART 4: EXPORT ---
$humanforceRows | Export-Csv -Path $OutputFile -NoTypeInformation
Remove-Item $TempCsv -ErrorAction SilentlyContinue 
Write-Host "Success! Ready to upload."
Start-Sleep -Seconds 3

B. WinSCP Configuration

  1. Install WinSCP on the user's machine.

  2. Open WinSCP and set the following parameters:

    • File Protocol: SFTP

    • Host name: tf.humanforce.com (Use tf.humanforce.co.uk if in Europe)

    • Port: 22

    • User name: [Insert Username provided by Support]

  3. Authentication (Private Key):

    • Click Advanced... > SSH > Authentication.

    • In Private key file, browse to the .ppk key file.

    • Note: Ensure "Allow agent forwarding" is unchecked.

    • Click OK.

  4. Save the Session:

    • Click Save on the main login screen.

    • Name the site Humanforce.

    • Optional: Create a desktop shortcut for easier access.

Verification Test

  1. Save a sample Pronto ODS file into the upload folder.

  2. Run the PowerShell script. Verify humanforce_upload.csv is created.

  3. Open WinSCP and upload the CSV file.

  4. Run the task in Integration Central and check the logs for "Success".