Remove additional commas in CSV file using Powershell

Question

I have a csv file that I'll like to import to sql but isn't properly formatted. I am not able to format the generated file (excel file) so I'm looking to do this with the CSV file using. I want to remove the extra commas and also replace the department name (,,,,,,) with the correct department as seen in the example below. Thank you in advance.

Example:

Current Format:
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

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

Toni · Accepted Answer · 2022-09-01 17:59:15Z

1

here you go:

$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'

answered Sep 1, 2022 at 17:59

Toni

1,8261 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iRon · Accepted Answer · 2022-09-01 19:04:35Z

Using the Csv cmdlets:

$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

mklement0 · Accepted Answer · 2022-09-01 20:31:38Z

1

Use a switch statement:

& { 
  $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

Note:

$_ -split ',' splits each line into fields by ,, and -ne '' filters out empty fields from the resulting array; applying -join ',' rejoins the nonempty fields with ,, which in effect removes multiple adjacent , and thereby eliminates empty fields.
- If you don't mind the complexity of a regex, you can perform the above more simply with a single -replace operation, as shown in Toni's helpful answer.
Using switch -File is an efficient way to read files line by line and perform conditional processing based on sophisticated matching (as an alternative to -Wildcard you can use -Regex for regex matching, and you can even use script blocks ({ ... } as conditionals).
- 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.
In your case, plain-text processing of your CSV file enabled a simple solution, but in general it is better to parse CSV files into objects whose properties you can work with, using the Import-Csv cmdlet, and, for later re-exporting to a CSV file, Export-Csv,

edited Sep 1, 2022 at 20:31

answered Sep 1, 2022 at 17:59

mklement0

453k68 gold badges729 silver badges989 bronze badges

2 Comments

Kruskal W Over a year ago

this worked, thank you very much for taking the time to explain. Was very helpful.

mklement0 Over a year ago

Glad to hear it, @KruskalCee; my pleasure. Toni's answer is concise and works well, but just to point out the tradeoffs: while this answer processes one line at a time and streams the modified lines to the output file one by one, Toni's answer reads the file in full into an array of lines first, then loops over the lines and builds up a parallel array (list) with the results. For small files this won't matter, but it's something to keep in mind when processing large files.

Collectives™ on Stack Overflow

Remove additional commas in CSV file using Powershell

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related