none
Checking a list of items against fields in a master file -- how to include in the output items where no match was found? RRS feed

  • Question

  • Hi,

    I am struggling to match a checklist full of strings against specific fields in a delimited masterfile, where

    input file #1, $masterfile, looks like (assume csv format for this case):
       col1,col2,col3,...colN
       val1,val2,val3,...colN

    input file #2, $checklist, looks like:
       string1
       string2
       stringN

    and I want output to look something like
       item      col2        col1       col6
       string1   string1     falala     hohum
       string2   string2     wishy     washy
       string3   
       string4   string4     blah       deblah

    I can do the search in PowerShell a couple different ways, and sometimes I can even format the result.
    But what I need help with is the parts where I: 
       1. Prepend the output with the search string and 
       2. Include output lines for which there was no match.

    My experience is in shell scripting and AWK, where producing the desired result is not a problem.
    But now I want to try to do it in PowerShell, in which I have just about zero fluency.

    Here is the closest I've gotten so far:

    ====================================

    $masterfile = 'masterfile_name'
    $checklist = get-content 'checklist_file'

    Import-CSV $masterlist |
    where {$checklist -eq $_.col2} |
    Format-Table -property col2, col1, col6 -AutoSize

    ====================================

    That gets me a nicely formatted table, complete with column headers

    But it doesn't let me prepend the lines with anything, and it doesn't tell me which items from the checklist are not found

    I have tried fiddling with select-string, and foreach, and parsing the header into fieldnames... but so far I'm really a fish out of water with PowerShell.

    If "where" is the way to do this, what do I need to add to it to make it do the whole job? 

    If not, can I do it with "select-string" and "foreach"?  Suggestions for getting over this hump?


    Tuesday, May 8, 2018 6:29 PM

Answers

  • If I understand: Here's one way you could accomplish it.


    $masterList = Import-Csv "masterfile.csv"
    $checkList = Get-Content "checklist.txt"
    foreach ( $name in $checkList ) {
      $outputObject = [PSCustomObject] @{
        "Lookup"            = $name
        "ADcn"              = $null
        "ADoperatingSystem" = $null
        "IBipv4addr"        = $null
        "IBSite"            = $null
      }
      $matchingRecord = $masterList | Where-Object { $_.ADcn -eq $name } | Select-Object -First 1
      if ( $matchingRecord ) {
        $outputObject.ADcn              = $name
        $outputObject.ADoperatingSystem = $matchingRecord.ADoperatingSystem
        $outputObject.IBipv4addr        = $matchingRecord.IBipv4addr
        $outputObject.IBsite            = $matchingRecord.IBsite
      }
      $outputObject
    }
    


    -- Bill Stewart [Bill_Stewart]



    Friday, May 11, 2018 7:39 PM
    Moderator

