PowerShell Integration | DataMystic Developer

PowerShell Integration with TextPipe COM

Automate data transformations from PowerShell using the TextPipe COM API. These examples cover COM object instantiation, filter execution, output verification, batch folder processing, and proper error handling with resource cleanup.

Prerequisites: TextPipe Pro edition must be installed and registered. PowerShell 5.1 or later is required. Run scripts with appropriate execution policy: Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

Basic Usage

The simplest TextPipe COM automation: instantiate the COM object, load a filter, add input files, execute the transformation, and release the COM object.

# Basic TextPipe COM automation from PowerShell
# Requires: TextPipe Pro installed and registered

# Create the COM object
$tp = New-Object -ComObject TextPipe.Application

try {
    # Configure for unattended operation
    $tp.Silent = $true
    $tp.Visible = $false
    $tp.OverwriteOutput = $true

    # Load a pre-configured filter list
    $filterLoaded = $tp.LoadFilter("C:\Filters\cleanup_csv.fll")
    if (-not $filterLoaded) {
        throw "Failed to load filter: $($tp.GetLastError())"
    }

    # Add input file and set output destination
    $tp.AddInputFile("C:\Data\raw_export.csv")
    $tp.OutputFile = "C:\Data\cleaned_export.csv"

    # Execute the transformation
    $filesProcessed = $tp.Go()

    Write-Host "Successfully processed $filesProcessed file(s)."
}
catch {
    Write-Error "TextPipe automation failed: $_"
    exit 1
}
finally {
    # Always release the COM object to prevent memory leaks
    $tp.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($tp) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

Output Verification

After executing a transformation, verify that the output file exists, has content, and optionally compare file sizes or check for expected patterns.

# TextPipe COM automation with output verification
param(
    [Parameter(Mandatory=$true)]
    [string]$FilterPath,

    [Parameter(Mandatory=$true)]
    [string]$InputFile,

    [Parameter(Mandatory=$true)]
    [string]$OutputFile
)

$tp = New-Object -ComObject TextPipe.Application

try {
    $tp.Silent = $true
    $tp.Visible = $false
    $tp.OverwriteOutput = $true

    # Load filter
    if (-not $tp.LoadFilter($FilterPath)) {
        throw "Filter load failed: $($tp.GetLastError())"
    }

    # Configure input/output
    $tp.AddInputFile($InputFile)
    $tp.OutputFile = $OutputFile

    # Record start time for performance tracking
    $startTime = Get-Date

    # Execute transformation
    $filesProcessed = $tp.Go()
    $duration = (Get-Date) - $startTime

    # --- Output Verification ---

    # Check 1: File exists
    if (-not (Test-Path $OutputFile)) {
        throw "Output file was not created: $OutputFile"
    }

    # Check 2: File is not empty
    $outputInfo = Get-Item $OutputFile
    if ($outputInfo.Length -eq 0) {
        throw "Output file is empty (0 bytes): $OutputFile"
    }

    # Check 3: Output has reasonable size relative to input
    $inputInfo = Get-Item $InputFile
    $sizeRatio = $outputInfo.Length / $inputInfo.Length
    if ($sizeRatio -lt 0.1) {
        Write-Warning "Output is less than 10% of input size. Verify transformation is correct."
    }

    # Check 4: Verify expected content pattern (optional)
    $firstLine = Get-Content $OutputFile -TotalCount 1
    if ([string]::IsNullOrWhiteSpace($firstLine)) {
        Write-Warning "First line of output is blank."
    }

    # Summary
    Write-Host "Transformation complete:" -ForegroundColor Green
    Write-Host "  Files processed: $filesProcessed"
    Write-Host "  Input size:      $($inputInfo.Length) bytes"
    Write-Host "  Output size:     $($outputInfo.Length) bytes"
    Write-Host "  Duration:        $($duration.TotalSeconds.ToString('F2')) seconds"
}
catch {
    Write-Error "Verification failed: $_"
    exit 1
}
finally {
    $tp.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($tp) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

Batch Folder Processing

Process all files in one or more folders, with logging and per-folder summaries. Useful for nightly batch jobs or scheduled data pipeline stages.

# Batch folder processing with TextPipe COM
# Process multiple source folders and log results

$filterPath = "C:\Filters\data_standardization.fll"
$logFile = "C:\Logs\textpipe_batch_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

$sourceFolders = @(
    @{ Path = "C:\Data\Sales";     Mask = "*.csv"; Recurse = $true },
    @{ Path = "C:\Data\Marketing"; Mask = "*.tsv"; Recurse = $false },
    @{ Path = "C:\Data\Finance";   Mask = "*.txt"; Recurse = $true }
)

$tp = New-Object -ComObject TextPipe.Application

try {
    $tp.Silent = $true
    $tp.OverwriteOutput = $true
    $tp.LogFile = $logFile

    # Load the transformation filter once
    if (-not $tp.LoadFilter($filterPath)) {
        throw "Failed to load filter: $($tp.GetLastError())"
    }

    $totalProcessed = 0

    foreach ($folder in $sourceFolders) {
        # Clear previous input files
        $tp.ClearInputFiles()

        # Add folder contents
        $tp.AddInputFolder($folder.Path, $folder.Mask, $folder.Recurse)

        # Create output folder alongside source
        $outputPath = Join-Path $folder.Path "Processed"
        if (-not (Test-Path $outputPath)) {
            New-Item -ItemType Directory -Path $outputPath -Force | Out-Null
        }
        $tp.OutputFolder = $outputPath

        # Execute
        $count = $tp.Go()
        $totalProcessed += $count

        Write-Host "Folder: $($folder.Path) - Processed $count files -> $outputPath"
    }

    Write-Host "`nBatch complete. Total files processed: $totalProcessed" -ForegroundColor Green
    Write-Host "Log file: $logFile"
}
catch {
    Write-Error "Batch processing failed: $_"
    exit 1
}
finally {
    $tp.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($tp) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

Error Handling Patterns

Robust error handling is essential for production automation. Use structured try/catch/finally to handle COM errors, validate inputs, and guarantee cleanup.

# Robust error handling pattern for TextPipe COM automation
function Invoke-TextPipeTransform {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [ValidateScript({ Test-Path $_ -PathType Leaf })]
        [string]$FilterPath,

        [Parameter(Mandatory)]
        [ValidateScript({ Test-Path $_ -PathType Leaf })]
        [string]$InputFile,

        [Parameter(Mandatory)]
        [string]$OutputFile,

        [int]$TimeoutSeconds = 300
    )

    # Validate output directory exists
    $outputDir = Split-Path $OutputFile -Parent
    if (-not (Test-Path $outputDir)) {
        New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
    }

    $tp = $null

    try {
        # Attempt COM instantiation
        $tp = New-Object -ComObject TextPipe.Application -ErrorAction Stop
    }
    catch [System.Runtime.InteropServices.COMException] {
        throw "TextPipe COM object not registered. Run: regsvr32 TextPipe.dll (as Administrator)"
    }
    catch {
        throw "Cannot create TextPipe COM object: $_"
    }

    try {
        $tp.Silent = $true
        $tp.Visible = $false
        $tp.OverwriteOutput = $true

        # Load filter with validation
        if (-not $tp.LoadFilter($FilterPath)) {
            $err = $tp.GetLastError()
            throw "Filter load failed for '$FilterPath': $err"
        }

        # Add input and configure output
        if (-not $tp.AddInputFile($InputFile)) {
            throw "Cannot add input file '$InputFile': $($tp.GetLastError())"
        }
        $tp.OutputFile = $OutputFile

        # Execute with timeout monitoring
        $job = Start-Job -ScriptBlock {
            param($tpRef) $tpRef.Go()
        } -ArgumentList $tp

        $completed = $job | Wait-Job -Timeout $TimeoutSeconds
        if (-not $completed) {
            $job | Stop-Job
            throw "Transformation timed out after $TimeoutSeconds seconds"
        }

        $filesProcessed = $job | Receive-Job
        $job | Remove-Job

        if ($filesProcessed -eq 0) {
            $err = $tp.GetLastError()
            throw "No files processed. Error: $err"
        }

        # Return result object
        [PSCustomObject]@{
            Success        = $true
            FilesProcessed = $filesProcessed
            OutputFile     = $OutputFile
            OutputSize     = (Get-Item $OutputFile).Length
        }
    }
    catch {
        [PSCustomObject]@{
            Success = $false
            Error   = $_.Exception.Message
        }
    }
    finally {
        if ($tp) {
            $tp.Quit()
            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($tp) | Out-Null
            [System.GC]::Collect()
            [System.GC]::WaitForPendingFinalizers()
        }
    }
}

# Usage example
$result = Invoke-TextPipeTransform `
    -FilterPath "C:\Filters\ebcdic_to_ascii.fll" `
    -InputFile "C:\Data\mainframe_extract.dat" `
    -OutputFile "C:\Data\converted_output.csv" `
    -TimeoutSeconds 600

if ($result.Success) {
    Write-Host "Success! Output: $($result.OutputFile) ($($result.OutputSize) bytes)"
} else {
    Write-Error "Failed: $($result.Error)"
    exit 1
}

Advanced: Reusable Function Module

Wrap TextPipe COM automation in a PowerShell module for reuse across scripts and scheduled tasks. This pattern ensures consistent cleanup and makes it easy to integrate into larger automation pipelines.

# TextPipeModule.psm1 - Reusable PowerShell module for TextPipe automation

function New-TextPipeSession {
    <#
    .SYNOPSIS
        Creates a new TextPipe COM automation session.
    .DESCRIPTION
        Instantiates the TextPipe.Application COM object configured for
        silent, unattended operation. Returns a session object that must
        be closed with Close-TextPipeSession when finished.
    #>
    [CmdletBinding()]
    param(
        [string]$LogFile
    )

    $tp = New-Object -ComObject TextPipe.Application
    $tp.Silent = $true
    $tp.Visible = $false
    $tp.OverwriteOutput = $true

    if ($LogFile) {
        $tp.LogFile = $LogFile
    }

    return $tp
}

function Close-TextPipeSession {
    <#
    .SYNOPSIS
        Cleanly releases a TextPipe COM session.
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [object]$Session
    )

    $Session.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Session) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

function Invoke-TextPipeFilter {
    <#
    .SYNOPSIS
        Executes a TextPipe filter against input files.
    .EXAMPLE
        $session = New-TextPipeSession
        Invoke-TextPipeFilter -Session $session -Filter "C:\f.fll" -Input "C:\in.csv" -Output "C:\out.csv"
        Close-TextPipeSession -Session $session
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)][object]$Session,
        [Parameter(Mandatory)][string]$Filter,
        [Parameter(Mandatory)][string]$Input,
        [Parameter(Mandatory)][string]$Output
    )

    $Session.ClearInputFiles()

    if (-not $Session.LoadFilter($Filter)) {
        throw "Load filter failed: $($Session.GetLastError())"
    }

    if (Test-Path $Input -PathType Container) {
        $Session.AddInputFolder($Input, "*.*", $true)
        $Session.OutputFolder = $Output
    } else {
        $Session.AddInputFile($Input)
        $Session.OutputFile = $Output
    }

    $count = $Session.Go()
    if ($count -eq 0 -and $Session.GetLastError()) {
        throw "Execution failed: $($Session.GetLastError())"
    }

    return $count
}

Export-ModuleMember -Function New-TextPipeSession, Close-TextPipeSession, Invoke-TextPipeFilter

Using the Module

# Import and use the TextPipe module
Import-Module .\TextPipeModule.psm1

$session = New-TextPipeSession -LogFile "C:\Logs\transform.log"

try {
    # Process multiple transformations in one session
    $count1 = Invoke-TextPipeFilter -Session $session `
        -Filter "C:\Filters\step1_normalize.fll" `
        -Input "C:\Data\raw.csv" `
        -Output "C:\Data\normalized.csv"

    $count2 = Invoke-TextPipeFilter -Session $session `
        -Filter "C:\Filters\step2_validate.fll" `
        -Input "C:\Data\normalized.csv" `
        -Output "C:\Data\validated.csv"

    Write-Host "Pipeline complete: Step 1 ($count1 files), Step 2 ($count2 files)"
}
finally {
    Close-TextPipeSession -Session $session
}

Next Steps

TextPipe COM API Reference — Full method and property documentation
Python Integration — pywin32 COM automation examples
CI/CD Integration — Pipeline configuration for automated transforms
Batch Processing Examples — Command-line batch automation