Deleting columns from CSV using PowerShell

Question

I have a CSV file that has duplicate column headers, so I can't use Import-Csv to do the work. The header names are dynamic. I need to get the third column, the fourth column, and every fourth column after that(ex: starting from 0 columns 2, 3, 7, 11, 15...).

The reason I have duplicate column names is that header 3 needed the same name as header 0, in groups of four. 0 > 3, 4 > 7, 8 > 11...

I used get-Content because I couldn't figure out how to make this work with Import-Csv. I had to use Import-Csv to get the number of columns, which I couldn't figure out with Get-Content.

#Rename every fourth column
$file = "C:\Scripts\File.csv"
$data = get-content $file
$step = 4
$csv = Import-Csv "C:\Scripts\File.csv"
$headers = $data | select -first 1
$count = $csv[0].PSObject.Properties | select -Expand Name

for ($i = 0; $i -lt $count.count; $i += $step)
{    
    $headers = $headers -split ","
    $headers[($i + 3)] = $headers[$i]
    $headers[($i + 2)] = "timestamp"
    $headers = $headers -join ","
    $data[0] = $headers
    $data | Set-Content "C:\Scripts\File.csv"
}

I can reuse the variable $count if needed (for $count.count), so I don't have to use Import-Csv again. I'm having trouble figuring out how to get just the columns I need based on number and not header name.

This worked great for getting the third column (2nd if starting from 0), but I'm not sure how to get every fourth after that (3rd if starting from 0)

type "C:\Scripts\File.csv" | % { $_.Split(",") | select -skip 2 -first 1 }

Screenshots below. Keep in mind I do not know the headers names of every fourth column as they could be anything, I only know which column number the data is in (every fourth column).

Before After

Is there some reason you can't just specify your own column headers manually with the -Header option of Import-Csv? — Bacon Bits

mjolinor mjolinor · Accepted Answer · 2014-12-10T14:15:28

I'd re-think that whole process and start with this:

$file = "C:\Scripts\File.csv"
$HeaderCount = ((gc sentlog.csv -TotalCount 1).split(',')).count -1
$CSV = import-csv $file -Header (0..$HeaderCount)

Now you can treat those column headings like array indexes to extract out the columns you want.

Use Select -Skip 1 to strip off the original header row. You can rewrite the property names for export using calculated properties or just create new objects, using property names extracted from the original header row.

OK, based on the posted data, try this:

$file = "C:\Scripts\File.csv"
$OutputFile = "C:\Scripts\OutputFile.csv"

$HeaderCount = ((Get-Content $file -TotalCount 1).split(',')).count -1
$CSV = import-csv $file -Header (0..$HeaderCount)

$SelectedColumns = @(2) + ( (0..$HeaderCount) |? { ($_ % 4) -eq 3 } ) -as [string[]]

 $CSV | 
 select $SelectedColumns |
 ConvertTo-CSV -NoTypeInformation |
 Select -Skip 1 |
 Set-Content $OutputFile

Deleting columns from CSV using PowerShell

1 Answers