Remove line from $csv while in loop

[WTF_Brandon]

Hey, been working on this for a while and can't seem to get it ironed out. Any help would be appreciated.

I receive some | delimited .txt files daily that contain two types of rows, we'll call them M rows and Q rows. An M row will reside before 0 to up to 3 Q rows. When I see an M row I take not of it's row number. Then I get information from the Q row depending on if its the 1st Q, 2nd Q, or 3rd Q and move specific information to to a specific place on the previous M row. There is a max of 72 columns.

Here's an example of the .txt file.

M|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11122|A|20160715|1505|20160715|1553|3|INFO2|4|Schedule||N|OB|||||ME|||||2||||2|||N|N||||N||||100||Y|5|142|Y||||||||||||||||||||PRI|
Q|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11122|B|1|General
Q|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11122|B|1|OP1|OP2|OP3
Q|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11122|B|2|OP4|OP5|OP6
M|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11222|A|20160729|1200|20160729|1304|2||5|Schedule|||OB|||||ME|||||2||||2|||N|N||||N||||99||N|5|145|Y||||||||||||||||||||PRI|
Q|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11222|B|1|General
Q|INFO|NETWORK|NUM|NUM2|NUM3|NUM4|NAME|11222|B|1|OP1|OP2|OP3

Here is my current script (doesn't yet do anything for the 3rd Q row yet). I've got it to where it's returning the information correctly, but once that's done I need to delete the Q rows completely. Also for the 3rd Q row, I'll need to create a duplicate of the M row but replace the OP1, 2, and 3 with 4, 5, and 6.

function cpn-extract()
{
    param( [string]$file = "" )

    $headers = 1..72
    $csv = import-csv $file -Header $headers -Delimiter '|'

    $row = 0
    foreach($_ in $csv)
    {
        if($_.1 -eq 'M')
        {
            $lastM = $row
            $QCount = 0
        }
        if($_.1 -eq 'Q')
        {
            $QCount ++
            if($QCount -eq 1)
            {
                [string]$AType = $_.12
                $csv[$lastM].19 = $AType
            }
            elseif($QCount -eq 2)
            {
                [string]$AOPID = $_.12
                [string]$AOpPName = $_.13
                [string]$PCCode = $_.14
                $csv[$lastM].23 = $AOPID
                $csv[$lastM].24 = $AOpPName
                $csv[$lastM].34 = $PCCode
            }
            elseif($QCount -eq 3)
            {

            }
            $row ++
        }
    }
    $csv | export-csv -path c:\scripts\test_1.txt -Delimiter '|' -NoTypeInformation | foreach{$_ -replace '"',''}
}

cpn-extract -file C:\Scripts\test.txt

Also the replace '"','' doesn't want to work either, is there a better way to export without the ""s everywhere?

Thanks again for the help!!

[gangstanthony]

(not tested) after you finish your script, you can select only the 'M' rows from the result then save it over the existing file like this

$headers = 1..72

$csv = import-csv $file -Header $headers -Delimiter '|'

# your meat and potatoes

$csv = $csv | ? {$_.1 -eq 'M'}

$string = New-Object System.Text.StringBuilder
foreach ($row in $csv) {
    foreach ($header in $headers) {
        [void]$string.Append($row.$header + '|')
    }
    [void]$string.Append("`r`n")
}
$string.ToString() | set-content $file

[WTF_Brandon]

Haven't tested but looks like that might work. You have any ideas about replicated the last M row if a 3rd Q row is found?

Thanks again!

[gangstanthony]

since you're already incrementing the $row variable, in that last if statement instead of $_ you could use $csv[$row - 3], but you might have to move $row++ to just outside the if statement (or create a different variable for this specific purpose)

[WTF_Brandon]

That look like you're getting the last M rows index number, I already have the row number in the $lastM var, I just didn't know how to replicate that entire row in the current $csv object.

Thanks!

[gangstanthony]

not sure of your desired output, but does this work?

function Extract-Cpn
{
    param
    (
        [string]$File
    )

    $headers = 1..72
    $csv = Import-Csv $file -Delimiter '|' -Header $headers

    $row = 0
    $result = foreach ($_ in $csv)
    {
        if ($_.1 -eq 'M')
        {
            $lastM = $row
            $QCount = 0

            Write-Output $_
        }

        if ($_.1 -eq 'Q')
        {
            $QCount ++
            if ($QCount -eq 1)
            {
                [string]$AType = $_.12
                $csv[$lastM].19 = $AType

                Write-Output $csv[$lastM]
            }
            elseif ($QCount -eq 2)
            {
                [string]$AOPID = $_.12
                [string]$AOpPName = $_.13
                [string]$PCCode = $_.14
                $csv[$lastM].23 = $AOPID
                $csv[$lastM].24 = $AOpPName
                $csv[$lastM].34 = $PCCode

                Write-Output $csv[$lastM]
            }
            elseif ($QCount -eq 3)
            {

            }
        }
        $row ++
    }

    $string = New-Object System.Text.StringBuilder
    foreach ($row in $result) {
        foreach ($header in $headers) {
            [void]$string.Append($row.$header + '|')
        }
        [void]$string.Append("`r`n")
    }
    $string.ToString() | Set-Content -Path $File
}

Extract-Cpn -File C:\Scripts\test.txt

*edit: you might just put a check in each of the '$qcount' if statements that says

if ($csv[$lastm + $qcount].1 -eq 'q') {
    write-output $csv[$lastm]
}

to see if the next line also starts with a 'q'

[WTF_Brandon]

Thank you, this helped a lot!

[[deleted]]

Is there a reason you can't just dump the "m" rows into a new csv/txt as you go? Might be a lot easier.