How to import an array of PSObjects from a text file?

Question

This is simple enough:

PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> ls .\test.csv


    Directory: C:\Users\saunders\Desktop\data


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2023-01-31   1:38 PM            640 test.csv


PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> cat .\test.csv
UserPrincipalName,"DisplayName","Title","UserType","IsLicensed"
[email protected],"Lee Gu","jr engineer","Member","True"
[email protected],"Megan Bowen","recruiter","Member","True"
[email protected],"Grady Archie","sr engineer","Member","True"
[email protected],"Miriam Graham","Director","Member","True"
[email protected],"openmailbox",,"Member","False"
[email protected],"Johanna Lorenz","Senior Engineer","Member","True"
[email protected],"Joni Sherman","recruiter","Member","False"
[email protected],"Alex Wilber","Marketing Assistant","Member","True"
[email protected],"Isaiah Langer","Sales Rep","Member","True"
PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> $test = Import-CSV .\test.csv
PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> $test[3]


UserPrincipalName : [email protected]
DisplayName       : Miriam Graham
Title             : Director
UserType          : Member
IsLicensed        : True



PS C:\Users\saunders\Desktop\data>

But how would CSV formatted data be obtained from a formatted text file?

PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> $records = Get-Content .\records.txt
PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> $records


UserPrincipalName : [email protected]
DisplayName       : Lee Gu
Title             : jr engineer
UserType          : Member
IsLicensed        : True

UserPrincipalName : [email protected]
DisplayName       : Megan Bowen
Title             : recruiter
UserType          : Member
IsLicensed        : True

UserPrincipalName : [email protected]
DisplayName       : Grady Archie
Title             : sr engineer
UserType          : Member
IsLicensed        : True

UserPrincipalName : [email protected]
DisplayName       : Miriam Graham
Title             : Director
UserType          : Member
IsLicensed        : True

UserPrincipalName : [email protected]
DisplayName       : openmailbox
Title             :
UserType          : Member
IsLicensed        : False

UserPrincipalName : [email protected]
DisplayName       : Johanna Lorenz
Title             : Senior Engineer
UserType          : Member
IsLicensed        : True

UserPrincipalName : [email protected]
DisplayName       : Joni Sherman
Title             : recruiter
UserType          : Member
IsLicensed        : False

UserPrincipalName : [email protected]
DisplayName       : Alex Wilber
Title             : Marketing Assistant
UserType          : Member
IsLicensed        : True

UserPrincipalName : [email protected]
DisplayName       : Isaiah Langer
Title             : Sales Rep
UserType          : Member
IsLicensed        : True

PS C:\Users\saunders\Desktop\data>

So that the data for each record is transposed and then written to a row in a CSV file. No doubt there's a term for this inverse operation. It doesn't have to be CSV per se, it's just that the above sample originates as CSV.

Please do correct any terminological errors.

as it stands, no the $records object cannot itself be directly exported back to CSV with:

PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> $records = Get-Content .\records.txt
PS C:\Users\saunders\Desktop\data>
PS C:\Users\saunders\Desktop\data> Export-Csv $records
Export-Csv : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'Path'. Specified method is not supported.
At line:1 char:12
+ Export-Csv $records
+            ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.PowerShell.Commands.ExportCsvCommand

PS C:\Users\saunders\Desktop\data>

as it will first have to parsed somehow.

