Import Column

PowerShell Help
1

I have a 2 CSV and I want to import 3 columns (FoundInAD,Results,Uptime) from CSV2 to CSV1 and I do not know how to do it, hoping that someone could help on this.

CSV1

ComputerName PatchingStyle LastSyncTime LastSyncResult LastReportedStatusTime PendingReboot NeededCount Compliant
Server1 7Saturday9PM 4/15/2020 18:31 Succeeded 4/15/2020 19:24 0 0 100
CSV2
ServerName Uptime Results FoundInAD
Server1 241 Days; 14 Hrs; 6 Mins; 47 Secs Up YES
I tried this code but it is not working, am getting .
$csv1 = Import-Csv -Path C:\BackUp\PScripts\SDO\7Saturday9PM.csv $csv2 = Import-Csv -Path C:\BackUp\PScripts\SDO\Report.csv | Select -Property FoundInAD,Results,Uptime

$Results = foreach($item1 in $csv1){
foreach($item2 in $csv2){
[PSCustomObject]@{
ComputerName = $item1.ComputerName
PatchingStyle = $item1.PatchingStyle
LastSyncTime = $item1.LastSyncTime
LastSyncResult = $item1.LastSyncResult
LastReportedStatusTime = $item1.LastReportedStatusTime
PendingReboot = $item1.PendingReboot
NeededCount = $item1.NeededCount
InstalledOrNA = $item1.InstalledOrNA
FoundInAD = $item2.FoundInAD
Results = $item2.Results
Uptime = $item2.Uptime
}
}
}
$Results | Out-GridView

2

Edel, welcome to Powershell.org. Please take a moment and read the very first post on top of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!.

When you post code or error messages or sample data or console output format it as code using the code tags “PRE”, please. Thanks in advance.

I assume you want to combine both CSVs based on the columns $CSV1.ComputerName and $CSV2.ServerName, right? You cannot use a nested loop for that. You have to match the current element of one of the arrays to the according one of the other array … like this maybe:

$csv1 = Import-Csv -Path C:\BackUp\PScripts\SDO\7Saturday9PM.csv
$csv2 = Import-Csv -Path C:\BackUp\PScripts\SDO\Report.csv | Select-Object -Property ServerName, FoundInAD, Results, Uptime

$Results = foreach ($item1 in $csv1) {
    $item2 = $csv2 | Where-Object { ($item1.ComputerName -split '\.')[0] -eq $_.ServerName }

    [PSCustomObject]@{
        ComputerName           = $item1.ComputerName
        PatchingStyle          = $item1.PatchingStyle
        LastSyncTime           = $item1.LastSyncTime
        LastSyncResult         = $item1.LastSyncResult
        LastReportedStatusTime = $item1.LastReportedStatusTime
        PendingReboot          = $item1.PendingReboot
        NeededCount            = $item1.NeededCount
        InstalledOrNA          = $item1.InstalledOrNA
        FoundInAD              = $item2.FoundInAD
        Results                = $item2.Results
        Uptime                 = $item2.Uptime
    }
}
$Results | Out-GridView
3

Hi Olaf,

Thanks for the quick response I tried the code you shared and am getting blank values for FoundInAD,Results and Uptime.

Anyways the ComputerName from csv1 is FQDN format while the server name in CSV2 is hostname only.

 

 

4

I could not recognize that in your sample data. So you have to convert the FQDN to a hostname to make it comparable to the hostname from your CSV2. Do you need help with that?

5

[quote quote=219510]Hi Olaf,

Thanks for the quick response I tried the code you shared and am getting blank values for FoundInAD,Results and Uptime.

Anyways the ComputerName from csv1 is FQDN format while the server name in CSV2 is hostname only.

[/quote]

It works fine if I change the ServerName to FQDN in csv2.

6

[quote quote=219510]Hi Olaf,

Thanks for the quick response I tried the code you shared and am getting blank values for FoundInAD,Results and Uptime.

Anyways the ComputerName from csv1 is FQDN format while the server name in CSV2 is hostname only.

[/quote]

It works fine if I change the ServerName to FQDN in csv2, but I don’t wanna do that, is there any other way?

7

I actually already updated the code I posted above. Try it. :wink:

8

Hi Olaf,

yes, if there is other way without changing the servername to FQDN… I use the hostname to find the device in AD, if I change that to FQDN the value from FoundInAD will be the issue.

9

Thank you so much Olaf, appreciate the help… It is working fine now, been trying to work on this for more than 3 weeks. lol

10

So you should have asked this 2 weeks ago. :wink:

11

Hi Olaf,

One more favor, I did export-csv and I got the output.csv is there a way to convert this to HTML without changing the format of columns?

 

12

Hmmm … did you try ConvertTo-Html?

Please read th ecomplete help including the examples to learn how to use it.