Filling empty cells in CSV with powershell based upon cell above empty cell

Question

I have a CSV file that I pull down and need to manipulate on a weekly basis. Part of that manipulation is filling empty cells with the proper Department and Brand as it is generated with empty cells for matching entries.

For example, Cell B34 contains the text "Frozen" while cells B35 through B40 are empty. Cell B41 contains the text "Dairy". I would like to copy the text "Frozen" from cell B34 to cells B35 through B40 using powershell.

I have looked around for an answer but haven't had any luck.

Thank you.

EDIT:

I used the code from wOxxOm in the comments as below.

$csv = import-csv inputfilepath.csv
$prevRow = $csv[0]; 
foreach ($row in $csv[1..($csv.length-1)]) {
    if (!$row.subCatDesc) { $row.subCatDesc = $prevRow.subCatDesc }; $prevRow = $row | export-csv outputfilepath.csv
}

Where subCatDesc is the header for the column. When I do this, it runs fine but the output file has cell A1 reading "#TYPE System.Management.Automation.PSCustomObject", Row 2 contains the same headers as the source file, and then there is only Row 3 which is the final line of the source file.

EDIT 2:

Modifying again with TheIncorrigible1's input:

$csv = import-csv -Path inputfilepath.csv
$prevRow = $csv[1]; 
foreach ($row in $csv[2..($csv.length-1)]) {
    if (!$row.subCatDesc) { $row.subCatDesc = $prevRow.subCatDesc }; $prevRow = $row 
}
$csv | Export-Csv -Path outputfilepath.csv -NoTypeInformation

This clears up the above issues, and actually successfully completed the task, with the exception of the first set of empty cells (Cells B3 to B19 in my test CSV) remaining empty and not copying the contents of B2 which is "Cheese".

EDIT 3:

Adjusting again:

$prevRow = $csv[0]

and foreach ($row in $csv[1..csv.length-1]) {

as originally given by wOxxOm resolves this. Thank you all, it works now.

I haven't because I haven't the foggiest where to start, and none of the related-ish code snippets I have found do quite what I am looking for. — BryceJenkins
Something like $prevRow = $csv[0]; foreach ($row in $csv[1..($csv.length-1)]) { if (!$row.b) { $row.b = $prevRow.b }; $prevRow = $row } — wOxxOm
Thanks wOxxOm. I will play with that and post my results. I mostly just needed a leg up really. — BryceJenkins
Use $csv = Import-Csv -Path 'file.csv' and toy with the members as a starting point. Import-Csv brings it in as a native PSObject based on the headers so it's easy to manipulate. After you're done, you can use $csv | Export-Csv -Path 'file.csv' -NoTypeInformation — Maximilian Burszley

BryceJenkins BryceJenkins · Accepted Answer · 2017-08-10T16:18:05

Based upon the comments above here is the resulting code that works:

$csv = import-csv -Path inputfilepath.csv
$prevRow = $csv[0]; 
foreach ($row in $csv[1..($csv.length-1)]) {
    if (!$row.subCatDesc) { $row.subCatDesc = $prevRow.subCatDesc }; $prevRow = $row 
}
$csv | Export-Csv -Path outputfilepath.csv -NoTypeInformation

Filling empty cells in CSV with powershell based upon cell above empty cell

1 Answers