Author Posts

August 2, 2018 at 2:29 pm

Hey guys i'm fairly new to powershell. I replaced a guy before who was automating imports of data and then outputting them into folders. It works great. The problem is that when a user executes these scripts, they never know how long a script will take. Sometimes it takes 1 min, sometimes 5, to 10 to 15. So the user has to sit there waiting until they see the output of a file to know its successful. So my solution was to add a progress bar to it so that the user can carry on doing something else till something the task is at 100% completion.

My question is... how would add a progress bar to a long string of code? As a for loop?

I know this

for ($a=1; $a -le 100; $a++)
{
Write-Progress -Activity "Working..." -PercentComplete $a -CurrentOperation "$a% complete" -Status "Please Wait"; Start-Sleep 1}

Will create a progress bar, so how would i include all this in the for () so that the progress bar times it:

### Andrews Transactional Reporting ### 
function Export-Xls 
{  
    [CmdletBinding(SupportsShouldProcess=$true)] 
     Param( 
        [parameter(mandatory=$true, position=1)] 
        $Path, 
     
        [parameter(mandatory=$false, position=2)] 
        $Worksheet = "Sheet", 
        [parameter( 
            mandatory=$true,  
            ValueFromPipeline=$true, 
            ValueFromPipelineByPropertyName=$true)] 
        [psobject[]] 
        $InputObject, 
        [parameter(mandatory=$false)] 
        [switch] 
        $Append, 
        [parameter(mandatory=$false)] 
        [switch] 
        $NoClobber, 
        [parameter(mandatory=$false)] 
        [switch] 
        $NoTypeInformation, 
        [parameter(mandatory=$false)] 
        [switch] 
        $Force 
    ) 
   Begin 
    { 
        $shouldProcess = $Force -or $psCmdlet.ShouldProcess($Path); 
        if (-not $shouldProcess) { return; } 
        function GetTempFileName($extension) 
        { 
            $temp = [io.path]::GetTempFileName(); 
            $params = @{ 
                Path = $temp; 
                Destination = $temp + $extension; 
                Confirm = $false; 
                Verbose = $VerbosePreference; 
            } 
            Move-Item @params; 
            $temp += $extension; 
            return $temp; 
        } 
        $xlFileFormats = @{ 
            # single worksheet formats 
            '.csv'  = 6;        # 6, 22, 23, 24 
            '.dbf'  = 11;       # 7, 8, 11 
            '.dif'  = 9;        #  
            '.prn'  = 36;       #  
            '.slk'  = 2;        # 2, 10 
            '.wk1'  = 31;       # 5, 30, 31 
            '.wk3'  = 32;       # 15, 32 
            '.wk4'  = 38;       #  
            '.wks'  = 4;        #  
            '.xlw'  = 35;       #  
            # multiple worksheet formats 
            '.xls'  = -4143;    # -4143, 1, 16, 18, 29, 33, 39, 43 
            '.xlsb' = 50;       # 
            '.xlsm' = 52;       # 
            '.xlsx' = 51;       # 
            '.xml'  = 46;       # 
            '.ods'  = 60;       # 
        } 
        $ext = [io.path]::GetExtension($Path).toLower(); 
        if ($xlFileFormats.Keys -notcontains $ext) { 
            $msg = "Error: $Path has unknown extension. Try "; 
            foreach ($extension in ($xlFileFormats.Keys | sort)) { 
                $msg += "$extension "; 
            } 
            Throw "$msg"; 
        } 
        # get full path 
        # 
        if (-not [io.path]::IsPathRooted($Path)) { 
            $fswd = $psCmdlet.CurrentProviderLocation("FileSystem"); 
            $Path = Join-Path -Path $fswd -ChildPath $Path; 
        } 
        $Path = [io.path]::GetFullPath($Path); 
        $obj = New-Object System.Collections.ArrayList; 
    } 
 
    Process 
    { 
        if (-not $shouldProcess) { return; } 
         $InputObject | ForEach-Object{ $obj.Add($_) | Out-Null; } 
    } 
    End 
    { 
        if (-not $shouldProcess) { return; } 
        $xl = New-Object -ComObject Excel.Application; 
        $xl.DisplayAlerts = $false; 
        $xl.Visible = $false; 
        # create temporary .csv file from all $InputObject 
        # 
        $csvTemp = GetTempFileName(".csv"); 
        $obj | Export-Csv -Path $csvTemp -Force -NoType:$NoTypeInformation -Confirm:$false; 
        # create a temporary excel file from the temporary .csv file 
        # 
        $xlsTemp = GetTempFileName($ext); 
        $wb = $xl.Workbooks.Add($csvTemp); 
        $ws = $wb.Worksheets.Item(1); 
        $ws.Name = $Worksheet; 
        $wb.SaveAs($xlsTemp, $xlFileFormats[$ext]); 
        $xlsTempSaved = $?; 
        $wb.Close(); 
        Remove-Variable -Name ('ws', 'wb') -Confirm:$false; 
        if ($xlsTempSaved) { 
            # decide how to export based on switches and $Path 
            # 
            $fileExist = Test-Path $Path; 
            $createFile = -not $fileExist; 
            $appendFile = $fileExist -and $Append; 
            $clobberFile = $fileExist -and (-not $appendFile) -and (-not $NoClobber); 
            $needNewFile = $fileExist -and (-not $appendFile) -and $NoClobber; 
         
            if ($appendFile) { 
                $wbDst = $xl.Workbooks.Open($Path); 
                $wbSrc = $xl.Workbooks.Open($xlsTemp); 
                $wsDst = $wbDst.Worksheets.Item($wbDst.Worksheets.Count); 
                $wsSrc = $wbSrc.Worksheets.Item(1); 
                $wsSrc.Name = $Worksheet; 
                $wsSrc.Copy($wsDst); 
                $wsDst.Move($wbDst.Worksheets.Item($wbDst.Worksheets.Count-1)); 
                $wbDst.Worksheets.Item(1).Select(); 
                $wbSrc.Close($false); 
                $wbDst.Close($true); 
                Remove-Variable -Name ('wsSrc', 'wbSrc') -Confirm:$false; 
                Remove-Variable -Name ('wsDst', 'wbDst') -Confirm:$false; 
            } elseif ($createFile -or $clobberFile) { 
                Copy-Item $xlsTemp -Destination $Path -Force -Confirm:$false; 
            } elseif ($needNewFile) { 
                Write-Error "The file '$Path' already exists." -Category ResourceExists; 
            } else { 
                Write-Error "Something was wrong with my logic."; 
            } 
        } 
        $xl.Quit(); 
        Remove-Variable -name xl -Confirm:$false; 
        Remove-Item $xlsTemp -Confirm:$false -Verbose:$VerbosePreference; 
        Remove-Item $csvTemp -Confirm:$false -Verbose:$VerbosePreference; 
        [gc]::Collect(); 
    } 
} 
### Ends Excel Function ###

