Share via

PowerShell Tip: Manipulating CSV data using PowerShell

  • Article

PowerShell Tip: Manipulating CSV data using PowerShell

Introduction

This Wiki is to demo manipulating CSV data using PowerShell. Recent times, we came across couple of requirements due to Infrastructure as well as organizational changes. Of course management needs reports before any technical transformation. Since we do PowerShell we will enjoy the tasks.

Requirement

  • We have data in excel sheet which has group name and members ID - Add members in specific group

Background

That's the simple one liner given by requester. Yeah indeed it's quite simple! But let's dive into the production environment to see the tricks and challenges. The excel sheet was saved as CSV and input data are good. But the members column has data like User1;User2 Members column is not NT or samaccountname it's firstname.lastname Can I query the required information from your server? No we don't permit because of IT Security.This rings! Clients have two trusted environment and one of the office location has there own Infrastructure. Only few enterprise admin has access to that! Finally it's our PowerShell team to play around.

Technical Description

  • Query Members from A Domain
  • Add members to B Domain groups

CSV Format

The CSV format shared by requester is shown below
NewDisplayName,SamAccountName,Domain,FQDN
Group1,ChenV;Test3;Test4,DomainA,DomainA\ChenV Group2,Test1;Test5;Test6,DomainA,DomainA\Test1

PowerShell Solution

Add-PSSnapin Quest.ActiveRoles.AdManagement
$data = Import-Csv C:\Temp\Group111.csv 
foreach($info in $data)
{
    $id = $info.SamaccountName -replace ';' , ',' -split ',' 
    foreach($ids in $id)
    {
        $user = Get-QADUser -Identity $ids -Service 'DC' | Select NTAccountName 
        "Adding Members '$($user.NTAccountName)' in " + $info.FQDN ;Add-QADGroupMember -Identity $info.FQDN -Member $user.NTAccountName -Verbose
    }

}

Reference

help Import-Csv -Detailed

help about_Foreach -Detailed

help Add-PSSnapin -Detailed

help about_Split -Detailed

help about_Join -Detailed