Powershell - Find match value in rows and output adjacent rows

BlackCat 81 Reputation points
2020-12-11T16:28:23.517+00:00

I have CSV with headers name, machine, type.

Name Machine Type

User1 server1 Service

user1 server1 Task

user2 server2 service

user2 server2 Task

I want to create new CSV file with name user1.csv and in this new csv with

Machine Type

server1 service

server2 Task

Same, a new file name user2.csv with

machine type

server2 service

server2 task

Thanks

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,355 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Rich Matheisen 44,621 Reputation points
    2020-12-12T03:36:06.9+00:00

    See if this works for you:

    $users = @{}
    $previous = ""
    $t = @() # Accumulate type
    
    Import-CSV c:\Junk\users.csv |
        ForEach-Object -BEGIN {} -PROCESS {
                if ($previous.Length -eq 0){
                    $previous = $_.Name, $_.Machine -join ";"
                    $t += $_.Type
                }
                elseif ($previous -eq ($_.Name, $_.Machine -join ";")) {
                    $t += $_.Type
                }
                Else{   # not the same user/server
                    [PSCustomObject]@{
                        Name = $previous.split(";")[0]
                        Server = $previous.split(";")[1]
                        Type = $t -join ";"
                    }    
                    $t = ,$_.Type
                    $previous = $_.Name, $_.Machine -join ";"
                }
            } -END{
                # emit last row
                [PSCustomObject]@{
                    Name = $previous.split(";")[0]
                    Server = $previous.split(";")[1]
                    Type = $t -join ";"
                }
            } | ForEach-Object{
                [array]$users[$_.Name] += $_
            }
    $users.GetEnumerator() |
            ForEach-Object{
                $_.Value | Export-Csv "C:\junk\$($_.Key).csv" -NoTypeInfo
            }
    
    1 person found this answer helpful.
    0 comments No comments

  2. BlackCat 81 Reputation points
    2020-12-13T06:00:04.573+00:00

    I am getting no error but export csv comes out like this

    "Module","Assembly","TypeHandle","DeclaringMethod","BaseType","UnderlyingSystemType","FullName","AssemblyQualifiedName","Namespace","GUID","IsEnum","GenericParameterAttributes","IsSecurityCritical","IsSecuritySafeCritical","IsSecurityTransparent","IsGenericTypeDefinition","IsGenericParameter","GenericParameterPosition","IsGenericType","IsConstructedGenericType","ContainsGenericParameters","StructLayoutAttribute","Name","MemberType","DeclaringType","ReflectedType","MetadataToken","GenericTypeParameters","DeclaredConstructors","DeclaredEvents","DeclaredFields","DeclaredMembers","DeclaredMethods","DeclaredNestedTypes","DeclaredProperties","ImplementedInterfaces","TypeInitializer","IsNested","Attributes","IsVisible","IsNotPublic","IsPublic","IsNestedPublic","IsNestedPrivate","IsNestedFamily","IsNestedAssembly","IsNestedFamANDAssem","IsNestedFamORAssem","IsAutoLayout","IsLayoutSequential","IsExplicitLayout","IsClass","IsInterface","IsValueType","IsAbstract","IsSealed","IsSpecialName","IsImport","IsSerializable","IsAnsiClass","IsUnicodeClass","IsAutoClass","IsArray","IsByRef","IsPointer","IsPrimitive","IsCOMObject","HasElementType","IsContextful","IsMarshalByRef","GenericTypeArguments","CustomAttributes"
    "System.Management.Automation.dll","System.Management.Automation, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35","System.RuntimeTypeHandle",,"System.Object","System.Management.Automation.PSObject","System.Management.Automation.PSObject","System.Management.Automation.PSObject, System.Management.Automation, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35","System.Management.Automation","0b8e33af-1f2a-38de-a865-22cc5931d778","False",,"True","False","False","False","False",,"False","False","False","System.Runtime.InteropServices.StructLayoutAttribute","PSObject","TypeInfo",,,"33555176","System.Type[]","System.Reflection.ConstructorInfo[]","System.Reflection.EventInfo[]","System.Reflection.FieldInfo[]","System.Reflection.MemberInfo[]","System.Reflection.MethodInfo[]","System.Reflection.TypeInfo+<get_DeclaredNestedTypes>d__23","System.Reflection.PropertyInfo[]","System.Type[]","Void .cctor()","False","AutoLayout, AnsiClass, Class, Public, Serializable, BeforeFieldInit","True","False","True","False","False","False","False","False","False","True","False","False","True","False","False","False","False","False","False","True","True","False","False","False","False","False","False","False","False","False","False","System.Type[]","System.Collections.ObjectModel.ReadOnlyCollection`1[System.Reflection.CustomAttributeData]"


  3. BlackCat 81 Reputation points
    2020-12-13T17:34:25.89+00:00

    It is working now, I had wrong file name at my end. Now if I want to read those CSV files from Junk folder, what variables i need to use for to get content of file name and Name attribute?

    Thanks


  4. BlackCat 81 Reputation points
    2020-12-13T17:42:04.657+00:00

    Also, I noticed sometimes it joined like this "Service;Service;Service;Service" instead of one on each line

    Thanks