Convert a returned string in PowerShell to delimited data and export

Question

I am using PowerShell to remotely configure storage arrays from various vendors. The script allows me to connect to the arrays with no issue. From there I run commands using REST API calls or remote SSH via plink.exe. Here is my issue. When using plink, I need to query the array and then perform operations conditionally based on the output. The issue is that the output is returned in string format. This is causing a problem for me because I would like to sort and extract portions of the returned string and present users with options based on the output.

Example - List Volumes

if ($sel_vendor -eq 3){ $ibm_ex_vols = & $rem_ssh $rem_ssh_arg1 $rem_ssh_arg2 $array_user"@"$array_mgmt_ip "-pw" $readpass "lsvdisk" foreach ($i in $ibm_ex_vols){ write-host $i } }

Here is the output of the code

id name  IO_group_id IO_group_name status mdisk_grp_id mdisk_grp_name  capacity type    FC_id FC_name RC_id RC_name vdisk_UID                        fc_map_count copy_count fast_write_state se_copy_count RC_change compressed_copy_count parent_mdisk_grp_id parent_mdisk_grp_name
0  Test1 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074B 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
1  Test2 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074C 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
2  Test3 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074D 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
3  Test4 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074E 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL

What I would like to be able to do is store this info and then select the headers and data from the id and name columns. I was able to output the data to a txt file using the out-file command. Once I did that I used Excel to convert it to a delimited file using the fixed with delimiter option. While this worked, I need to figure out a dynamic solution.

Since the width of the columns will change depending on how long various values are (for example a system with a long name making the name column longer than usual), what I would do is perform a RegEx match to capture each header listing and include spaces, then use the length of the headers to construct a width delimited regex match that could break up each line into a PSCustomObject with values, and you'd be set to export to CSV or do whatever you wanted. — TheMadTechnician
– TheMadTechnician, Commented Oct 14, 2015 at 16:39

user4003407 · Accepted Answer · 2015-11-11 13:13:02Z

2

Here is a simple parsing function, which can split your data and produce custom object with properties to work with:

function Parse-Data{
    begin{
        $Headers=$null
    }
    process{
        if(!$Headers){
            $Headers=
            [Regex]::Matches($_,'\S+')|
            ForEach-Object {
                $Header=$null
            } {
                if($Header){
                    $Header.SubArgs+=$_.Index-1-$Header.SubArgs[0]
                    $Header
                }
                $Header=[PSCustomObject]@{
                    Name=$_.Value
                    SubArgs=,$_.Index
                }
            } {
                $Header
            }
        }else{
            $String=$_
            $Headers|
            ForEach-Object {
                $Object=[ordered]@{}
            } {
                $Object.Add($_.Name,$String.Substring.Invoke($_.SubArgs).TrimEnd())
            } {
                [PSCustomObject]$Object
            }
        }
    }
}

And this is how you can invoke it:

$ibm_ex_vols=@'
id name  IO_group_id IO_group_name status mdisk_grp_id mdisk_grp_name  capacity type    FC_id FC_name RC_id RC_name vdisk_UID                        fc_map_count copy_count fast_write_state se_copy_count RC_change compressed_copy_count parent_mdisk_grp_id parent_mdisk_grp_name
0  Test1 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074B 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
1  Test2 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074C 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
2  Test3 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074D 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
3  Test4 0           io_grp0       online 0            SVC_SYSTEM_POOL 10.00GB  striped                             600507680C80004E980000000000074E 0            1          empty            1             no        0                     0                   SVC_SYSTEM_POOL
'@-split'\r?\n'

$ibm_ex_vols|Parse-Data

edited Nov 11, 2015 at 13:13

answered Oct 14, 2015 at 18:06

user4003407

22.3k4 gold badges54 silver badges62 bronze badges

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

7 Comments

Steve Kehrer Over a year ago

You have the data wrapped in @' and '@. In my code however this is not a static block of info. This is a string stored in $ibm_ex_vols. Can I simply replace the text with this: @' $ibm_ex_vols '@

user4003407 Over a year ago

@SteveKehrer You can use variable here. I made an edit.

Steve Kehrer Over a year ago

This works well, but I was hoping for abbreviated output with only the id and name columns.

user4003407 Over a year ago

@SteveKehrer You can simply add some of Format-* cmdlets to not display not needed data: $ibm_ex_vols|Parse-Data|Format-Table id,name.

Steve Kehrer Over a year ago

Brilliant! This works perfectly and should allow me to use the parse function for any data I return from the array. Thanks for all the suggestions. I am really looking forward to gaining more advanced knowledge of PowerShell.

|

Alexander Obersht · Accepted Answer · 2015-10-15 05:30:37Z

1

Here's an even simpler, math-based solution (assuming that $ibm_ex_vols contains the output as a collection of strings):

$sOutFile = "outfile.csv"

# Splitting the headers line into chars.
$cChars = $ibm_ex_vols[0] -split ''

$cInsertIndices = @()
$j = 0
for ($i = 1; $i -lt $cChars.Count; $i++) {
    # If previous character is a whitespace and the current character isn't
    if ( ($cChars[$i - 1] -eq ' ') -and ($cChars[$i] -ne  ' ') ) {
        # we'll insert a delimiter here
        $cInsertIndices += $i + $j - 1
        $j++ # and each insert will increase the line length.
    }
}

foreach ($sLine in $ibm_ex_vols) {
    foreach ($i in $cInsertIndices) {
        # Adding delimiter.
        $sLine = $sLine.Insert($i, ',')
    }

    # Optionally we can also trim trailing whitespaces:
    # $sLine = $sLine -replace '\s+(?=,)'
    $sLine | Out-File -FilePath $sOutFile -Append
}

Of course here we don't do any actual parsing and hence don't get convenient PSObjects to work with.

Finally, if we could be sure that all data fields will be populated and won't contain any whitespace characters, we wouldn't need to rely on field width and could further simplify our code to something like this:

$sOutFile = "outfile.csv"

foreach ($sLine in $ibm_ex_vols) {
    $sLine = $sLine -replace '\s+', ','
    $sLine | Out-File -FilePath $sOutFile -Append
}

edited Oct 15, 2015 at 5:30

answered Oct 14, 2015 at 19:34

Alexander Obersht

3,2832 gold badges25 silver badges26 bronze badges

4 Comments

user4003407 Over a year ago

Here some oneliner based on your idea: $ibm_ex_vols-replace"(?<=^($(([Regex]::Matches($ibm_ex_vols[0],' \S').Index|%{".{$_}"})-join'|'))) ",','.

Steve Kehrer Over a year ago

So that is a nice short way to get comma separated output. The result is that it prints that output to the screen which is not what I was shooting for. What I really need is to be able to take that string and parse out the ID and Name headers and values and ideally display in a table. I can do this easily if I can export the data to a CSV, import and then use select-object. I was hoping to avoid the import export route if possible.

Alexander Obersht Over a year ago

@PetSerAl Nice one. Looks like a perl hack though. ;) I think retaining some readability is more practical even at the cost of some extra lines.

user4003407 Over a year ago

@SteveKehrer You can pipe strings to ConvertFrom-Csv, and get objects without exporting and importing.

Collectives™ on Stack Overflow

Convert a returned string in PowerShell to delimited data and export

2 Answers 2

7 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related