Reformat csv file with 2 line header with Powershell

richardsmith2 · 2022-01-15T20:11:21.000Z

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

Neally · 2022-01-15T20:15:13.000Z

Welcome.

We’re happy to help, but not a script writing service.

What have you tried? where are you stuck?

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don't say "Need help" or "PowerShell Help", actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy, past…

192033ab-bb8f-4032-88a5-8e2313af0344-codebutton_small.png

Neally Bot

Neally · 2022-01-15T20:18:04.000Z

richardsmith2:

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.

just so you know, that’s not a valid CSV format. Sure, the file has a ‘.csv’ ending, but the format in it, is NOT CSV. THat’s why ‘import-csv’ does not work.

You have to use ‘get-content’ and iterate through it.

richardsmith2 · 2022-01-15T22:18:44.000Z

Thanks for the replies. I should have been a little more descriptive in my request. I mainly wanted to know if this was possible and if anyone has ever seen this type of csv format. Usually, when I see these issues, I can have someone review them and suggest a method that I can try.

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

  Import-Csv .\2021_cat_trans.csv

One of the comments suggested ‘get-content’, which I can review. Sorry for the confusion. I was looking for guidance on where to start.

Neally · 2022-01-16T05:20:18.000Z

"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",

$inFile = get-content "D:\TestFolder\input.csv"
$data = $inFile | foreach-object { if ($_ -notlike "") { $_ } }

$table =
for ($i = 0; $i -lt ($data | measure).count; $i = $I + 5) {
    for ($c = 0; $c -lt 2; $c++) {
        if($c -eq 0){
            $Category = ( $data[$i  +$c] -split ",")[0] -replace '"'
            $Date     = ( $data[$i+2+$c] -split ",")[0]
            $Ref      = ( $data[$i+2+$c] -split ",")[1]
            $Payee    = ( $data[$i+2+$c] -split ",")[2] -replace '"'
            $Amount   = (($data[$i+2+$c] -split ",")[3..4] -replace '"') -join ","
            $Note     = ( $data[$i+2+$c] -split ",")[5] -replace '"'
        }
        else{
            $Category = ( $data[$i]      -split ",")[0] -replace '"'
            $Date     = ( $data[$i+2+$c] -split ",")[0]
            $Ref      = ( $data[$i+2+$c] -split ",")[1]
            $Payee    = ( $data[$i+2+$c] -split ",")[2] -replace '"'
            $Amount   = (($data[$i+2+$c] -split ",")[3] -replace '"') -join ","
            $Note     = ( $data[$i+2+$c] -split ",")[4] -replace '"'
        }

        [pscustomobject]@{
            Category =  $Category 
            Date     =  $Date     
            Ref      =  $Ref      
            Payee    =  $Payee    
            Amount   =  $Amount   
            Note     =  $Note     
        }
    }
}

$table

fd506d45-da13-42fd-a894-5aa11191ca58-2022-01-15_23-19-53.png

richardsmith2 · 2022-01-16T21:25:47.000Z

Wow, this is incredible. This is a great starting place for me. This was written based on each section having 2 rows of data. The sections will have variable lengths, but I can try to work on that. I appreciate what you have done. I did not expect anyone to go this far. I hope you get credit for answering this post.

Thanks again,

Richard