I have excel spread sheet where NSG rules are added in that excel spread sheet. I am writing PowerShell code to check the completely excel sheet if any line breaks are present in the sheet (attached the screenshot and highlighted in the red color) and if any line break exists then convert the values by removing the line break and add the comma before that value. I have tried the requirement with below code, taken from this answer:
$path = "./NSG_Rules.xlsx"
$rows = Import-Excel $path
# Get the column (property) names
$propNames = $rows[0].psobject.Properties.Name
$rowsToDeleteIndices = [System.Collections.Generic.List[int]] @()
# Process all rows, expanding the cells of merged columns into individual rows.
$rowIndex = 1 # Start with 1, to account for the header row.
foreach ($row in $rows) {
++$rowIndex
foreach ($propName in $propNames) {
$propValue = $row.$propName
# Check if the value contains a comma and needs to be expanded
if ($propValue -match ',') {
$values = $propValue -split ','
# Update the current row with the first value
$row.$propName = $values[0]
# Create new rows for the remaining values
for ($i = 1; $i -lt $values.Count; $i++) {
$newRow = $row.psobject.Copy()
$newRow.$propName = $values[$i]
$rows += $newRow
}
}
}
}
# Export the modified values back to the worksheet,
# in place, with all formatting preserved.
# Via -PassThru, obtain a reference to the package (workbook),
# for advanced operations such as row deletion.
$pkg = $rows | Export-Excel $path -PassThru
# Obtain a reference to the first worksheet again
# (the same one implicitly targeted by Import-Excel / Export-Excel).
$ws = $pkg.Workbook.Worksheets[1]
# Now delete the no longer needed rows with merged columns from it.
$i = 0
$rowsToDeleteIndices | ForEach-Object { $ws.DeleteRow($_ + $i--) }
# Close and thereby implicitly save the package.
Close-ExcelPackage $pkg`
my requirement is the excel spread sheet should update by removing the line breaks and add comma to that value. I am trying to get the same format of the input sheet as well as in the output.
Below are the screenshots for reference:
expected output in after code execution:

Note: I have raised same type of question earlier but this requirement is quite different compared to other questions and sorry for raising multiple questions.
