CODEWITHSUNDEEP
powershell
user11708158
user11708158

Reputation:

How to remove blank columns and reorder the columns in desired order in powershell

I got a excel sheet ,which got values from server . I want to remove columns which have no value..I did that but resulted excel sheet column names are in alphabetical order.But I want them in desired order. If use Select-object to get desired order , it will give the removed columns again.Below is the code i used to remove blank columns. 

$x = Import-Csv YourFile.csv
$f = $x[0] | Get-Member -MemberType NoteProperty | Select name
$f | Add-Member -Name count -Type NoteProperty -Value 0
$f | %{
  $n = $_.Name
  $_.Count = @($x | Select $n -ExpandProperty $n | ? {$_ -ne ''}).count
}
$f = @($f | ? {$_.count -gt 0} | Select Name -expandproperty Name)

$x | Select $f | Export-Csv NewFile.csv -NoTypeInformation

Upvotes: 2

Views: 567

Answers (1)

mklement0
mklement0

Reputation: 437109

As Axel Andersen points out, Get-Member invariably returns an object's members alphabetically sorted.

Try the following approach, which uses .psobject.Properties.Name to get the column (property) names in the original order:

$rows = Import-Csv YourFile.csv
$columnNames = $rows[0].psobject.Properties.Name

$nonEmptyColumnNames = $columnNames.Where({ (@($rows.$_) -ne '').Count -gt 0 })

$rows | Select-Object $nonEmptyColumnNames | Export-Csv NewFile.csv -NoTypeInformation

Upvotes: 1

Related Questions