### Logic for Report ###
### Variables to create Temp CSV before Separating out by Creditor/ Port Data ###
$remitdata = Read-Host -Prompt "'Folder with Remit Data with last \'" 
$costdata =  Read-Host -Prompt "'Folder with Cost Data with last \'" 
### hard Coded locations ###
$Lookup = "G:\Word\Automation\LookupFiles\AndrewsLookup.csv"
$tempRemit = "G:\Word\Automation\LookupFiles\AndrewsRemit.csv"
$tempCost = "G:\Word\Automation\LookupFiles\AndrewsCost.csv"
$cost = "G:\Word\Automation\LookupFiles\combinedAndrewCost.txt"
$remit = "G:\Word\Automation\LookupFiles\combinedAndrewremit.txt"
##############################################################
$CombinedR = Read-Host -Prompt "'Final File Location and Name for Remit Data end with .xlsx'" 
$CombinedC = Read-Host -Prompt "'Final File Location and Name for Cost Data end with .xlsx'" 
$match = "30"
$directpay = 'PAY DIRECT'
#################################################################################

Get-ChildItem -Path $costdata -Filter "*.CST" | Get-Content | Add-Content -Path $cost
Get-ChildItem -Path $remitdata -Filter "*.RMT" | Get-Content | Add-Content -Path $remit

