CODEWITHSUNDEEP
powershellcsvautomation
bossangelo
bossangelo

Reputation: 87

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

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?

Upvotes: 1

Views: 2620

Answers (1)

Theo
Theo

Reputation: 61228

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

Upvotes: 2

Related Questions