I have a long process that updates thousands of records originating from the Import-Csv cmdlet.
During this long process I would like to be updated as to the number of records the Loop is currently at. I want some code that would reside within the ForEach-Object to provide incremental output during the processing of the data to tell the user some sort of status as to how many records have been processed rather than waiting the unknown amount of time only to see the output after the script is finished executing.
The only option I've found is to use Write-Progress however I have not been able to get it to work using a ForEach-Object. The only way I've got this to work is using the very simple example I found that demonstrates this using a simple for loop. Really in the end I just want to send a blip to the Console just letting me know how many records have been processed (perhaps after every 1000 records or so). I don't need anything fancy like Write-Progress but would still be curious as to why my code does not work.
for ($i = 1; $i -le 100; $i++ ) {
Write-Progress -Activity "Search in Progress" -Status "$i% Complete:" -PercentComplete $i
Start-Sleep -Milliseconds 150
}
I don't understand the behavior I'm seeing when I run the below. The output slowly crawls onto the Console and when it becomes complete it ends at 45% or some percentage that is NOT 100%. - Fixed this piece using suggestion in comments from Mathias and mclayton.
The remaining issues after applying those much needed suggestions is that if there is nothing to process the script runs so fast that it outputs a percentage that is very low and it never gets to the full 100%. I applied [Math]::Ceiling( ($i/$ProcessData.count) * 100 ) which fixed the issue of the script showing 99.3355888% as it never truly made it to a full 100% inside the loop.
The other issue is still the strange crawling output once the script is started.
#Declare File Paths
$SourceFile = "C:\DataTemp\SomeTestData.tsv"
$ProcessData = Import-Csv $SourceFile -Delimiter "`t"
Write-Host $ProcessData.count
$ProcessData | ForEach-Object -Begin {
# Set the $i counter variable to zero.
$i = 0
} -Process {
# Increment the $i counter variable which is used to create the progress bar.
$i = $i + 1
# Determine the completion percentage
if ( ($i % 100) -eq 0 ) {
$Completed = [Math]::Ceiling( ($i/$ProcessData.count) * 100 )
Write-Progress -Activity "Processing Records" -Status "$i% Complete:" -PercentComplete $Completed
}
Start-Sleep -Milliseconds 1
#$_.record_id
#$Completed
}
The example I was attempting to replicate above came from this link (example 3): https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/write-progress?view=powershell-7.3 And also when I run Example 3 it almost locked up Visual Studio Code and also populates the Terminal with a weird crawling output, meaning the data slowly populates in the terminal.
_record_number record_id id_testing Notes IntakeComplete
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
111 6 5 "We will not be testing because of covid 19" 1
222 6 5 "We will not be testing because of covid 19" 1
444 6 5 "We will not be testing because of covid 19" 1
333 6 5 "We will not be testing because of covid 19" 1
Below is the actual code I applied the ProgressBar code to. I'm open to any improvements you might be able to provide such as how to better obtain the Count of rows in the file being imported. Mathias suggested I directly Pipe this into the ForEach-Object however I don't know how to get the Total number of rows if I do that.
#Declare File Paths
$SourceFile = "C:\DataTemp\SomeTestData.tsv"
$ExportFile = "C:\DataTemp\SomeTestData_Updated.tsv"
$FormatFile = "C:\DataTemp\SomeTestData.fmt"
$ErrorFile = "C:\DataTemp\SomeTestData_Errors.log"
# Adjust this according to file input size
$progressInterval = 1000 # 1000 meaning 1000 Lines or Records in the tsv/csv
# variable to keep track of the record count
$recordCount = 0
$totalRecords = 0
# kick off the progress activity
$activity = "Processing '$SourceFile'"
Write-Progress -Activity $activity -Status 'Import initiated!' # This can be short lived and not noticed depending on the time it takes to import
#---------- 1 Add Column to the beginning of the CSV/TSV file for the Identity Column. The Export-Csv Commandlet by default also Replaces NewLine_LF (\n) with Cariage Return Line Feed CRLF (\r\n)
# The main purpose of the below is to add a Column at the beginning of the file as a placeholder for the Identity Column in the Staging Table.
# The below will read the Tab Delimited file having EOL Characters as New Lines/Line Feeds \n and turn them into CRLF (Cariage Return, Line Feeds) \r\n.
# It will also remove almost all of the double quotes it finds in the file. The -UseQuotes option is not perfect and so we still have to clean
# up the data Post Import and remove any Leading or Trailing Double Quotes using a SQL Update.
$ProcessData = Import-Csv $SourceFile -Delimiter "`t"
$totalRecords = $ProcessData.count
$ProcessData | Select-Object @{Name='_record_number';Expression={'999'}},* | #Example Adding new column to End Select-Object *,@{Name='column3';Expression={'setvalue'}}
ForEach-Object {
# increment record counter
$recordCount++
# Loop Thru ALL field values and if NOT Null preform a replace to remove TABs and Double Quotes from within each field
foreach ($property in $_.PSObject.Properties)
{
if ( ![string]::IsNullOrEmpty($property.Value) ) {
# Replace Tabs, Double Quotes, Cariage Returns and New Lines within Fields and Trim
$property.Value = $property.Value.Trim() -replace '`t|\"|`r|`n',''
# Scan fields for dates. If a Date is found make sure the Year is greater than 1900. If not update the year to 1900
if ( ( $property.Value -as [datetime] ) -and ( [datetime]$property.Value -lt (Get-Date 1900-01-01) ) ) {
$property.Value = (Get-Date $property.Value -Year 1900).ToString('yyyy-MM-dd')
}
}
}
# check if it's time to update the status message
if ($recordCount % $progressInterval -eq 0) {
Write-Progress -Activity $activity -Status "Records processed so far: $recordCount/$totalRecords"
}
# Check if it's the last record
if ($recordCount -eq $totalRecords) {
Write-Progress -Activity $activity -Status "Total Records: $recordCount. Preparing bcp.exe import"
Start-Sleep -Milliseconds 4000
}
# Output the updated object
$_
} |
# Write-Host $_ # Use Write-Host to view piped object Post Update from the foreach loop for testing and verification
Export-Csv $ExportFile -Delimiter "`t" -UseQuotes Never -NoTypeInformation
# shut down the progress bar
Write-Progress -Activity $activity -Completed
Write-Host "Total number of Records: $recordCount"
#---------- 2 Import Using Format File ---------------
$global:pass = "password"
# Use an Import File to Bulk Import Data
bcp Server.Schema.Table IN $ExportFile `
-f $FormatFile `
-e $ErrorFile `
-S LOCALPC\SQLEXPRESS `
-U sa -P $pass `
-F 2 `
#-L 1000
As mentioned in the comments, it's not actually PowerShell reporting that progress completes at 45% - it's caused by the definition of the status message:
-Status "$i% Complete:"
$i is the record counter, not the calculated percentage.
Replace it with:
-Status "$Completed% Complete:"
... and it'll report the expect number.
I have a long process that updates thousands of records originating from the Import-Csv cmdlet.
Then you probably want to:
Write-Progress on each iterationSince you just want to keep track of the processed record count, there's no need to attempt to report a completion percentage. Simply update the status message for every X records:
# adjust this according to input size
$progressInterval = 5
# variable to keep track of the record count - better named than `$i` if you ask me :)
$recordCount = 0
# kick off the progress activity
$activity = "Processing '$SourceFile'"
Write-Progress -Activity $activity -Status 'Import initiated!'
# don't assign the imported data to a variable - start processing it right away instead!
Import-Csv $SourceFile -Delimiter "`t" |ForEach-Object {
# increment record counter
$recordCount++
# check if it's time to update the status message
if ($recordCount % $progressInterval -eq 0) {
Write-Progress -Activity $activity -Status "Records processed so far: $recordCount"
}
# work on each record $_ here
Start-Sleep -Milliseconds 50
}
# shut down the progress bar
Write-Progress -Activity $activity -Completed