1

I have a string with structured data (see below). I need to take this string and convert it to an object, so I can export it to .csv (or whatever else is requested of me). I ran the following code:

$data = $string -replace "\s*:\s*","="

But my output looks like this:

City=Country=Department=DisplayName=John Doe
DistinguishedName=CN=John Doe, CN=Users, DC=domain, DC=com
[email protected]
Enabled=False
Fax=GivenName=John
MobilePhone=Name=John Doe
ObjectClass=user
ObjectGUID=cdb9a45c-80f4-4919-bf43-5db8d9ca83da
Office=OfficePhone=PostalCode=SamAccountName=jdoe
SID=S-1-5-21-2025429266-2000478354-1606980848-16934
State=StreetAddress=Surname=Doe
[email protected]

This is clearly not correct. What is a better way to make this conversion? I thought about using ConvertFrom-String with the TemplateContent parameter, but haven't been able to make that work yet.

Here are the first two entries in the string (which contains several users worth of data):

$string = @"
City              :
Country           :
Department        :
DisplayName       : John Doe
DistinguishedName : CN=John Doe,CN=Users,DC=domain,DC=com
EmailAddress      : [email protected]
Enabled           : False
Fax               :
GivenName         : John
MobilePhone       :
Name              : John Doe
ObjectClass       : user
ObjectGUID        : cdb9a45c-80f4-4919-bf43-5db8d9ca83da
Office            :
OfficePhone       :
PostalCode        :
SamAccountName    : jdoe
SID               : S-1-5-21-2025429266-2000478354-1606980848-16934
State             :
StreetAddress     :
Surname           : Doe
Title             :
UserPrincipalName : [email protected]

City              :
Country           :
Department        :
DisplayName       : DiscoverySearchMailbox{D919BA15-46A6-415f-80AD-7E09334BB852}
DistinguishedName : CN=DiscoverySearchMailbox {D919BA15-46A6-415f-80AD-7E09334BB852},CN=Users,DC=domain,DC=com
EmailAddress      : DiscoverySearchMailbox{D919BA15-46A6-415f-80AD-7E09334BB852}@domain.com
Enabled           : False
Fax               :
GivenName         :
MobilePhone       :
Name              : DiscoverySearchMailbox{D919BA15-46A6-415f-80AD-7E09334BB852}
ObjectClass       : user
ObjectGUID        : 0f35137a-de93-472f-9114-5488a462d178
Office            :
OfficePhone       :
PostalCode        :
SamAccountName    : SM_2187102a90634829b
SID               : S-1-5-21-2438891277-1009865731-3229889747-3109
State             :
StreetAddress     :
Surname           : MsExchDiscoveryMailbox D919BA15-46A6-415f-80AD-7E09334BB852
Title             :
UserPrincipalName : DiscoverySearchMailbox{D919BA15-46A6-415f-80AD-7E09334BB852}@domain.com
"@

Thanks.

Improve this question
0

3 Answers 3

Reset to default
2

Assuming that you don't mind that the order of the properties of the resulting custom objects doesn't reflect the input order, you can use ConvertFrom-StringData (I suggest avoiding the finicky and poorly documented ConvertFrom-String):

$string.Trim() -split '\r?\n\r?\n' | ForEach-Object { 
  [pscustomobject] ($_ -replace '(?m)^(.+?):', '$1=' | ConvertFrom-StringData)
}  # | Export-Csv ....

Note: Casting to [pscustomobject] requires PSv3+; on PSv2, use New-Object PSCustomObject -Property (...)

  • If, by contrast, you want the properties to be in input order without having to repeat or even know their names (a quick fix to the above would be to pipe to Select-Object with the property names enumerated in the desired order), see this solution.

  • $string.Trim() -split '\r?\n\r?\n' -ne '' splits the input lines into blocks of lines each representing one object; splitting is performed by two consecutive newlines (either CRLF or LF-only ones).

    • .Trim() ensures that any leading or trailing empty lines are ignored.

  • $_ -replace '(?m)^(.+?):', '$1=' | ConvertFrom-StringData converts each block into <key>=<value> lines that ConvertFrom-StringData converts as a group to a [hashtable] instance; because hash tables inherently enumerate their entries in no guaranteed order, this is where the input ordering of properties is lost.

    • Note: In PowerShell (Core) 7, ConvertFrom-StringData now supports a -Delimiter parameter, which simplifies the above to:
      $_ | ConvertFrom-StringData -Delimiter :

    • GitHub issue #19070 is a feature request to make ConvertFrom-StringData emit ordered hashtables instead, which then would preserve the input order.

  • Cast [pscustomobject] converts each hashtable to a custom object, which is implicitly output; the output can be piped to Export-Csv.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I appreciate you digging into this with me.
