0

My goal is to take multiple PS commands, export the results into individual columns.There are no relations between the data. Currently I run 15-20 individual PS scripts that each output one column of data into their own csv file. I then take each of those 15-20 csv documents and copy/paste their individual columns into a new, single spreadsheet--into their own columns. I am trying to make this process more efficient by running one script that generates one csv file that contains multiple columns with their respected results.

Here is what my final output should look like:

Here is what my current output looks like

Here is my current rendition of the code. I have tried many variations although this is the closest I've been to a working product.

$ExportPath="\\NetworkLocation\test.csv"
$ADSG1="AD_Security_Group_Name"
$OUpath='OU=Servers,DC=sample,DC=sample,DC=org'

$TST_Script1=Get-ADGroupMember $ADSG1 | Select-object -ExpandProperty Name | Out-String
$TST_Script2=Get-ADComputer -Filter * -SearchBase $OUpath | Select-object -ExpandProperty Name | Out-String

$Object = [pscustomobject]@{
    TST_Script1 = $TST_Script1
    TST_Script2 = $TST_Script2
}
$Object | Export-Csv -Path $ExportPath -NoTypeInformation
Improve this question
2
  • you need to iterate thru the collection that is largest and build a PSCO using the resulting index. Commented Aug 25, 2020 at 19:41
  • You have two disjointed collections that potentially return different object types. Both of your commands can return multiple objects. Storing each command result in a variable means that each will be a collection. Export-Csv converts the input object into text by using the property name as the header and that property's value as a data row. If there are multiple objects with property values, then you get a row per value. You have one object with one property that happens to contain an array of values. As a result, you get one data row. Commented Aug 25, 2020 at 19:46

1 Answer 1

Reset to default
1

You can do the following:

$TST_Script1=Get-ADGroupMember $ADSG1 | Select-object -ExpandProperty Name
$TST_Script2=Get-ADComputer -Filter * -SearchBase $OUpath | Select-object -ExpandProperty Name

0..(($TST_Script1.Count,$TST_Script2.Count | Sort -Desc)[0]-1) | Foreach-Object {
    [pscustomobject]@{
        TST_Script1 = $TST_Script1[$_]
        TST_Script2 = $TST_Script2[$_]
    }
} | ConvertTo-Csv -NoType

Export-Csv converts an input object's properties into column headers. The values of those properties are added as data rows aligning with the headers. When piping in a collection, each object in the collection is converted.

The issue with your attempt is you are outputting one object with two properties. Each of those properties then contains an array of values. That is different than outputting multiple objects with each having two properties and those properties contain singular values.

Out-String muddies things even more by taking an array output and converting it to a single string preserving the formatted array output.

As an aside, doing it the way you want does not make much sense to me. Both $TST_Script1 and $TST_Script2 seem disjointed as in they don't depend on each other and could contain different object types. Typically, you would want your data rows to represent objects that share schema or object type and contain properties that describe each of those objects. Often, data from different objects or commands is used to create properties for a current object. But the purpose is to better describe the object you are outputting. Your objective is to take two different, independent properties about two different objects and mash them together to appear as one object.

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

4 Comments

The overall purpose of this is to have a dataset that contains raw data from AD. I then take that data and move it into a tracking spreadsheet where I use excel functions to reference the dataset. Think of it like a excel pivot table referencing data form another source/location. This script would generate new data as progress on the project is made
The proposed solution works great if there were only two values (val1 and val2). This looks to be a limitation on [math]::Max. What if I have 6 or more values? Using my above example I have a need for at least x6 TST_Scripts1,2,3,4,5,6. This would output 6 columns in the csv document. When I update the proposed solution to include 6 variables I pickup the following error: Cannot find an overload for "Max" and the argument count: "6".I am sorry, my PS skills are growing. Thank you for the input thus far.
@Jeff I updated the loop math it to work with more than two values now.
The updated loop works great. Thank you so much for your time and effort you've put in this question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.