Add missing comma to a CSV with Powershell

Question

I have a CSV which I process using powershell where occasionally one or more of the rows will be missing one of the comma delimiters. It will always have 3 columns and the 2nd column is optional.

Ex.

Col1,Col2,Col3
SomeCol1Val,,SomeCol3Val
AnotherCol1Val,AnotherCol3Val

In the above example I need to add another comma to Row #2

I've been able to determine which row needs to be updated and how change the value, but I'm not sure how overwrite that specific row in the file.

$csvFile = Get-Content "C:\MyFile.csv"

foreach($row in $csvFile) {

$cnt = ($row.ToCharArray() -eq ',').count
if ($cnt -eq 1) {
    $row = $row -replace ",",",,"
}
}

Thanks

You’ve updated it with your $row setting line. Now just output $CSV back to the file — Doug Maurer, Nov 23 '20 at 18:16

mklement0 · Accepted Answer · 2020-11-23T20:56:56.920

As Doug Maurer points out, all that is missing from your code is to write the updated $row values back to your input file, using the Set-Content cmdlet.

However, I suggest a different, faster approach, using a switch statement with the -File option and a single -replace operation based on a regex.

$csvFile = 'C:\MyFile.csv'

$newContent = 
  switch -File $csvFile {
    default { $_ -replace '^([^,]+),([^,]+)$', '$1,,$2' }
  }

Set-Content $csvFile -Value $newContent -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 that you may have to use the -Encoding parameter to specify the desired character encoding, which in Windows PowerShell is the active ANSI code page and in PowerShell [Core] v6+ BOM-less UTF-8.

If you wanted to stick with your original approach:

$csvFile = 'C:\MyFile.csv'

$newContent = 
  foreach ($row in Get-Content $csvFile) {
    if (($row.ToCharArray() -eq ',').Count -eq 1) {
      $row -replace ',', ',,'
    } else {
      $row
    }
  }

Set-Content $csvFile -Value $newContent -WhatIf

Note that both approaches collect all (modified) lines in memory as a whole, so as to speed up the operation and also to allow writing back to the input file.

However, it is possible to stream the output, to a different file - i.e. to write the output file line by line - by enclosing the switch statement in & { ... } and piping that to Set-Content. With your Get-Content approach you'd have to use
Get-Content ... | ForEach-Object { ... } | Set-Content instead.

Add missing comma to a CSV with Powershell

1 Answers1