Using Variables in CSV

Question

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?

give us an example of $1nclient, $2nclient and $3nclient and the output you expect. — Martin Brandl
– Martin Brandl, Commented Sep 20, 2016 at 19:02
Have these variables been populated before you define the multiline string? Also, $input is an automatic variable that shouldn't be used as a regular variable. — Ansgar Wiechers
– Ansgar Wiechers, Commented Sep 20, 2016 at 19:05
@MartinBrandl the examples of all three variables would be a varied list of computers names: $1nclient = hi.microsoft.com, hello.microsoft.com. --- $2nclient = [email protected], [email protected], [email protected] --- $3nclient = [email protected] — Justin Beagley
– Justin Beagley, Commented Sep 20, 2016 at 19:15
With $1nclient, $2nclient and $3nclient defined before $buildcsv I see the values in the output (although not in the format you expect). If you don't you either define the 3 lists after $buildcsv, or you have something in your code that you didn't tell us about. — Ansgar Wiechers
– Ansgar Wiechers, Commented Sep 20, 2016 at 19:30
Oh! You are trying to transpose the arrays. Ok, that's do-able, but not simple. Pretty sure I've posted an answer for this before, let me see if I can find it. — TheMadTechnician
– TheMadTechnician, Commented Sep 20, 2016 at 19:42

Martin Brandl · Accepted Answer · 2016-09-20 19:58:35Z

4

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]

edited Sep 20, 2016 at 19:58

answered Sep 20, 2016 at 19:52

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

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

2 Comments

Justin Beagley Over a year ago

Thanks for the response. But isn't there a more efficient way of populating a column for csv file? What if i created a excel.application object, and a sheet and ran a foreach loop to populate each cell in a column?

Justin Beagley Over a year ago

Also - this code works, but i'm getting added info in the $objects variable. To explain more, I'm populating the $1nclient variables from an imported CSV report. So when i run your code i'm actually getting @{[email protected]} where display_client_name is the name of the header from the imported csv.

Matt · Accepted Answer · 2016-09-21 10:30:44Z

2

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.

edited Sep 21, 2016 at 10:30

answered Sep 20, 2016 at 20:18

Matt

47.1k9 gold badges90 silver badges125 bronze badges

2 Comments

Justin Beagley Over a year ago

this code works, but i'm getting added info in the $objects variable. To explain more, I'm populating the $1nclient variables from an imported CSV report. So when i run your code i'm actually getting @{[email protected]} where display_client_name is the name of the header from the imported csv. –

Matt Over a year ago

You need to expand the property. Just add $1nclient [$_].display_client_name. @justinbeagley

Collectives™ on Stack Overflow

Using Variables in CSV

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related