powershell - Can Export-Csv be given a max no. of rows to write before continuing with a new CSV? (split file) -

this script audits share , creates csv detailing file names found , file extension information - working well. problem csv has more rows excel able open. there way split file - let's after x no. of rows create new csv file?

# changes powershell prompt directory script being executed  function get-scriptdirectory {     $invocation = (get-variable myinvocation -scope 1).value     split-path $invocation.mycommand.path }  $isescriptpath = split-path -parent $psise.currentfile.fullpath -ea silentlycontinue  if ($isescriptpath -eq $null) {     cd $(get-scriptdirectory -ea silentlycontinue) } else {     cd $isescriptpath }  # checks logs directory - if directory not exist creates $logs = test-path "$pwd\logs" if (-not $logs) {     md "$pwd\logs" }  ############# main script ################  $servers = get-content .\servers.txt          # servers.txt should in script location containing list of servers audited $share = "e$"                                 # change audit location   foreach ($server in $servers) {     $now = get-date -format _hhmm_dd-mm-yyyy      # user config     $report = ("fileextaudit_"+ $server + "_" + $share + "$now.csv") # name of file write out to.     $scanfrom = "\\$server\$share" # script audit location , below (if has permissions..)      gci $scanfrom -recurse | ? {$_.psiscontainer -eq $false} |         select-object directoryname, name, extension, length, lastaccesstime,             @{l="hsm";e={if( $_.attributes -match "compressed"){"true"} else {"false"} }},             @{l="acl";e={[string]((get-acl $_.fullname).owner)}} |         export-csv "$pwd\logs\$report" -notypeinformation  } 

export-csv doesn't support splitting input several files. have implement logic yourself.

appending in loop arguably not elegant way go this, work without major changes in code while still avoiding having collect results in variable:

$maxrecords = 500  # maximum number of records per csv $now = get-date  foreach ($server in $servers) {     get-childitem "\\$server\$share" -recurse |         where-object { -not $_.psiscontainer } |         select-object directoryname, name, extension, length, lastaccesstime,             @{l="hsm";e={ if ( $_.attributes -match "compressed") {$true} else {$false} }},             @{l="acl";e={ (get-acl $_.fullname).owner }} |         foreach-object -begin {$i = 0} -process {             $index  = [int][math]::floor([int]$i/[int]$maxrecords)             $report = 'fileextaudit_{0}_{1}_{2:hhmm_dd-mm-yyyy}_{3:d3}.csv' -f $server, $share, $now, $index             $_ | export-csv "$pwd\logs\$report" -notype -append             $i++         } } 

using streamwriter better option performance pov (not faster in , out of itself, avoid repeatedly closing , re-opening output file). however, requires more elaborate handling, , need build csv lines yourself.

$maxrecords = 500  # maximum number of records per csv $now = get-date  foreach ($server in $servers) {     get-childitem "\\$server\$share" -recurse |         where-object { $_.psiscontainer -eq $false } |         select-object directoryname, name, extension, length, lastaccesstime,             @{l="hsm";e={ if ( $_.attributes -match "compressed") {$true} else {$false} }},             @{l="acl";e={ (get-acl $_.fullname).owner }} |         foreach-object -begin {$i = 0} -process {             if ($i % $maxrecords -eq 0) {                 if ($writer) {                     $writer.close()                     $writer.dispose()                 }                 $index  = [int][math]::floor([int]$i/[int]$maxrecords)                 $report = 'fileextaudit_{0}_{1}_{2:hhmm_dd-mm-yyyy}_{3:d3}.csv' -f $server, $share, $now, $index                 $writer = [io.streamwriter]"$pwd\logs\$report"                 $writer.writeline('"directoryname","name","extension","length","lastaccesstime","hsm","acl"')             }             $writer.writeline(('"{0}","{1}","{2}","{3}","{4}","{5}","{6}"' -f $_.directoryname, $_.name, $_.extension, $_.length, $_.lastaccesstime, $_.hsm, $_.acl))             $i++         }      if ($writer) {         $writer.close()         $writer.dispose()     } }