How to return current item back to the pipeline?

user_8397835 0
Viewed 240

Im currently importing csv files and bulk loading to sql table.

using this code

$CSVDataTable = Import-Csv $csvFile | % -begin {$i=0} -process { Write-Progress -activity "Importing file" -currentOperation "Reading line $i" -PercentComplete -1; $i++; return $_ } | Out-DataTable

i am able to show the progress, but I'd like to optimize it and one recommendation Ive found is utilizing StreamReader.

so ive tried the following:

[int]$LinesInFile = 0
$reader = New-Object IO.StreamReader $csvFile
$line = $reader.ReadLine()
while($reader.ReadLine() -ne $null) { $LinesInFile++ }

$CSVDataTable = 0..($LinesInFile-1) | foreach {
    $percent = ($_/$LinesInFile)*100
    Write-Progress -Activity 'Importing from CSV' -Status "$percent % Complete" -CurrentOperation "Importing row # $($_+1)" -PercentComplete $percent;
    return $reader[$_]
} | Import-Csv $csvFile | Out-DataTable

Error (due to return $reader[$_]):

Import-Csv : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its
properties do not match any of the parameters that take pipeline input.
progress-bar powershell
1 Answers

The only thing that makes sense to pipe to Import-Csv are System.IO.FileInfo instances representing CSV files, such as output by Get-ChildItem (which, as an aside, is broken due to a bug in Windows PowerShell, since fixed in PowerShell [Core] v6+).

If you want to report on the progress of an Import-Csv call, put a ForEach-Object command after it, in which you can emit the progress message and then pass Import-Csv's output object ($_) through:

# Count the data rows in the input CSV file.
$rowCount = 0
switch -File $csvFile { default { ++$rowCount } }
--$rowCount # subtract 1 from the line count to account for the header row.

Import-Csv $csvFile | ForEach-Object -Begin { $i = 0 } {

  $percent = '{0:N1}' -f (++$i / $rowCount * 100)
  Write-Progress -Activity 'Importing from CSV' -Status "$percent % Complete" -CurrentOperation "Importing row # $i" -PercentComplete $percent

  $_  # pass the object from Import-Csv through.

} | Out-DataTable

# Hide the progress bar now.
# (Otherwise it would linger until the script as a whole completes.)
Write-Progress '(unused))' -Completed

It's worth noting that per-object Write-Progress calls significantly slow down execution (and counting the number of lines up front has its cost too, as does even just passing objects through via a ForEach-Object call).

A simple way to mitigate the slowdown is to only use Write-Progress for every N objects, such as every 100 objects in the following example:

# Count the data rows in the input CSV file.
$rowCount = 0
switch -File $csvFile { default { ++$rowCount } }
--$rowCount # subtract 1 from the line count to account for the header row.

Import-Csv $csvFile | ForEach-Object -Begin { $i = 0 } {

  if (++$i % 100 -eq 1 -or $i -eq $rowCount) {
    $percent = '{0:N1}' -f ($i / $rowCount * 100)
    Write-Progress -Activity 'Importing from CSV' -Status "$percent % Complete" -CurrentOperation "Importing row # $i" -PercentComplete $percent
  }

  $_  # pass the object from Import-Csv through.

} | Out-DataTable

# Hide the progress bar now.
# (Otherwise it would linger until the script as a whole completes.)
Write-Progress '(unused))' -Completed

Note: If Out-DataTable requires a nontrivial amount of processing time after the last object has been passed to it, you could pad the number of rows for the purpose of the percentage display based on an estimate of that additional time, as a percentage of the true row count.

user_453750
Related