PowerShell: populate column A null rows with data from previous column A row data, if Column B value-CodePudding

I have an oddly formatted csv file that I need to adjust and I could use some help using powershell. I usually have a pretty good idea where to start, but I am at a bit of a loss with this one on how to iterate data from column A from the previous row to the next row based on Column B having "sales order" and only doing it if the Column A field is Null and then using the next row that is populated to continue down the line.

Here is what the csv looks like as a picture:

Here is the actual data:

,Type,Date,Due Date,Num
Inventory,,,,
2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),,,,
,Sales Order,7/23/2021,7/23/2021,12834
Total 2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),,,,
Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,9/23/2021,9/23/2021,13304
Total Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,7/23/2021,7/23/2021,12841
Total Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,4/1/2021,4/1/2021,11807
Total Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,6/9/2021,6/9/2021,12457
Total Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),,,,
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,,,,
,Sales Order,6/7/2021,6/7/2021,12425
,Sales Order,8/3/2021,8/3/2021,12939
,Sales Order,9/14/2021,9/14/2021,13218
Total Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,,,,
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,,,,
,Sales Order,6/8/2021,6/8/2021,12454
,Sales Order,8/24/2021,8/24/2021,13102
,Sales Order,9/14/2021,9/14/2021,13220
,Sales Order,10/8/2021,10/8/2021,13415

I need to fill the blank lines in Column A that have "Sales Order" in column B with the line from above. (i hate quickbooks report formatting) so it looks like this: (added lines are highlighted in Yellow)

Data looks like this:

,Type,Date,Due Date,Num
Inventory,,,,
2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),,,,
2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),Sales Order,7/23/2021,7/23/2021,12834
Total 2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),,,,
Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),Sales Order,9/23/2021,9/23/2021,13304
Total Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),Sales Order,7/23/2021,7/23/2021,12841
Total Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),Sales Order,4/1/2021,4/1/2021,11807
Total Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),Sales Order,6/9/2021,6/9/2021,12457
Total Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),,,,
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,,,,
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,Sales Order,6/7/2021,6/7/2021,12425
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,Sales Order,8/3/2021,8/3/2021,12939
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,Sales Order,9/14/2021,9/14/2021,13218
Total Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,,,,
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,,,,
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,Sales Order,6/8/2021,6/8/2021,12454
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,Sales Order,8/24/2021,8/24/2021,13102
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,Sales Order,9/14/2021,9/14/2021,13220
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,Sales Order,10/8/2021,10/8/2021,13415

as a side note, if anyone knows how to display Quickbooks desktop report for "open sales orders by item" to display in a better format that would do that, that would also be great.

CodePudding user response：

$Data = ConvertFrom-Csv @'
,Type,Date,Due Date,Num
Inventory,,,,
2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),,,,
,Sales Order,7/23/2021,7/23/2021,12834
Total 2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid),,,,
Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,9/23/2021,9/23/2021,13304
Total Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,7/23/2021,7/23/2021,12841
Total Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,4/1/2021,4/1/2021,11807
Total Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer)),,,,
Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),,,,
,Sales Order,6/9/2021,6/9/2021,12457
Total Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer)),,,,
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,,,,
,Sales Order,6/7/2021,6/7/2021,12425
,Sales Order,8/3/2021,8/3/2021,12939
,Sales Order,9/14/2021,9/14/2021,13218
Total Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...,,,,
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...,,,,
,Sales Order,6/8/2021,6/8/2021,12454
,Sales Order,8/24/2021,8/24/2021,13102
,Sales Order,9/14/2021,9/14/2021,13220
,Sales Order,10/8/2021,10/8/2021,13415
'@

WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers.

$Data |ForEach-Object { $H1 = '' } {
    If ($_.H1) { $H1 = $_.H1 }
    If ($_.Type -eq 'Sales Order') { $_.H1 = $H1 }
    $_
} |Format-Table

Result:

H1                                                                                         Type        Date      Due Date  Num
--                                                                                         ----        ----      --------  ---
Inventory
2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid)
2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid)                      Sales Order 7/23/2021 7/23/2021 12834
Total 2.25 Gal Cast Iron KettlewLid (2.25 Gallon Cast Iron Kettle with Lid)
Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer))
Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer))           Sales Order 9/23/2021 9/23/2021 13304
Total Antika Chestnut Brown (Antika Chestnut Brown (87 sqft/pallet - 9.67 sqft/layer))
Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer))
Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer))                   Sales Order 7/23/2021 7/23/2021 12841
Total Antika Onyx Black (Antika Onyx Black (87 sqft/pallet - 9.67 sqft/layer))
Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer))
Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer))                   Sales Order 4/1/2021  4/1/2021  11807
Total Antika Sandlewood (Antika Sandlewood (87 sqft/pallet - 9.67 sqft/layer))
Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer))
Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer))                   Sales Order 6/9/2021  6/9/2021  12457
Total Antika Shale Grey (Antika Shale Grey (87 sqft/pallet - 9.67 sqft/layer))
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...       Sales Order 6/7/2021  6/7/2021  12425
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...       Sales Order 8/3/2021  8/3/2021  12939
Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...       Sales Order 9/14/2021 9/14/2021 13218
Total Architect Cap Champlain Grey (Architectural Cap Champlain Grey (66 linft/pallet -...
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...       Sales Order 6/8/2021  6/8/2021  12454
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...       Sales Order 8/24/2021 8/24/2021 13102
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...       Sales Order 9/14/2021 9/14/2021 13220
Architect Cap Chestnut Brown (Architectural Cap Chestnut Brown (66 linft/pallet -...       Sales Order 10/8/2021 10/8/2021 13415