locked
Adding notes to AD field with line breaks from a CSV RRS feed

  • Question

  • Hello,

    I have a script that creates new groups from a CSV.  The issue I am having is with the notes field. I have a column headed notes.  The column contains multiple lines with spaces. However when I create my group the notes are all clobbered together.  How can I update the notes field while preserving the lines breaks in my CSV?  An example of the notes field with the header is below.

    Notes

    Hi technet

    I can't get my notes field to preserve

    Line breaks when creating new AD Groups

    Can you help.

    Wednesday, March 4, 2020 1:23 AM

Answers

  • Just to clarify: are you working with, say, Excel to create the CSV? Or is the CSV created from some other application?

    If it's Excel, in cells containing multiple lines the lines are separated by the "LF" character (0x0A). That's represented by the "`n" escape sequence in Powershell.

    If the "info" attribute requires the line separators to be CrLf (0x0D0x0A) then this is what you might want:

    @{'info=($group.notes -replace "`n", "`r`n")}


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by 3k88EIL Wednesday, March 4, 2020 10:32 PM
    Wednesday, March 4, 2020 8:52 PM

All replies

  • What is the issue.  Just assign it to the field.

    The lines must be separated by line break. 


    \_(ツ)_/

    Wednesday, March 4, 2020 1:41 AM
  • With PowerShell I separate the lines with `r`n.

    Edit: Example:

    Set-ADUser -Identity "jsmith" -Replace @{info="First Line`r`nSecond Line"}


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Wednesday, March 4, 2020 1:47 AM
  • Thank you Richard, the issue is that I have multiple up to 10 groups and if possible I would prefer to not have to put the notes in manually but rather use my CSV. How would I incorporate the carriage returns with line otherAttributes @{'info'=$group.notes}. While using my CSV?

    $filePath = Read-host ("Please enter the file name and path to create your groups and press Enter")

    #Use a location from your C drive.  Example C:\temp\Test.csv

    $GetFile = Import-csv $filePath

     

    forEach ($group in $GetFile)

        {

          Try

          {

            $newGroups = New-ADGroup -Name $group.GroupName -GroupCategory $group.GroupCategory -GroupScope $group.GroupScope -Path $group.Path -description $group.Description -otherAttributes @{'info'=$group.notes}

            Write-Host("Created " + $group.Groupname + " in " + $group.Path) -ForegroundColor Green -BackgroundColor Black

          } catch {

             Write-Host $_.Exception.Message -ForegroundColor Red -BackgroundColor Black -ErrorAction Continue

             Write-Host("Could not create " + $group.name) -ForegroundColor Red -BackgroundColor Black

          }

           

        }

    Wednesday, March 4, 2020 11:52 AM
  • The "notes" field of your CSV file must have some indication of where the line breaks should be. Best would be to place the string "`r`n" everywhere a new line starts. Otherwise, you need to replace whatever does indicate a line break with "`r`n".

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Wednesday, March 4, 2020 12:01 PM
  • Thank you Richard, I formatted my CSV like the below and it still did not preserve the line breaks. Do you have any suggestions? Should each line be enclosed in quotes as well?

    "Primary Owner: TESTING"`r`n"

    Backup Owner:TESTING"`r`n"

    Data Steward Approval Required? Yes"`r`n"

    TESTING"`r`n"

    TESTING"`r`n"

    No Sensitive Group Access"`r`n"

    "`r`n"

    Business Justification: TESTING"`r`n""


    • Edited by 3k88EIL Wednesday, March 4, 2020 1:19 PM
    Wednesday, March 4, 2020 1:18 PM
  • The string "`r`n" is part of the entire string, so it should be inside the quotes. It is an escape sequence representing the characters for line feed carriage return, similar to any other characters. A csv cannot have a record on multiple lines, or it will be interpreted as separate records, so I assume what you posted is line wrapped. The entire value should be in one set of quotes. Example:

    "Primary Owner: TESTING`r`nBackup Owner:TESTING`r`nData Steward Approval Required? Yes`r`nTESTING`r`nTESTING`r`nNo Sensitive Group Access`r`n`r`nBusiness Justification: TESTING`r`n"
    


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Wednesday, March 4, 2020 1:35 PM
  • Thank you Richard I formatted my CSV as you had your example formatted and still no luck. Would excel be a better option to do this if it something in the csv?

    • Edited by 3k88EIL Wednesday, March 4, 2020 5:32 PM
    Wednesday, March 4, 2020 5:30 PM
  • I tried Richard's line and it works just fine. I am not sure how you have formatted your line.
    Wednesday, March 4, 2020 6:07 PM
  • The value I gave results in the following in PowerShell:

    Edit: And I have used similar to assign multi-lined values to the info attribute.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Wednesday, March 4, 2020 8:10 PM
  • Just to clarify: are you working with, say, Excel to create the CSV? Or is the CSV created from some other application?

    If it's Excel, in cells containing multiple lines the lines are separated by the "LF" character (0x0A). That's represented by the "`n" escape sequence in Powershell.

    If the "info" attribute requires the line separators to be CrLf (0x0D0x0A) then this is what you might want:

    @{'info=($group.notes -replace "`n", "`r`n")}


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by 3k88EIL Wednesday, March 4, 2020 10:32 PM
    Wednesday, March 4, 2020 8:52 PM
  • Yo cannot use notepad to type in the codes.  You must insert an actual line break in notepad or a text editor.,  The codes only work in PowerShell when creating a script to store.

    The following demonstrate how this works:

    @"
    SamAccountName, info
    jsmith, "This is many lines`r`nof text`r`ncreated in`r`nPowerShell"
    jones, "This is many lines`r`nof text`r`ncreated in`r`nPowerShell"
    mbrown, "This is many lines`r`nof text`r`ncreated in`r`nPowerShell"
    "@ | ConvertFrom-Csv | Export-Csv info.csv
    
    $csv = Import-Csv info.csv
    
    $csv|
        ForEach-Object{
            Set-AdUser $_.SamAccountName -replace @{info=$_.info} 
        }

    If you open the CSV in notepad you will see the lines are broken across lines.  To do this in notepad, Excel or any other editor you need to use the "Enter" key to break the lines.  The codes will just be stored as text and PS does not convert them when importing the SV,


    \_(ツ)_/


    • Proposed as answer by Richard MuellerMVP Wednesday, March 4, 2020 9:28 PM
    • Edited by jrv Wednesday, March 4, 2020 9:37 PM
    Wednesday, March 4, 2020 8:58 PM
  • Jim is correct, but replace "1r1n" with "`r`n`" in his example.

    I just figured out that my example works in PowerShell, but the line returns are unreadable characters in ADUC. My mistake.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Wednesday, March 4, 2020 9:35 PM
  • Jim is correct, but replace "1r1n" with "`r`n`" in his example.

    I just figured out that my example works in PowerShell, but the line returns are unreadable characters in ADUC. My mistake.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    I fixed that twice but the copy/paste seems to have messed me up.  Thanks.  I fixed my original post.


    \_(ツ)_/

    Wednesday, March 4, 2020 9:38 PM
  • You guys are awesome! The below worked perfectly. I've been. Eating my head for 3 months on this!!!

    @{'info=($group.notes -replace "`n", "`r`n")}

    Wednesday, March 4, 2020 10:31 PM