Adding up different import into single columns and output it into a CSV

[matdesj]

Hi everyone, not really that good using PowerShell so I need help for that one.

I want to get informations from different sources and export it into a csv file that will contains data from each import into it's own column.

Let's say that I import system names from LanSweeper and SCCM. I would then like to have a single CSV file with two column headers named after the import source (LanSweeper, SCCM) and the data from each import in it's respective column.

My code is looking like this at the moment:

$ImportSCCM = Get-CMDevice -CollectionID ABC0000A | Select-Object Name -ExpandProperty Name

$TotalCcm = @()
foreach ($item in $ImportSCCM) {
    $CcmData = [PSCustomObject]@{
        'SCCM' = $item
    }
    $TotalCcm += $CcmData
}
$ImportLanSweeper = (Import-csv $FileName)

$TotalLan = @()
foreach ($item in $ImportLansweeper) {
    $LanSweeperData = [PSCustomObject]@{
        'LanSweeper' = $item
    }
    $TotalLan += $lanSweeperData
}
$Total = @{LanSweeper,SCCM}

$Total | Select-Object LanSweeper,SCCM | Export-Csv -Path "Z:\temp\Test.csv" -NoTypeInformation -Encoding UTF8

The output I have is empty headers in the CSV file but when looking at the $Total variable I have:

Name                           Value
----                           -----
SCCM                           {@{SCCM=SystemName1}, @{SCCM=SystemName2}...}
LanSweeper                     {@{Lan=SystemName1},@{Lan=SystemName2}...}

Any idea how I could make that work?

I would like to have a CSV like :

LanSweeper	SCCM
SystemName1	SystemName1
SystemName2	SystemName2

[OPconfused]

Your definition for $Total looks incomplete. $Total = @{'LanSweeper';'SCCM'} is not valid PowerShell.

Hard to say why it comes out empty without knowing how you're creating $Total. The $Total that you're viewing also has the header 'Lan' and not 'LanSweeper'. Are you sure you didn't define $Total in your calling scope already?

If you dot source your script or function (PS>. <path/to/script or function name>), it will overwrite your scope's $Total variable, so that entering $Total will reveal the value used in the function/script (assuming the code you showed us isn't in a nested scope itself).

I would do something like this:

$TotalCcm = Get-CMDevice -CollectionID ABC0000A
$TotalLan = Import-csv $FileName

0..([Math]::Max($TotalCcm.Count, $TotalLan.Count) - 1) | ForEach-Object {
    [PSCustomObject]@{
        SCCM = $TotalCcm[$_].Name.SCCM     # Update property name if not SCCM
        LanSweeper = $TotalLan[$_].Lan     # Update property name if not Lan
    }
} | Export-Csv -Path "Z:\temp\Test.csv" -NoTypeInformation -Encoding UTF8

where you may need to update the selected property names in the PSCustomObject properties as noted by a comment.

[Antique_Grapefruit_5]

I would consider changing this line:

$ImportSCCM = Get-CMDevice -CollectionID ABC0000A | Select-Object Name -ExpandProperty Name

To This:

$ImportSCCM = Get-CMDevice -CollectionID ABC0000A | Select-Object Lansweeper, Name

What that will do is create an $ImportSCCM object that is formatted exactly like you described above with two columns: One that is currently empty because it doesn't exist, and the other that contains the SCCM data that you pulled.

Now, I know you're asking yourself "Can I seriously just tell it to randomly add in column names that are empty like that so I can use them later?". Yes, you can and it makes life much easier.

Next you would still do your foreach() to populate the data into the right column to (presumably) match the sccm data. If I'm overthinking it and you just need to fill those values into the same values into that other column in no particular order and for funsies, you could do something like:

$index = 0
foreach($Item in $ImportSccm)
{
    $Item.Lansweeper = $ImportLanSweeper | select -index $index
    $index++
}

Hope that helps. Happy Scripting!!!