HOW TO output the values of an array to a text file in a specified format

Question

I'm using DBATools Module Invoke-DBQQuery ( based on Invoke-SQLCMD2) to query a SQL database. The query returns single record as a PSObject called $Results that looks like this...

FileName       : C12345
BADGENUMBER    : BADGENUMBER=12345
LASTNAME       : LASTNAME=SMITH
FIRSTNAME      : FIRSTNAME=JOHN
CIA            : CIA=YES
SOCIALSECURITY : SOCIALSECURITY=999999999
DACDATE        : DACDATE=07/16/2022
UIC            : UIC=42158

I need to output this PSObject to a TXT file with just the values no Field Titles one field on each row. that looks like this...

C12345
BADGENUMBER=12345
LASTNAME=SMITH
FIRSTNAME=JOHN
CIA=YES
SOCIALSECURITY=999999999
DACDATE=07/16/2022
UIC=42158

How do I go about producing the test file in the format I need?

$Results| Out-File c:\test.test.txt

produces the first output I listed.

Appreciate any assistance anyone can provide.

-MARK-

Isn't this just going to ouput 12345 withoout the BADGENUMBER= ? — Walter Mitty, Jan 09 '20 at 18:50

score 0 · Answer 1 · answered Dec 10 '21 at 05:15

Here some different angles to solve

$result = Invoke-DbaQuery -SqlInstance $sql -Query 'select * from MyTable' -As PSObject

# Simulate a 2 rows result
$result = [PSCustomObject]@{
    FileName       = 'C12345'
    BADGENUMBER    = 'BADGENUMBER=12345'
    LASTNAME       = 'LASTNAME=SMITH'
    FIRSTNAME      = 'FIRSTNAME=JOHN'
    CIA            = 'CIA=YES'
    SOCIALSECURITY = 'SOCIALSECURITY=999999999'
    DACDATE        = 'DACDATE=07/16/2022'
    UIC            = 'UIC=42158'
},[PSCustomObject]@{
    FileName       = 'C12345'
    BADGENUMBER    = 'BADGENUMBER=12345'
    LASTNAME       = 'LASTNAME=SMITH'
    FIRSTNAME      = 'FIRSTNAME=JOHN'
    CIA            = 'CIA=YES'
    SOCIALSECURITY = 'SOCIALSECURITY=999999999'
    DACDATE        = 'DACDATE=07/16/2022'
    UIC            = 'UIC=42158'
}
# Using RegEx -replace to remove the front part
($result | Out-String) -replace '(?m)^[\w\s]+:\s','' | Out-File C:\Temp\test1.txt

# Looping for every properties (they will get reordered by property name)
# I just saw Mathias suggestion and it would produce the same result
$result | Get-Member -Type NoteProperty | ForEach-Object {$result.($_.Name)} | Out-File C:\Temp\test2.txt

# Simplier solution
$output = foreach ($row in $result) {
    $row.FileName
    $row.BADGENUMBER
    $row.LASTNAME
    $row.FIRSTNAME
    $row.CIA
    $row.SOCIALSECURITY
    $row.DACDATE
    $row.UIC
    "" # you probably want to have an empty row seperating each record
}
$output | Out-File C:\Temp\test3.txt

HOW TO output the values of an array to a text file in a specified format

1 Answers1