CODEWITHSUNDEEP
powershell
Justin Beagley
Justin Beagley

Reputation: 299

Using Variables in CSV

I have 3 variables. Each variable is an array of users. I'm trying to create a CSV in PowerShell that contains these 3 arrays in separate headers, but the code I'm using isn't working.

Here's the code: 

$buildcsv = @"
1st Notification, 2nd Notification, 3rd Notification
$1nclient, $2nclient, $3nclient
"@

$excsv = ConvertFrom-Csv $buildcsv

However the output only gives me the header, not the related list of users. 

PS> $excsv

1st Notification 2nd Notification 3rd Notification
---------------- ---------------- ----------------

The output that I expect would look like this: 

1st Notification     2nd Notification       3rd Notification
----------------     ----------------       ----------------
hi.microsoft.com     [email protected]      [email protected]
hello.microsoft.com  [email protected]
                     [email protected]

Is there a reason why a variable of System.Array type wouldn't work?

Upvotes: 2

Views: 442

Answers (2)

Matt
Matt

Reputation: 46710

CSV Objects are not built in the way you want as discovered in other answers and comments. Normally each row must be its own object that has the same properties as each row before it. We are not making a true CSV here but a collection of lists.

I wanted to show that PowerShell is forgiving with array notation as well as another method to determine how many rows you have. 

$1nclient = @("hi.microsoft.com", "hello.microsoft.com")
$2nclient = @("[email protected]", "[email protected]", "[email protected]")
$3nclient = @("[email protected]")

$maximumEntries = ($1nclient,$2nclient,$3nclient | Measure-Object -Property Count -Maximum).Maximum

0..($maximumEntries - 1) | ForEach-Object{
    [pscustomobject][ordered]@{
        '1st Notification' = $1nclient[$_]
        '2nd Notification' = $2nclient[$_]
        '3rd Notification' = $3nclient[$_]
    }
}

Calling a nonexistent array element in PowerShell will return a null. So there is little coding need to account for that. Also don't need to build an array. Just use the pipeline. You can tack on Export-CSV on the end of this if you wanted to.

note that it is important we guarantee array types with something like @() else PowerShell will treat the string itself as an array and return single letters from positions in a string instead of the first element.

Upvotes: 2

Martin Brandl
Martin Brandl

Reputation: 58931

Unfortunately this is not how PowerShell works since you have a list of columns with there values instead of a list of rows (records). So you have to transpose your column array into a collection of rows:

$1nclient = @("hi.microsoft.com", "hello.microsoft.com")
$2nclient = @("[email protected]", "[email protected]", "[email protected]")
$3nclient = @("[email protected]")

$biggestArray = [Math]::Max($1nclient.Count, ([Math]::Max($2nclient.Count, $3nclient.Count)))

$objects = @()

for ($i = 0; $i -lt $biggestArray; $i++)
{
    if ($1nclient.Count -le $i) { $current1 = '' } else { $current1 = $1nclient[$i]}
    if ($2nclient.Count -le $i) { $current2 = '' } else { $current2 = $2nclient[$i]}
    if ($3nclient.Count -le $i) { $current3 = '' } else { $current3 = $3nclient[$i]}

    $objects += [PsCustomObject]@{
        '1st Notification' = $current1
        '2nd Notification' = $current2
        '3rd Notification' = $current3
    }
} 
$objects

Output:

1st Notification    2nd Notification      3rd Notification       
----------------    ----------------      ----------------       
hi.microsoft.com    [email protected]     [email protected]
hello.microsoft.com [email protected]                        
                    [email protected]

Upvotes: 4

Related Questions