remove lines from flat file with no header

Question

Post reply

remove lines from flat file with no header

Bruin

SSCrazy

Points: 2004
More actions
September 9, 2020 at 2:52 pm
Go to Answer
#3787346
I have a file that has a delimiter of bar (|) and has no headers. I need a way to remove bad data rows if it meets criteria. Then create
a new file with rows removed, and keep a log of the bad rows.
This is my criteria for rows I don't want in the new file:
# Where $cols[0] -like '"20230201"' -and $cols[11] -like '5'
Thanks.
```
$badLines = 0
Get-Content 'C:\CSV_Destination\poddet.csv' | ForEach-OBJECT {
    $currentLine = $_
    $cols = $_.Split('|')
    TRY {

    # Where $cols[0] -like '"20230201"' -and $cols[11] -like '5'

    } CATCH {
        Write-OUTPUT $currentLine
        $badLines ++
    }
}
IF ($badLines -gt 0)
{
    THROW "$($badLines) lines contained invalid dates"
}
```
Jeffrey Williams

SSC Guru

Points: 90244
More actions
September 9, 2020 at 10:25 pm
Answer
#3787430
Here is one method - you can extend as needed:
```
$badFile = "C:\Temp\badfile.csv";
$goodFile = "C:\Temp\goodfile.csv";

Import-Csv -Path C:\temp\testfile.csv -Delimiter "|" -Header (1..12) |
    Group-Object -Property {$_.1 -eq "20230201" -and $_.11 -eq "5"} | % {
        ($_.Group | ConvertTo-Csv -Delimiter "|" -NoTypeInformation | Select-Object -Skip 1) -replace '"', '' | 
                Set-Content -Path "$(if($_.Name -eq 'True') {$badFile} else {$goodFile})";
    }
```
This assumes only 12 columns - but you can increase as needed. The Group-Object property returns true or false based on the conditions and 'groups' the rows based on that value. We then convert the values in each group to CSV - skip the first row (column headers) - remove quotes and output to either our 'good' file or 'bad' file.
If you want all columns quoted and you don't mind including column headers in the output - you can use Export-Csv. That would be a simple Import-Csv (defining the actual column headers) - pipe to a foreach-object (each row) - check each row for condition to determine which file - then export.
```
$badFile = "C:\Temp\badfile.csv";
$goodFile = "C:\Temp\goodfile.csv";

Import-Csv -Path C:\temp\testfile.csv -Delimiter "|" -Header (1..12) | % {
    $outputFile = if ($_.1 -eq '20230201' -and $_.11 -eq '5') {$badFile} else {$goodFile};
    Export-Csv -InputObject $_ -Delimiter "|" -LiteralPath $outputFile -NoTypeInformation -Append;
    }
```
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

It would probably help your cause a bit if you posted what several good and bad rows actually look like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2004 More actions · Answer 2

Bruin

SSCrazy

Points: 2004

September 10, 2020 at 12:32 am

#3787443

That works perfect.

Many Thanks.

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 3

You are welcome - which version did you end up using?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs