问使用Powershell删除CSV文件中的附加逗号

EN

$csvArray = new-object System.Collections.Generic.List[string]
#Import the file
$text = (gc "C:\tmp\testdata.txt") -replace ",{2,}",","
$arrayEnd = $text.count -1
$text[1..$arrayEnd] | %{
    If ($_ -notmatch "^(,)"){
        $department = $_ -replace ","
    }
    Else {
        $csvArray.add($department + $_)
    }
}
$csvArray.Insert(0,$text[0])
$csvArray | set-content 'C:\tmp\my.csv' 

& { 
  $first = $true
  switch -Wildcard -File in.csv { # Loop over all lines in file in.csv
    ',*' { # intra-department line
       # Prepend the department name, eliminate empty fields and output.
      $dept + (($_ -split ',' -ne '') -join ',')
    }
    default {
      if ($first) { # header line
        # Eliminate empty fields and output.
        ($_ -split ',' -ne '') -join ',' 
        $first = $false
      } 
      else { # department-only line
        $dept = ($_ -split ',')[0] # save department name
      }
    }
  }
} | Set-Content -Encoding utf8 out.csv

- If you don't mind the complexity of a [regex](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Regular_Expressions), you can perform the above more simply with a single [`-replace`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Comparison_Operators#replacement-operator) operation, as shown in [Toni's helpful answer](https://stackoverflow.com/a/73573678/45375).

- As a language statement, `switch` cannot be used _directly_ in a pipeline.
- This limitation can be overcome by enclosing it in a script block (`{ ... }`) invoked with `&`, which enables the usual, memory-friendly _streaming_ behavior in the pipeline; that is, the lines are processed one by one, as are the modified output lines relayed to `Set-Content`, so that the input file needn't be read into memory _as a whole_.

$Csv = @'
Department,,,,,,First Name,,,,Last Name,,,,,,,School Year,Enrolment Status
Psychology ,,,,,,,,,,,,,,,,,,,,,,, (Remove this line)
,,,,,,Jane,,,,Doe,,,,,,,2022,Enrolled
,,,,,,Jeff,,,,Dane,,,,,,,2019,Enrolled
,,,,,,Tate,,,,Anderson,,,,,,,2019,Not Enrolled
,,,,,,Daphne,,,,Miller,,,,,,,2021,Enrolled
,,,,,,Cora,,,,Dame,,,,,,,2022,Enrolled
Computer Science ,,,,,,,,,,,,,,,,,,,,,,, (Remove this line)
,,,,,,Dora,,,,Explorer,,,,,,,2022,Not Enrolled
,,,,,,Peppa,,,,Diggs,,,,,,,2020,Enrolled
,,,,,,Conrad,,,,Strat,,,,,,,2020,Enrolled
,,,,,,Kat,Noir,,,,2019,,,,,,,Enrolled
,,,,,,Lance,,,,Bug,2018,,,,,,,Enrolled
'@

$List = ConvertFrom-Csv $Csv -Header @(1..20) # |Import-Csv .\Your.Csv -Header @(1..20)
$Columns = $List[0].PSObject.Properties.Where{ $_.Value -and $_.Value -ne 'Department' }.Name
$List |Select-Object -Property $Columns |Where-Object { $_.$($Columns[0]) } |
    ConvertTo-Csv -UseQuote Never |Select-Object -Skip 1 # |Set-Content -Encoding utf8 out.csv

First Name,Last Name,School Year,Enrolment Status
Jane,Doe,2022,Enrolled
Jeff,Dane,2019,Enrolled
Tate,Anderson,2019,Not Enrolled
Daphne,Miller,2021,Enrolled
Cora,Dame,2022,Enrolled
Dora,Explorer,2022,Not Enrolled
Peppa,Diggs,2020,Enrolled
Conrad,Strat,2020,Enrolled
Kat,,,Enrolled
Lance,Bug,,Enrolled