Are you able to re-export the data in the correct format using `Export-Csv` ? — Santiago Squarzon, Jan 31 '23 at 22:08
no @SantiagoSquarzon and I added the error. Bear in mind that it's originating as a text file, so it's almost a RegEx parsing question. But, I would think, a bit different...? — Nicholas Saunders, Jan 31 '23 at 22:13
Yes, you need text parsing to convert that into objects. Which makes me sad this was exported this way to begin with :( — Santiago Squarzon, Jan 31 '23 at 22:14
Leaving open for elaboration on `help ConvertFrom-StringData` versus `help ConvertFrom-String` as is relevant to the question. When all you have is a hammer everything looks like a nail. There's more than one way to skin this cat. — Nicholas Saunders, Feb 01 '23 at 06:15

mklement0 · Answer 1 · 2023-02-05T03:32:55.887

The text format your showing is a for-display format, as produced by PowerShell's Format-List cmdlet.

As such, it isn't meant for programmatic processing, so there's no standard cmdlet that can parse that format.

For programmatic processing, a structured text format should be used, such as JSON or CSV, or - for the most structural flexibility and best (albeit limited) type fidelity - PowerShell's XML-based CLIXML format, as produced by Export-Clixml and understood by Import-Clixml.

If you're stuck with the given format, you'll have to do your own parsing; e.g.:

# Outputs [pscustomobject] instances that can be exported to CSV, for instance.
((Get-Content -Raw records.txt) -replace ':', '=') -split '\r?\n\r?\n' | 
  ConvertFrom-StringData | 
  ForEach-Object { [pscustomobject] $_ }

The above is relatively simple, but:

assumes that none of the property values contain : (this could be worked around) and that all values are single-line; also, the values are subject to interpretation of \ chars. as the start of escape sequence (see cmdlet link in next bullet point).
has one unavoidable drawback: because ConvertFrom-StringData outputs inherently unordered hashtables, the property order isn't preserved.
- Potentially fixing this problem in a future PowerShell version (post-7.3.2, current as of this writing) is the subject of GitHub issue #19070

If maintaining the property order is important, and you know the property names, you can pipe to a Select-Object call with the property names listed in the desired order as its -Property argument.

Otherwise, you'll have to parse the text-file lines yourself.

Note: I suggest avoiding the obsolescent, Windows-only ConvertFrom-String cmdlet, despite its allure:

It provides separator-based parsing as well as heuristics-based parsing based on templates containing example values.
The separator-based parsing applies automatic type conversions that you cannot control, and the template language is poorly documented, with the exact behavior being hard to predict (as is unavoidable in a heuristics-based solution).

Oh, I agree it's a terrible idea to have data like this! – Nicholas Saunders Jan 31 '23 at 23:23 — Nicholas Saunders, Jan 31 '23 at 23:23

score 3 · Accepted Answer · answered Feb 01 '23 at 00:50

3

With Windows PowerShell specifically, you have the option of using ConvertFrom-String's template-based parsing capability:

# define a template using the data from 2 consecutive records from the input data
$recordTemplate = @'
UserPrincipalName : {UserPrincipalName*:[email protected]}
DisplayName       : {DisplayName:Lee Gu}
Title             : {Title:jr engineer}
UserType          : {UserType:Member}
IsLicensed        : {IsLicensed:True}

UserPrincipalName : {UserPrincipalName*:[email protected]}
'@

# ConvertFrom-String will use the example(s) in the template to infer the format of the remaining records
Get-Content .\records.txt | ConvertFrom-String -TemplateContent $recordTemplate

answered Feb 01 '23 at 00:50

Mathias R. Jessen

157,619
12
148
206

I'm looking at `help ConvertFrom-StringData` now. – Nicholas Saunders Feb 01 '23 at 06:14
1

@NicholasSaunders That's a different cmdlet :) – Mathias R. Jessen Feb 01 '23 at 11:08
1

It is available in pwsh Core via implicit remoting – Santiago Squarzon Feb 01 '23 at 12:20

score 0 · Answer 3 · answered Jan 31 '23 at 23:22

While not fully realized, something like

wsl >
wsl > pwd
/mnt/c/WINDOWS/system32/awk
wsl >
wsl > awk '{print $1}' input.txt | datamash transpose
A       B       C       D
wsl >
wsl > awk '{print $2}' input.txt | datamash transpose
2014    2013    2014    2014
wsl >
wsl > awk '{print $3}' input.txt | datamash transpose
1002    990     2030    599
wsl >
wsl > cat input.txt
A        2014   1002
B        2013    990
C        2014   2030
D        2014    599
wsl >

where the matrix is transposed one line at a time per record.

The output above, when concatted, would be:

A       B       C       D
2014    2013    2014    2014
1002    990     2030    599

Which, at first glance at least, would be easily imported as CSV. Not a PowerShell solution, however.

How to import an array of PSObjects from a text file?

3 Answers3

Linked

Related