# All Remit
Get-Content $remit | % {if ($_.Substring(0,2) -eq $match -and $_.Substring(328,10) -ne $directpay){$_}}|
   select -Property @{name='Resurgence FileNo';expression={$_.Substring(32,8)}}, 
                    @{name='Autovest FileNo';expression={$_.Substring(2,6)}},
                    @{name='Transaction Date';expression={$_.Substring(73,2)+'/'+$_.Substring(75,2)+'/'+$_.Substring(69,4)}},
                    @{name='Transaction Description';expression={$_.Substring(328,10)}},
                    @{name='Current Balance';expression={$_.Substring(219,12)}},
                    @{name='Payment Amt';expression={$_.Substring(81,10)}},
                    @{name='Cost Ret';expression={$_.Substring(124,10)}},
                    @{name='Net Amt';expression={$_.Substring(96,10)}},
                    @{name='Our Fee';expression={$_.Substring(140,8)}},
                    @{name='Check To Client';expression={$_.Substring(112,8)}} |
                     Export-CSV -NoTypeInformation -path $tempRemit

# All Cost
Get-Content $cost | % {if ($_.Substring(0,2) -eq $match) {$_}}|
select -Property @{name='Resurgence FileNo';expression={$_.Substring(32,8)}}, 
                 @{name='Autovest FileNo';expression={$_.Substring(2,6)}},
          @{name='Transaction Date';expression={$_.Substring(73,2)+'/'+$_.Substring(75,2)+'/'+$_.Substring(69,4)}},
                 @{name='Cost Description';expression={$_.Substring(328,22)}},
                 @{name='Cost Amt';expression={$_.Substring(466,8)}} |
                 Export-CSV -NoTypeInformation -path $tempCost

### Logic to Add Additional FAQs from lookup file ###
### Variables for this Step ####
$OGR = Import-CSV $tempRemit
$OGC = Import-CSV $tempCost
$perv = Import-CSV $Lookup

$UserOutputAutoR = @()
$UserOutputAutoC = @()
$UserOutputAccR = @()
$UserOutputAccC = @()
$UserOutputTopR = @()
$UserOutputTopC = @()
################################

### Remit File Format ###
foreach($or in $OGR)
{
    foreach($p in $perv)
    {
    if($or.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'AUTOVEST LLC')
      {$UserOutputAutoR += New-Object PsObject | Add-Member -Name Date -value $or.'Transaction Date' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_FileNo -value $p.fileno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Your_FileNo -value $p.forw_refno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Creditor -value $p.plaintiff_1 -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Trans_Desc -value $or.'Transaction Description' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Current_Bal -value $or.'Current Balance' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Gross_Amt_Coll -value $or.'Payment Amt' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Cost_Ret -value $or.'Cost Ret' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Net_Amt -value $or.'Net Amt' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_Fee -value $or.'Our Fee' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Check_to_Client -value $or.'Check To Client' -MemberType NoteProperty -PassThru
                                            
      }
      elseif($or.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'AUTOMOTIVE CREDIT CORPORATION')
      {$UserOutputAccR += New-Object PsObject | Add-Member -Name Date -value $or.'Transaction Date' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_FileNo -value $p.fileno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Your_FileNo -value $p.forw_refno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Creditor -value $p.plaintiff_1 -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Trans_Desc -value $or.'Transaction Description' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Current_Bal -value $or.'Current Balance' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Gross_Amt_Coll -value $or.'Payment Amt' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Cost_Ret -value $or.'Cost Ret' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Net_Amt -value $or.'Net Amt' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_Fee -value $or.'Our Fee' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Check_to_Client -value $or.'Check To Client' -MemberType NoteProperty -PassThru
                                            
      }
            elseif($or.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'TOP FINANCE COMPANY INC')
      {$UserOutputTopR += New-Object PsObject | Add-Member -Name Date -value $or.'Transaction Date' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_FileNo -value $p.fileno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Your_FileNo -value $p.forw_refno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Creditor -value $p.plaintiff_1 -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Trans_Desc -value $or.'Transaction Description' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Current_Bal -value $or.'Current Balance' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Gross_Amt_Coll -value $or.'Payment Amt' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Cost_Ret -value $or.'Cost Ret' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Net_Amt -value $or.'Net Amt' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_Fee -value $or.'Our Fee' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Check_to_Client -value $or.'Check To Client' -MemberType NoteProperty -PassThru
                                            
      }                                             
                                             
    }
}

