Reformat csv file with 2 line header with Powershell

Question

I have a csv that has a category, followed by a header row, data, then a total row. The next category comes after a blank line. This pattern repeats. I would like to reformat this to another file to use in a spreadsheet. When I read this file the first line Misc Expense becomes the header, the date is the only value in this field.

The import-csv command also stops reading to the first blank line
Any assistance would be appreciated

CSV example:

"Misc expense
"Date,Ref,Payee,Amount,Note
11/04/2021,DEP,"Vendor1","5,048.34","Roof"
12/12/2021,DEP,"Vendor2","345.97","Office Supply"
,,"Misc Expense Total:","5394.31","

Misc Income
"Date,Ref,Payee,Amount,Note
11/17/2021,DEP,Vendor3,"1,000.00",
12/03/2021,DEP,Vendor4,"456.00",
,,"Misc Income Total:","1,456.00",

Current Output

Misc Expense

---

11/04/2021
12/12/2021

Expected or the desired outcome

Category   Date Ref Payee Amount Note
Misc Expense 11/04/2021 DEP Vendor1 5,048.34 Roof
Misc Expense 12/12/2021 DEP Vendor2 345.97 Office Supply
Misc Income 11/17/2021 DEP Vendor3 1,000.00
Misc Income 12/03/2021 DEP Vendor4 456.00

I started with this, but it only sees the first line which is the category, then produces blanks with commas

Import-Csv .\2021_cat_trans.csv |
ForEach-Object {
    Write-Host "$($_.Date), $($_.Ref), $($_.Payee), $($_.Amount), $($_.Note)."
}

This line will pull just the date in the first column

Powershell

 Import-Csv .\2021_cat_trans.csv

Looking to know where to start I am looking into get-content but need some guidance

Answer 1

This isn't a very elegant way to do it, but see if this works for you. The code uses a fixed set of data rather than an input file, but have to replace the line "$y=$x -split "rn"" with "$y = get-content . . ." to get the data from your file. You can remove the "here string" since it's just the contents of the data you posted.

$x=@'
"Misc expense
"Date,Ref,Payee,Amount,Note
11/04/2021,DEP,"Vendor1","5,048.34","Roof"
12/12/2021,DEP,"Vendor2","345.97","Office Supply"
,,"Misc Expense Total:","5394.31","

Misc Income
"Date,Ref,Payee,Amount,Note
11/17/2021,DEP,Vendor3,"1,000.00",
12/03/2021,DEP,Vendor4,"456.00",
,,"Misc Income Total:","1,456.00",
'@

$y=$x -split "`r`n"
$Category = ""
$Header = ""
$NewCsv = @()
ForEach ($l in $y) {
    if ($l -match '(Misc expense|Misc income)$'){   # get the category
        $Category = $matches[1]
        continue
    }
    elseif ($l -match '\sTotal'){      # ignore total lines
        continue
    }
    elseif ($l -match '(Date,Ref,Payee,Amount,Note)' ){
        if ($Header.Length -eq 0){
            $Header = "Category," + $matches[1]
            $NewCsv += $Header
            continue
        }
        else{
            continue
        }
    }
    elseif($l.Trim().Length -lt 1){
        continue                        # drop empty lines
    }
    $NewCsv += $Category + "," + $l
}
$NewCsv | Out-File c:\junk\Clean.Csv