When using Select-Object and further writing to the .csv file using Export-CSV, can I not specify the column name?

user_12537896 0
Viewed 117

Problem: 1) "$FileName" in string, 2) recording 7000+ files took over an hour. How do I make the recording faster?

Get-ChildItem "C:\*.csv" | ForEach-Object {
$CSV = Import-CSV -Path $_.FullName
    $FileName = $_.Name

    $CSV | Select-Object *,@{N='Filename';E={$FileName}} | Export-CSV $_.FullName
}
csv powershell performance
1 Answers

The only way to speed this up with PowerShell code alone is to resort to plain-text processing:

Get-ChildItem C:\*.csv | ForEach-Object {  
  $fileName = $_.Name
  $toAppend = ',"Filename"'
  $isHeaderRow = $true
  $lines = switch -File $_.FullName {
    default {
      $_ + $toAppend  # append to the line and output
      if ($isHeaderRow) {
        $isHeaderRow = $false
        $toAppend = ',"{0}"' -f $fileName
      }
    }
  }
  # Write the updated lines back to the file.
  # Adjust the -Encoding argument as needed.
  Set-Content $_.FullName -Value $lines -Encoding utf8 -WhatIf 
}

Note: The -WhatIf common parameter in the command above previews the operation. Remove -WhatIf once you're sure the operation will do what you want.

Note:

  • The switch statement with the -File parameter is an efficient way to process a text file line by line.

  • Note that the array of updated lines, $lines, is passed to Set-Content via the -Value parameter, which is much faster than using the pipeline ($lines | Set-Content ...)

  • The code assumes that a single CSV file can fit into memory as a whole; if that's not possible, you'd have to switch to a pipeline-based solution, which would slow things down:
    & { switch ... } | Set-Content ...

Variation, where all input files have the same column structure and are to be merged into a single output file:

$outFile = './out.csv' # single output file
$null = New-Item $outFile # initialize it (create it empty)

$firstFile = $true
Get-ChildItem C:\*.csv | ForEach-Object {  
  $fileName = $_.Name
  $isHeaderRow = $true
  $lines = switch -File $_.FullName {
    default {
      if ($isHeaderRow) {
        $isHeaderRow = $false
        if ($firstFile) {
          $firstFile = $false
          $_ + ',"Filename"' # output single header row
        }        
        # Construct string to append to this file's data rows.
        $toAppend = ',"{0}"' -f $fileName
      } else { # data row
        $_ + $toAppend # append to the line and output
      }
    }
  }
  # Append this file's updated lines to the output file.
  # Adjust the -Encoding argument as needed.
  Add-Content $outFile -Value $lines -Encoding utf8 -WhatIf 
}
user_453750
Related