How to filter CSV File with PowerShell?

Question

I have a CSV file for the backup report in which I have multiple columns. I want to get only those clients who have never been successful with a particular save set.

Sample Input file is

Client Name,Save Set Name,Group,Status
a,All,Group1,Failed
a,SQL,Group2,succeeded
b,SQL,Group1,Failed
c,FS,Group1,Failed
d,DBA,Group1,Failed
e,RDM,Group1,Failed
a,ALL,Group2,succeeded
b,SQL,Group3,succeeded
c,SQL,Group4, Failed

Output

Client Name Save Set Name   Status  Group
a   All Failed  Group1
b   SQL Failed  Group1
c   FS  Failed  Group1
d   DBA Failed  Group1
e   RDM Failed  Group1
c   SQL Failed  Group4

Expected Output

Client Name,Save Set Name,Group,Status
c,FS,Group1,Failed
d,DBA,Group1,Failed
e,RDM,Group1,Failed
c,SQL,Group4, Failed

In my command below, the problem I am facing is that I am getting clients as Failed who gets successful in some other group whereas I want only those clients with save sets who got failed and don't have any success value in status.

Get-Content E:\Report\Daily_Failed.csv | 
    ConvertFrom-Csv | 
        Select-Object -Unique * | 
            Group-Object -Property 'Client Name', 'Save Set Name', 'Group' | 
                Where-Object { 0 -eq ($_.Group | Where-Object Status -eq 'succeeded').Count } | 
                    Select-Object -Expand Group | 
                        Select-Object "Client Name", "Save Set Name", "status", "Group" |
                            Export-Csv -NoTypeInformation E:\Report\Failed_$CurrentDate.csv

Example 2.

Client Name,Save Set Name,Group,Status
gsiecwt2020.web.local,pseudo_saveset,D_CWT_File_System_1,failed
gsiecwt2020.web.local,J:\System,D_CWT_File_System_2,succeeded
gsiecwt2020.web.local,K:\System,D_CWT_File_System_1,succeeded
gsiecwt2020.web.local,K:\System,D_CWT_File_System_3,failed

Desired Output

gsiecwt2020.web.local,pseudo_saveset,D_CWT_File_System_1,failed

It should be matching Client Name, Save Set Name and Status. If the client with same Save Set Name gets succeeded in any other group, then it should be marked as a success, but if the client with particular Save Set Name is failed then it should be marked as Failed.

Does this answer your question? Filter output (where-object) from variable — shadow2020
– shadow2020, Commented Apr 14, 2020 at 15:24

Theo · Accepted Answer · 2020-04-16 10:02:28Z

4

You can do this with a Group-Object and a Where-Object clause.

For demo I'm using a Here-String; in real life you would import the csv from file with

$csv = Import-Csv -Path 'X:\yourInputFile.csv'

Code:

$csv = @"
Client Name,Save Set Name,Group,Status
a,All,Group1,Failed
a,SQL,Group2,succeeded
b,SQL,Group1,Failed
c,FS,Group1,Failed
d,DBA,Group1,Failed
e,RDM,Group1,Failed
a,ALL,Group2,succeeded
b,SQL,Group3,succeeded
c,SQL,Group4, Failed
"@ | ConvertFrom-Csv

$result = $csv | Group-Object 'Client Name' | ForEach-Object {
    $succeeded = $_.Group | Where-Object { $_.Status -eq 'succeeded' }
    if (!$succeeded) { $_.Group }  # never succeeded, so output these
}

# output on screen
$result | Format-Table -AutoSize

# output to CSV
$result | Export-Csv -Path 'X:\yourFilteredCsv.csv' -UseCulture -NoTypeInformation

Result:

Client Name Save Set Name Group  Status
----------- ------------- -----  ------
c           FS            Group1 Failed
c           SQL           Group4 Failed
d           DBA           Group1 Failed
e           RDM           Group1 Failed

Seeing your latest comments, I think/hope I now understand the question better. Luckily, the code can be easily adjusted to group not only on 'Client Name', but also on 'Save Set Name' like below.

$result = $csv | Group-Object 'Client Name','Save Set Name' | ForEach-Object {
    # if any of the group items have a status of 'succeeded', skip that group
    $succeeded = $_.Group | Where-Object { $_.Status -eq 'succeeded' }
    if (!$succeeded) { $_.Group }  # never succeeded, so output these
}

# output on screen
$result | Format-Table -AutoSize

# output to CSV
$result | Export-Csv -Path 'X:\yourFilteredCsv.csv' -UseCulture -NoTypeInformation

Using example 1:

Client Name,Save Set Name,Group,Status
a,All,Group1,Failed
a,SQL,Group2,succeeded
b,SQL,Group1,Failed
c,FS,Group1,Failed
d,DBA,Group1,Failed
e,RDM,Group1,Failed
a,ALL,Group2,succeeded
b,SQL,Group3,succeeded
c,SQL,Group4, Failed

yields:

Client Name Save Set Name Group  Status
----------- ------------- -----  ------
c           FS            Group1 Failed
d           DBA           Group1 Failed
e           RDM           Group1 Failed
c           SQL           Group4 Failed

Using example 2:

Client Name,Save Set Name,Group,Status
gsiecwt2020.web.local,pseudo_saveset,D_CWT_File_System_1,failed
gsiecwt2020.web.local,J:\System,D_CWT_File_System_2,succeeded
gsiecwt2020.web.local,K:\System,D_CWT_File_System_1,succeeded
gsiecwt2020.web.local,K:\System,D_CWT_File_System_3,failed

yields:

Client Name           Save Set Name  Group               Status
-----------           -------------  -----               ------
gsiecwt2020.web.local pseudo_saveset D_CWT_File_System_1 failed

edited Apr 16, 2020 at 10:02

answered Apr 14, 2020 at 14:22

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GURU SINGH Over a year ago

@Theo- Thanks for your effort. In your script, I am facing an issue that it's not taking the "save set" tab in consideration. If any of the save gets successful, it is marking complete Client as successful which should not be the case. I have updated my question with new example

Theo Over a year ago

@GURUSINGH I don't know what you mean.. The field Save Set Name is present in the output and no, it is not marking the client successfull. Your question is to find clients that have never been successfull, so in each group, I'm testing to see if there ever was any 'succeeded' status. Only if this is not the case, the client gets output because all Status fields for that client said 'Failed'. P.S. I don't see the question edited.

GURU SINGH Over a year ago

@Theo- Question updated with new Example and explanation. Please check

GURU SINGH Over a year ago

@Theo- Could you please help.?

GURU SINGH Over a year ago

This is what actually i was looking for. Thanks a lot.

|

Collectives™ on Stack Overflow

How to filter CSV File with PowerShell?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related