How to filter select certain column from CSV and save to new csv in Powershell?

Question

Hi I need to read a csv file in Powershell and select certain columns and save it into new file.

here is my code

# reading the file
$import = Import-Csv -Path ($file_dir+"Myfile.csv")
# listing out all columns in csv
$import_columnname = $import | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 
'Name'
# grab all columns that I need, using pattern matching
$import_columnneeded = $import_columnname | select-string -Pattern 'stringIdonotNeed|stringIdonotNeed2' -NotMatch
# select the one i need
$import | select -ExpandProperty $import_columnneeded | Export-CSV '.\whereever'

$import_columnneeded is a array, it does not work with | select -ExpandProperty, select -ExpandProperty only works strings. So what can I do to select columns that I need dynamically?

Why not using the -Property and/or the ExcludeProperty parameters of the Select-Object cmdlet? — iRon
– iRon, Commented Dec 15, 2020 at 20:54
-ExpandProperty returns an array of values for the expanded property. It seems you want to output multiple columns of values. How would you join/delimit the multiple columns of values? If all of this property expansion is required, you may reconsider the schema of the original CSV file. — AdminOfThings
– AdminOfThings, Commented Dec 15, 2020 at 21:48

Theo · Accepted Answer · 2020-12-16 15:28:10Z

If I look at your code, you want to create a new csv file from an original while skipping some of the columns.

For example, if this is the data in your CSV

Username  Identifier Password Firstname Lastname Department  Location  
--------  ---------- -------- --------- -------- ----------  --------  
booker12  9012       12se74   Rachel    Booker   Sales       Manchester
grey07    2070       04ap67   Laura     Grey     Depot       London    
johnson81 4081       30no86   Craig     Johnson  Depot       London    
jenkins46 9346       14ju73   Mary      Jenkins  Engineering Manchester
smith79   5079       09ja61   Jamie     Smith    Engineering Manchester

You can use this

# a regex string that contains all headers you don't want combined with regex OR '|'
$notNeeded = 'Password|Location|Identifier'

$file_dir = 'D:\Test'
$fileIn   = Join-Path -Path $file_dir -ChildPath "MyFile.csv"
$fileOut  = Join-Path -Path $file_dir -ChildPath "MyNewFile.csv"

# import the original file
$import = Import-Csv -Path $fileIn

# get an array of column headers excluding the ones you don't want
$columns = $import[0].PSObject.Properties.Name | Where-Object { $_ -notmatch $notNeeded }

# output a new csv file with all remaining headers
$import | Select-Object $columns | Export-Csv -Path $fileOut -NoTypeInformation

To produce:

Username  Firstname Lastname Department 
--------  --------- -------- ---------- 
booker12  Rachel    Booker   Sales      
grey07    Laura     Grey     Depot      
johnson81 Craig     Johnson  Depot      
jenkins46 Mary      Jenkins  Engineering
smith79   Jamie     Smith    Engineering

Collectives™ on Stack Overflow

How to filter select certain column from CSV and save to new csv in Powershell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest