CODEWITHSUNDEEP
powershellcsv
Pablo
Pablo

Reputation: 31

PowerShell CSV line break but without changing the cell in the generated CSV

I get a list of users that I store in a variable that will then be used to create a CSV. Originally the elements of the variable were separated by a comma using -join (","). I want the "," to be replaced by a line break so I used -join ("'n") the line break is done well but the elements of the variable are not in the same cell.

This is what the CSV gives me:

What I have

Here is the desired result:

What I want

What I tried and it doesn't seem to work :

-join [Environment]::NewLine

ReplaceCommaWithNewLine($Compare -join ",")

$data = Get-Content -Path $Chemin_File_CSV                                                  
$data = $data -replace ",","`n"                                                         
$data | Set-Content -Path $Chemin_File_CSV                                                   
$data = Import-Csv -Path $Chemin_CSV_File

Do you know how to solve my problem ?

You can find the corresponding script below :

# Retrieving the list of privileged groups to check
$GroupsToCheck = @("Administrateurs","Administrateurs du schéma","Administrateurs de l’entreprise","Admins du domaine",       "Opérateurs de compte","Opérateurs de serveur" ,"Opérateurs de sauvegarde","Opérateurs d’impression")

# Retrieve the list of members of the AD group "Protected Users
$ADGroup_ProtectedUsers_Members = (Get-ADGroupMember "Protected Users").SamAccountName

# Comparison of the groups contained in the $GroupsToCheck table and in the AD group "Protected Users
$Compare = ((Compare-Object -ReferenceObject $GroupsToCheck  -DifferenceObject $ADGroup_ProtectedUsers_Members) | Where-Object -Property SideIndicator -EQ "<=").InputObject

# If $Compare is empty, Conform
if (!$Compare) {
    $Status = "Conform"
    $Value = $null

# Else, Not Conform
} else {
     $Status = "Non Conform"
     $Value=  ($Compare -join "`n")
}

$Path__CSV = "C:\Users\Public\Documents\test.csv"

$Content_CSV = (@"
"Status" ; "Value"
$Status  ; $Value
"@) | Out-File -FilePath $Path_CSV -Encoding UTF8 -Append

Thank you in advance.

Upvotes: 3

Views: 390

Answers (1)

mklement0
mklement0

Reputation: 437638

Don't use plain-text processing; create objects ([pscustomobject]), which you can pass to Export-Csv.

[pscustomobject] @{
  Status = $Status
  Value = $Value
} | # see note re -UseCulture and -Delimiter at the bottom
  Export-Csv -NoTypeInformation -Encoding utf8 $Path_CSV -Append

Export-Csv automatically takes care of enclosing multi-line strings in "...", which preserves their embedded newlines as part of the field (column) value (in fact, all fields are enclosed in "...")[1], as the following example with its in-memory equivalent, ConvertTo-Csv, demonstrates:

[pscustomobject] @{
  SomeColumn = 42
  MultiLineColumn = 'foo', 'bar', 'baz' -join "`n"
} | 
  ConvertTo-Csv

Output:

"SomeColumn","MultiLineColumn"
"42","foo
bar
baz"

Note:

  • If your CSV format uses a culture-specific field separator in lieu of , - such as ; - add -UseCulture to your Export-Csv / ConvertTo-Csv calls, assuming the desired separator is the one associated with the current culture.

  • If you want to control the field separator (delimiter) explicitly, use the -Delimiter parameter.

[1] This is invariably true in Windows PowerShell, and true by default in PowerShell (Core) 7+, where you now get to control the quoting behavior via the -UseQuotes and -QuoteFields parameters.

Upvotes: 3

Related Questions