New to PowerShell loop throught CSV

Welcome Forums General PowerShell Q&A New to PowerShell loop throught CSV

Viewing 4 reply threads
  • Author
    Posts
    • #204993
      Participant
      Topics: 1
      Replies: 1
      Points: -5
      Rank: Member

      I have been looking at different post and articles but cant figure out how to go through a  CSV file line by line and when it see a name in lets say column F it then writes a new name in column J. I will have different names in column F that I want to write out in column J. This is what I have so far but can seem to figure out how to proceed.

      A                 B           C                   D           E        F                           G                H

      Machine  Type       OS name   Owner   Tag    Name                   Publisher Version

      Com1        srv          Windows   bob       123     Windows 2016   Microsoft 2016

      Com2       srv          Linux          Jake      456    Linux                    Redhat      6.2

      $csv = Get-ChildItem -Path “C:\temp\Landesk Server Software Inventory*.csv”
      $modifiedcsv = @()
      $importcsv = Import-Csv $csv
      # loop though rows
      foreach ($row in $importcsv) {
      #? Create logic to lookup $row.column name in your lookup table for common names
      If ($row.Type0 -eq 'Windows*') {
      $row."Name" = "$($row.name)"
      Write-Host $row."Microsoft Windows 2016 Standard".Range(k1:k1)
      } Else {
      
      }
      $row | Add-Member -MemberType NoteProperty -Name "Common Name" -Value $commonname -PassThru
        $emptyhash += $row
      
        If ($row.Type0 -eq 'Linux*') {
      $row."Name" = "$($row.name)"
      Write-Host $row."Redhat".Range(k1:k1)
      } Else {
      
      }
      $row | Add-Member -MemberType NoteProperty -Name "Common Name" -Value $commonname -PassThru
        $emptyhash += $row
      }
      $modifiedcsv | Export-csv -nti $filename
      • This topic was modified 3 months ago by grokkit. Reason: please format your code as per the forum posting instructions
    • #205056
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      It might be me but your code looks a little confusing to me. Let’s start a little smaller.

      $RawData = @'
      Machine,Type,OS name,Owner,Tag,Name,Publisher Version
      Com1,srv,Windows,bob,123,Windows 2016,Microsoft 2016
      Com2,srv,Linux,Jake,456,Linux,Redhat 6.2
      '@ |
          ConvertFrom-Csv
      
      $RawData
      
      $RawData | 
          Select-Object -Property *, 
              @{
                  Name       = 'CommonName';
                  Expression = { if ($_.Name -like '*Windows*') { 'Windows' }else { 'Linux' } }
              } -OutVariable Result
      
      $Result |
          Format-Table -AutoSize

      This code snippet uses a here string as input data converts it to structured csv data with ConvertTo-CSV and outputs it to the console. Then we pipe it to Select-Object and use a calculated property to check the column with the name “Name” if the content of that “cell” is like ‘*Windows*’. If it is it will output ‘Windows’. If it is not it will output ‘Linux’. Of course you can do a more sophisticated “check” if you like. 😉 Then we output the newly created data contained in the variable $Result and pipe it to Format-Table to display it to the console. Of course you can do whatever further step you like.

      I recommend to read always the complete help including the examples for all cmdlets you’re about to use to learn how to use them.

    • #205155
      Participant
      Topics: 5
      Replies: 321
      Points: 436
      Helping Hand
      Rank: Contributor
      $RawData = @'
      Machine,Type,OS name,Owner,Tag,Name,Publisher Version
      Com1,srv,Windows,bob,123,Windows 2016,Microsoft 2016
      Com2,srv,Linux,Jake,456,Linux,Redhat 6.2
      '@
      
      $RawData | ConvertFrom-Csv |
      Select-Object *,@{n='CommonName';exp={
      # Add to switch statement if more OSNames are present
          switch ($_.Name)
          {
              {$_ -match '^windows'}{'Windows'}
              {$_ -match '^linux'}{'Linux'} 
              {$_ -match 'redhat linux'}{'Redhat'}    
          }
          }} 
      
    • #205332
      Participant
      Topics: 0
      Replies: 2
      Points: 12
      Rank: Member

      I may have misunderstood your original question slightly. But if all you want to do is move data/add data in a csv this should do the trick. Just modify the table to take into account any new headings you want. 

      $ImportCSV = Import-CSV "C:\temp\Landesk Server Software Inventory CSV.csv"
      <# - Test Data
      $RawData = @'
      Machine,Type,OS name,Owner,Tag,Name,Publisher Version
      Com1,srv,Windows,bob,123,Windows 2016,Microsoft 2016
      Com2,srv,Linux,Jake,456,Linux,Redhat 6.2
      '@
      $ImportCSV = $RawData | ConvertFrom-Csv
      #>
      $ExportCSVLocation = "C:\temp\Landesk Server Software Inventory CSV New.csv"
      $Data = [System.Collections.ArrayList]@()
      ForEach ($row in $ImportCSV)
          {
          If ($row.Name -like "*Windows*" -or $row.Name -like "*Linux*")
              {
              $Data.Add([pscustomobject]@{
                  Machine = $row.Machine
                  Type = $row.Type
                  "OS Name" = $row."OS Name"
                  Owner = $row.Owner
                  Tag = $row.Tag
                  Name = "" # <- This row can be filled with whatever you want to fill the name collumn with, or leave it blank
                  "Publisher Version" = $row."Publisher Version"
                  NewName = $row.Name # <- This is where we have moved the name row to.
                  }) | Out-Null
              }
          else
              {
              $Data.Add([pscustomobject]@{
                  Machine = $row.Machine
                  Type = $row.Type
                  "OS Name" = $row."OS Name"
                  Owner = $row.Owner
                  Tag = $row.Tag
                  Name = $row.name
                  "Publisher Version" = $row."Publisher Version"
                  NewName = ""
                  }) | Out-Null
              }
          }
      $Data | Export-Csv -Path $ExportCSVLocation
      
    • #205677
      Participant
      Topics: 1
      Replies: 1
      Points: -5
      Rank: Member

      This is how I figured out the code. Thanks everyone for your input.

      $csv = Get-ChildItem -Path “C:\temp\Landesk Server Software Inventoryv2.csv”
      $modifiedcsv = @()
      $importcsv = Import-Csv $csv
      # loop though rows
      foreach ($row in $importcsv) {
      If ($row.publisher -like “Microsoft*”) {
      $row | Add-Member -MemberType NoteProperty -Name “Publisher – Common Name” -Value Microsoft -PassThru
      $modifiedcsv += $row }
      Elseif ($row.publisher  -like “ABBYY*”) {
      $row | Add-Member -MemberType NoteProperty -Name “Publisher – Common Name” -Value ABBYY -PassThru
      $modifiedcsv += $row}
      Elseif ($row.publisher  -like “Acro**”) {
      $row | Add-Member -MemberType NoteProperty -Name “Publisher – Common Name” -Value “Acro Software Inc.” -PassThru
      $modifiedcsv += $row}
         }
      $modifiedcsv | Export-csv -nti c:\temp\test.csv
Viewing 4 reply threads
  • The topic ‘New to PowerShell loop throught CSV’ is closed to new replies.