@StackExchangeGuy: My pleasure; glad we worked it out - it was an obscure symptom; what happened was that the empty lines were converted into empty objects, which produce an empty line when converted to CSV; and sine they were at the start of the string, their columns - in this case:lack thereof - were locked in for all remaining objects, so the remaining objects too produced just empty lines.
0

Here You go:) 

    $a=@"
City              :
Country           :
Department        :
DisplayName       : John Doe
DistinguishedName : CN=John Doe,CN=Users,DC=domain,DC=com
EmailAddress      : [email protected]
Enabled           : False
Fax               :
GivenName         : John
MobilePhone       :
Name              : John Doe
ObjectClass       : user
ObjectGUID        : cdb9a45c-80f4-4919-bf43-5db8d9ca83da
Office            :
OfficePhone       :
PostalCode        :
SamAccountName    : jdoe
SID               : S-1-5-21-2025429266-2000478354-1606980848-16934
State             :
StreetAddress     :
Surname           : Doe
Title             :
UserPrincipalName : [email protected]
"@
$b=ConvertFrom-Csv -InputObject $a -Delimiter ':' -Header "key","value"
$c=New-Object -TypeName System.Management.Automation.PSObject 
$b|%{ $c|Add-Member -NotePropertyName $_.key -NotePropertyValue "$($_.value)"}

Resulting object looks like this

PS C:\Users\Tomasz> $c|gm




  S C:\Users\Tomasz> $c


City               : 
Country            : 
Department         : 
DisplayName        : John Doe
DistinguishedName  : CN=John Doe,CN=Users,DC=domain,DC=com
EmailAddress       : [email protected]
Enabled            : False
Fax                : 
GivenName          : John
MobilePhone        : 
Name               : John Doe
ObjectClass        : user
ObjectGUID         : cdb9a45c-80f4-4919-bf43-5db8d9ca83da
Office             : 
OfficePhone        : 
PostalCode         : 
SamAccountName     : jdoe
SID                : S-1-5-21-2025429266-2000478354-1606980848-16934
State              : 
StreetAddress      : 
Surname            : Doe
Title              : 
UserPrincipalName  : [email protected]

If this kind of solution seems like a good Idea I'll work on my answer more.
It obviously needs white spaces removal and some nicer variable names, but I trust You can get that done Yourself :)

Improve this answer

2 Comments

This doesn't seem to work when there are multiple users instances in the string. As soon as it gets to the second user, this code starts complaining about having already defined the property.
Yep - if You have multiple users - You should split the input string by any means that You determine next user (maybe there's an empty string between them) like $b=ConvertFrom-Csv -InputObject ($a.split('<yourdelimiter>')) -Delimiter ':' -Header "key","value" or something of this sort
0

The escape sequence \s matches all whitespace, including newlines. Because of that lines without a value are actually merged with the next line. Split the string at newlines, do the replacement, then merge the string array back to a single string. 

$data = $string -split '\r?\n' -replace '\s*:\s*','=' | Out-String

or make sure you don't replace line break characters:

$data = $string -replace '[\t ]*:[\t ]*', '='

Edit:

Since your input data seems to consist of multiple records, not just one, you need to split the resulting string by record, so that you have individual strings per data set. Convert each data set to a hashtable with ConvertFrom-StringData, then convert those hashtables to custom objects.

$data = $string -split '(?<=\r?\n)\r?\n' | ForEach-Object {
    $prop = $_.Trim() -split '\r?\n' -replace '\s*:\s*','=' |
            Out-String |
            ConvertFrom-StringData
    New-Object -Type PSObject -Property $prop
}

In PowerShell v3 and newer you can use the [PSCustomObject] type accelerator instead of New-Object:

$data = $string -split '(?<=\r?\n)\r?\n' | ForEach-Object {
    $prop = $_.Trim() -split '\r?\n' -replace '\s*:\s*','=' |
            Out-String |
            ConvertFrom-StringData
    [PSCustomObject]$prop
}

The resulting list of objects can then be exported to a CSV.

Improve this answer

3 Comments

I don't want a string full of user data, so I left off the | Out-String. I am left with an array in $data.
@StackExchangeGuy Frankly, I don't care what you want. You need the data in a single string (more precisely one string per record if your input data has multiple record, which you didn't show in your original question), so that you can use ConvertFrom-StringData to convert the string to a hashtable, and then New-Object to convert the hashtable to a custom object.
Don't be upset. I was just saying that I left off a bit of your code when I used your answer to try to address my situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.