Powershell - DataRow to an Array

Question

I have the result of a SQL query which looks like this:

$result1 | get-member

TypeName: System.Data.DataRow

Name   MemberType  Definition                                                         
----   ----------  ----------                                                         
. . .
DATE   Property    System.DateTime DATE {get;set;}                              
FCO    Property    System.String FCO {get;set;}                        
LS     Property    System.String LS {get;set;}                                
TCO    Property    System.UInt32 TCO {get;set;}


$result1
#multiple lines similar to this
DATE : 29/09/2015 00:00:00
LS   : c-am1
TCO  : 8059
FCO  : 0

I need to get this into an array thus:

$Data = @"
Date, LS, TCO, FCO
[the contents of the $result1 object goes here under the relevant headings]
[example]
29/09/2015,c-am1,8059,0
29/09/2015,c-am2,5985,3
"@

This is so I can pass it to the ConvertTo-AdvHTML function https://community.spiceworks.com/scripts/show/2448-create-advanced-html-tables-in-powershell-convertto-advhtml

$Data = $Data | ConvertFrom-Csv
$HTML = $Data | ConvertTo-AdvHTML

I'm sure there is an easy way to do this, but can't find it. How can I do this?

Your $Data example isn't an array, it's a herestring.

Alexander Obersht
– Alexander Obersht

2015-09-30 16:30:46 +00:00
Commented Sep 30, 2015 at 16:30 — Alexander Obersht
– Alexander Obersht, Commented Sep 30, 2015 at 16:30

Dave Sexton · Accepted Answer · 2015-09-30 16:43:08Z

2

You say you want to put your data into an array but then show an example of your data in a string. If you are doing this to follow the example on the link to ConvertTo-AdvHTML then it is an unnecessary step because you already have an array of objects in result1. All you need to do is this;

$HTML = $result1 | ConvertTo-AdvHTML

You just need to add to the above code any switches to ConvertTo-AdvHTML. The first step in the example from your link was only so the author could show a self contained example.

answered Sep 30, 2015 at 16:43

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

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

1 Comment

IGGt Over a year ago

Cheers, but $HTML = $result1 | ConvertTo-AdvHTML caused the script to hang. I tried using foreach $res in $result1{..etc..} to create the $data 'string', and that created the table, but with one element per row, each containing the number 58? It was only when I added the $Data = $Data | ConvertFrom-Csv that it worked as expected.

IGGt · Accepted Answer · 2015-10-01 08:16:56Z

0

The way I eventually got this to work was:

$Data = @()
$Data += "DATE,LS,TCO,FCO"

$a=0
foreach($res in $result1)
    {
        $ed = $res.DATE
        $ls = $res.LS
        $tc = $res.TCO
        $fc = $res.FCO

        $Data += [string]"[row:white]$ed,$ls,$tc,$fc"

        $a++
    }

$Data = $Data | ConvertFrom-Csv
$html = $Data | ConvertTo-AdvHTML

answered Oct 1, 2015 at 8:16

IGGt

2,79710 gold badges46 silver badges68 bronze badges

Collectives™ on Stack Overflow

Powershell - DataRow to an Array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related