$UserOutputAutoR | Export-XLS -NoTypeInformation -path $CombinedR -Worksheet "Autovest Remit" -Append
$UserOutputAccR | Export-XLS -NoTypeInformation -path $CombinedR -Worksheet "ACC Remit" -Append
$UserOutputTopR | Export-XLS -NoTypeInformation -path $CombinedR -Worksheet "TopFin Remit" -Append

### Cost File Format ### Only Thing left is getting cost Value Correct
foreach($oc in $OGC)
{
    foreach($p in $perv)
    {
    if($oc.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'AUTOVEST LLC')
      {$UserOutputAutoC += New-Object PsObject |Add-Member -Name Date -value $oc.'Transaction Date' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Product -value $p.field -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_FileNo -value $p.fileno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Creditor -value $p.plaintiff_1 -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Your_FileNo -value $p.forw_refno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Cost_Desc -value $oc.'Cost Description' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Amount -value $oc.'Cost Amt' -MemberType NoteProperty -PassThru
      }
    elseif($oc.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'AUTOMOTIVE CREDIT CORPORATION')
      {$UserOutputAccC += New-Object PsObject |Add-Member -Name Date -value $oc.'Transaction Date' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Product -value $p.field -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_FileNo -value $p.fileno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Creditor -value $p.plaintiff_1 -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Your_FileNo -value $p.forw_refno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Cost_Desc -value $oc.'Cost Description' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Amount -value $oc.'Cost Amt' -MemberType NoteProperty -PassThru
      }

    elseif($oc.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'TOP FINANCE COMPANY INC')
      {$UserOutputTopC += New-Object PsObject |Add-Member -Name Date -value $oc.'Transaction Date' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Product -value $p.field -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Our_FileNo -value $p.fileno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Creditor -value $p.plaintiff_1 -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Your_FileNo -value $p.forw_refno -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Cost_Desc -value $oc.'Cost Description' -MemberType NoteProperty -PassThru |
                                            Add-Member -Name Amount -value $oc.'Cost Amt' -MemberType NoteProperty -PassThru
      }      
                                                   
    }
}

$UserOutputAutoC | Export-XLS -NoTypeInformation -path $CombinedC -Worksheet "Autovest Cost" -Append
$UserOutputAccC | Export-XLS -NoTypeInformation -path $CombinedC -Worksheet "ACC Cost" -Append
$UserOutputTopc | Export-XLS -NoTypeInformation -path $CombinedC -Worksheet "TopFin Cost" -Append

### Removes Temp pre-sorted Files and Combined Files
Remove-Item $tempRemit
Remove-Item $tempCost
Remove-Item $cost
Remove-Item $remit

August 2, 2018 at 8:23 pm