All replies

  • ViTRian,

    I read your question twice but I didn't get how your checklist and masterfile fit together. Do they have something in common? How do you come from this:

    col1,col2,col3,...colN
    val1,val2,val3,...colN
    ... and this:
    string1
    string2
    stringN
    to that:
    item      col2        col1       col6
    string1   string1     falala     hohum
    string2   string2     wishy      washy
    string3   
    string4   string4     blah       deblah
    Except of two header names there's no recognizable relation between these two data sets.


    Best regards,

    (79,108,97,102|%{[char]$_})-join''





    • Edited by BOfH-666 Tuesday, May 8, 2018 10:03 PM
    Tuesday, May 8, 2018 9:57 PM
  • Oh!  Sorry about that.  And thank you for at least trying to make sense of it.

    I went a little overboard with my data scrubbing... 

    ===================

    Inputfile "checklist" contains a list of strings (windows server names, to be exact)

    Input file "masterfile" contains a list of delimited lines (it's actually a combined report of active directory and ipam details about windows servers)

    Output should reformat masterfile lines where the second field equals a string (servername) from checklist 

    ===================

    So, trying again to characterize that:

    input file #1, $masterfile, looks something like (assume csv format for this case):

    ADdNSHostName,ADcn,,ADoperatingSystem,ADwhenChanged,IBipv4addr,IBdateEntered,IBdeviceOwner,IBsite

    server1.foo.com,server1,"Windows Server 2012 R2 Standard",20160517,10.10.10.10,friend,TPA

    server3.bar.com,server3,"Windows Server 2008 R2 Enterprise",20120302,10.10.30.10,relative,ATL

    input file #2, $checklist, looks like:

       server1

       server2

       server3

     
    and I want output to look something like (just making this up):
       Lookup   ADcn         ADdNSHostName    IBipv4addr        IBsite
       server1   server1       server1.foo.com       10.10.10.10       TPA
       server2   

       server3   server3       server3.bar.com        10.10.30.10      ATL

    Point is, I got as far as being able to make the matches and even list them out in the order I want and with a header. 

    But what I need help with is *also* being able to 1)prepend the lookup strings from the checklist and 2)show the lookup strings that no match was found for.

    Wednesday, May 9, 2018 5:13 PM
  • Point is, I got as far as being able to make the matches and even list them out in the order I want and with a header. 

    But what I need help with is *also* being able to 1)prepend the lookup strings from the checklist and 2)show the lookup strings that no match was found for.

    So, show what you have so far and with the rest we will try to help. Don't let us do all the work by ourself.  ;-)

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Thursday, May 10, 2018 2:28 PM
  • Thank you for looking -- I'd be glad of any help you feel like, but especially how to get the output to include not-matching lookup terms.

    ---------------
    The input files
    ---------------

    cat .\masterfile.csv
    ADdNSHostName,ADcn,ADoperatingSystem,ADwhenChanged,IBipv4addr,IBdateEntered,IBdeviceOwner,IBsite
    server1.foo.com,server1,"Windows Server 2012 R2 Standard",20160517,10.10.10.10,20100810,friend,TPA
    server3.bar.com,server3,"Windows Server 2008 R2 Enterprise",20120302,10.10.30.10,20101001,relative,ATL

    cat .\checklist.txt
    server1
    server2
    server3

    -------------------------------
    Setting variables for use in PS
    -------------------------------
    $masterfile = '.\masterfile.csv'
    $checklist = Get-Content '.\checklist.txt'


    -------------------------------
    Using foreach and Select-String
    -------------------------------
    Foreach ( $lookup in $checklist ) {
       select-string -Pattern $lookup -Path $masterfile
    }

    masterfile.csv:2:server1.foo.com,server1,"Windows Server 2012 R2 
    Standard",20160517,10.10.10.10,20100810,friend,TPA
    masterfile.csv:3:server3.bar.com,server3,"Windows Server 2008 R2 
    Enterprise",20120302,10.10.30.10,20101001,relative,ATL

    -- Shows the matches but not the non-matches, wraps the output in a mess, and I don't know how to control heading or fields

    --------------------------
    Using Import-CSV and Where
    --------------------------
    Import-CSV $masterfile |
    Where {$checklist -eq $_.ADcn} |
     Format-Table -property ADcn,ADdNSHostName,IBipv4addr,IBsite -AutoSize

    ADcn    ADdNSHostName   IBipv4addr  IBsite
    ----    -------------   ----------  ------
    server1 server1.foo.com 10.10.10.10 TPA   
    server3 server3.bar.com 10.10.30.10 ATL

    -- Shows the matches but not the non-matches, makes me wonder how this would work if the master file did not have handy column headers at the top


    I'd be glad of any help you feel like, but especially how to get the output to include what did NOT match.

    (86,105,107,107,105|%{[char]$_})-join''

     
    Friday, May 11, 2018 3:41 PM
  • Essentially, I want to do like the following, but in PowerShell:

     (... not striving for elegance here... just wanting to clarify with quick-and-dirty shell script what I wanted to do in PS ... I'm sure there's a way to get similar flexibility but I haven't fully figured out how yet )                        

    cat checklist.txt | while read i junk; do echo "$i,`egrep -i ",$i," masterfile.csv`"; done |\
    awk -F, '
    BEGIN { 
       printf("\n%s,%s,%s,%s,%s\n", 
          "Lookup","ADcn","ADoperatingSystem","IBipv4adddr","IBsite" )
    }
    {
       printf("%s,%s,%s,%s,%s\n", 
          $1, $3, $4, $6, $9 )
    }'

    Lookup,ADcn,ADoperatingSystem,IBipv4adddr,IBsite
    server1,server1,"Windows Server 2012 R2 Standard",10.10.10.10,TPA
    server2,,,,
    server3,server3,"Windows Server 2008 R2 Enterprise",10.10.30.10,ATL

    ... Alternatively:


    cat checklist.txt | while read i junk; do echo "$i,`egrep -i ",$i," masterfile.csv`"; done |\
    awk -F, '
    BEGIN { 
       printf("\n%-10s %-10s %-40s %-20s %s\n", 
          "Lookup","ADcn","ADoperatingSystem","IBipv4adddr","IBsite" )
    }
    {
       printf("%-10s %-10s %-40s %-20s %s\n", 
          $1, $3, $4, $6, $9 )
    }'

    Lookup     ADcn       ADoperatingSystem                        IBipv4adddr          IBsite
    server1    server1    "Windows Server 2012 R2 Standard"        10.10.10.10          TPA
    server2
    server3    server3    "Windows Server 2008 R2 Enterprise"      10.10.30.10          ATL

    Friday, May 11, 2018 5:48 PM
  • If I understand: Here's one way you could accomplish it.


    $masterList = Import-Csv "masterfile.csv"
    $checkList = Get-Content "checklist.txt"
    foreach ( $name in $checkList ) {
      $outputObject = [PSCustomObject] @{
        "Lookup"            = $name
        "ADcn"              = $null
        "ADoperatingSystem" = $null
        "IBipv4addr"        = $null
        "IBSite"            = $null
      }
      $matchingRecord = $masterList | Where-Object { $_.ADcn -eq $name } | Select-Object -First 1
      if ( $matchingRecord ) {
        $outputObject.ADcn              = $name
        $outputObject.ADoperatingSystem = $matchingRecord.ADoperatingSystem
        $outputObject.IBipv4addr        = $matchingRecord.IBipv4addr
        $outputObject.IBsite            = $matchingRecord.IBsite
      }
      $outputObject
    }
    


    -- Bill Stewart [Bill_Stewart]



    Friday, May 11, 2018 7:39 PM
    Moderator
  • Please format your code as code here in the forum. It's hard to read when there are line breaks where they don't belong.
    $MasterFile = @'
    ADdNSHostName,ADcn,ADoperatingSystem,ADwhenChanged,IBipv4addr,IBdateEntered,IBdeviceOwner,IBsite
    server1.foo.com,server1,"Windows Server 2012 R2 Standard",20160517,10.10.10.10,,friend,TPA
    server3.bar.com,server3,"Windows Server 2008 R2 Enterprise",20120302,10.10.30.10,,relative,ATL
    '@
    
    $Checklist = @(
        'server1',
        'server2',
        'server3'
    )
    
    ConvertFrom-Csv -Delimiter ',' -InputObject $MasterFile -OutVariable MasterData
    
    $ResultObject = New-Object System.Collections.Generic.List[object]
    ForEach ($Item in $Checklist) {
        $OUtObj = [PSCustomObject]@{
            Lookup        = $Item
            ADcn          = $null
            ADdNSHostName = $null
            IBipv4addr    = $null
            IBsite        = $null
        }
        if ($Item -in $MasterData.ADcn) {
            $index = $MasterData.ADcn.IndexOf($Item)
            $OUtObj.ADcn = $MasterData.ADcn[$index]
            $OUtObj.ADdNSHostName = $MasterData.ADdNSHostName[$index]
            $OUtObj.IBipv4addr = $MasterData.IBipv4addr[$index]
            $OUtObj.IBsite = $MasterData.IBsite[$index]
        }
        $ResultObject.Add($OUtObj)
    }
    $ResultObject
    You might take a step back and take a little time to start to learn the basics of Powershell from scratch. That's even free of charge and pretty entertaining when you watch the free video course at Microsoft Virtual Academy - Getting started with Powershell.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''




    • Edited by BOfH-666 Friday, May 11, 2018 10:48 PM
    Friday, May 11, 2018 9:45 PM
  • That is most instructive.  Thank you.  (I had to substitute "IBsite" where you have "ATL", but what you sent does give me a much better sense of PS control over the data than I had before.)
    Monday, May 14, 2018 2:50 PM