Import Column

Welcome Forums General PowerShell Q&A Import Column

Viewing 11 reply threads
  • Author
    Posts
    • #219492
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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

      • This topic was modified 1 month, 2 weeks ago by edefer6380.
    • #219504
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      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
    • #219510
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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.

       

       

    • #219513
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

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

      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?

    • #219516
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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.

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

    • #219519
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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.

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

    • #219522
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

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

      I actually already updated the code I posted above. Try it. 😉

    • #219528
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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.

    • #219534
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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

    • #219567
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      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

      So you should have asked this 2 weeks ago. 😉

    • #219681
      Participant
      Topics: 3
      Replies: 6
      Points: 38
      Rank: Member

      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?

       

    • #219693
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      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?

      Hmmm … did you try ConvertTo-Html?

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

Viewing 11 reply threads
  • You must be logged in to reply to this topic.