In order to do progress, you need to be able to perform arithmetic. If you take this as an example, I created mock objects for OGR and perv, this is the same thing that Import-CSV does. You'll need to change your standard foreach ($x in $y) to a for loop.
You are performing two basic math operations:

  1. Array Index. In a standard for loop, you are just doing $x.Property. In the for loop, you will reference objects by index, like $ogr[0].Property. Count operations start at 1, indexes start at 0, so you need to subtract 1 from the count.
  2. Percentages and current item in loop. Since count starts at 1 and our loop is using index starting at 0, we need to add 1 if we are doing count math.

Try to pick apart this example:

$OGR = @()
$OGR += [pscustomobject]@{Name="Bill";Age=34;}
$OGR += [pscustomobject]@{Name="Sam";Age=22;}
$OGR += [pscustomobject]@{Name="Sally";Age=49;}


$perv = @()
$perv += [pscustomobject]@{Task=1}
$perv += [pscustomobject]@{Task=2}
$perv += [pscustomobject]@{Task=3}
$perv += [pscustomobject]@{Task=4}
$perv += [pscustomobject]@{Task=5}


for($o=0;$o -le ($OGR.Count -1);$o++) {

    $outerParams = @{
        Activity         = "Doing OGR stuff for {0}" -f $OGR[$o].Name
        Status           = 'Processing OGR {0} of {1}' -f ($o + 1), $OGR.Count
        PercentComplete  = ($o+1)/$OGR.count*100
        CurrentOperation = 'OuterLoop'
    }

    Write-Progress @outerParams
    "Doing OGR stuff for {0}" -f $OGR[$o].Name
    
    for($p=0;$p -le ($perv.Count -1);$p++) {
        $innerParams = @{
            Id               = 1
            Activity         = "Performing task {0} in perv" -f $perv[$p].Task
            Status           = 'Processing perv {0} of {1}' -f ($p + 1), $perv.Count
            PercentComplete  = ($p+1)/$perv.count*100
            CurrentOperation = 'InnerLoop'
        }
        Write-Progress @innerParams
        "Performing task {0} in perv" -f $perv[$p].Task
        Start-Sleep -Seconds 1
        
    }

    Start-Sleep -Seconds 2
}

Additionally, you can simplify the loops a bit and remove all of the Add-Member stuff. The loops are creating 3 separate objects. Another way would be to add another property to filter on and generate a single output:

$results = foreach($or in $OGR) {
    foreach($p in $perv) {
        if($or.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'AUTOVEST LLC') {
            $recordType = 'AutoR'
        }
        elseif($or.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'AUTOMOTIVE CREDIT CORPORATION') {
            $recordType = 'AccR'
        }
        elseif($or.'Resurgence FileNo' -eq $p.fileno -and $p.plaintiff_1 -eq 'TOP FINANCE COMPANY INC') {
           $recordType = 'TopR'                                 
        }
        else {
            $recordType = 'UNKNOWN'
        }                           
       
        New-Object -TypeName PSObject -Property @{
            Date            = $or.'Transaction Date'
            Our_FileNo      = $p.fileno
            Your_FileNo     = $p.forw_refno
            Creditor        = $p.plaintiff_1
            Trans_Desc      = $or.'Transaction Description'
            Current_Bal     = $or.'Current Balance'
            Gross_Amt_Coll  = $or.'Payment Amt'
            Cost_Ret        = $or.'Cost Ret'
            Net_Amt         = $or.'Net Amt'
            Our_Fee         = $or.'Our Fee'
            Check_to_Client = $or.'Check To Client'
            Type            = $recordType
        }
    }
}

$results | 
Where{$_.RecordType -eq 'AutoR'} | 
Select * -ExcludeProperty RecordType | 
Export-XLS -NoTypeInformation -path $CombinedR -Worksheet "Autovest Remit" -Append

August 3, 2018 at 3:52 pm

Hey, thanks for the reply. I got the progress bar working now for each Foreach in my code. Now, you're the 2nd person that has suggested getting rid of my add-member stuff...

So to do that, i just everywhere in my code the Add-Member – Name stuff with how you have it formatted in your suggestion?