I am doing tasks like create Groups in AD with users few times a day on daily basis, It is difficult to edit powershell code everyday according to CSV column headers. I am trying to create an advanced function that can read CSV files, without me editing column headers inside the code. I have started to build an advanced for that but I am not sure If I am on right track. Any suggestions or approach how to build it would be great help. FYI, I have already built different functions for creating groups, only difficulty I have now to build a functions that can read CSV file automatically
I am posting my starter function, where I am successful to count column headers and extract their names in variable, now I am getting difficulty to store members name under each column headers to a variable.
Actually I did not understand what your actual problem is. You might elaborate a little more detailed and you may share some sample data before and after.
If it’s about getting the headers of a given CSV file you can use the following snippet:
I’m with Olaf. I don’t quite understand the problem you are trying to solve. Mainly because it seems like a simple issue (get the headers from a given csv), but you are doing a pretty elaborate solution which seems unnecessary. As I see it there are two relatively simple algorithms to get the headers of a csv in PowerShell.
Import-CSV "C:\GroupstoCreate.csv" |
Get-Member -MemberType NoteProperty |
Select-Object -ExpandProperty Name
Both of these solutions will return the header names as an array of strings. If you want to incorporate this into a function that will return an array of strings with the header name, I’d do something like this.
function Get-Headers
{
param(
[parameter(Mandatory=$true)]
[ValidateScript({
if (Test-Path -Path $_ -ErrorAction Ignore ) { $true }
else {throw "$_ does not exist"}
})]
[string]
$Path
) #Param block
(Import-CSV -Path $Path)[0].psobject.Properties.Name
} #Function Get-Headers
It may be that the functional goal is to pivot a CSV file, where the number of headers (i.e., group names) varies day-to-day, and the following rows are the group members arranged by column. Following this premise, I used Get-Content to treat all rows as strings instead of importing the CSV. I then split the row strings, and used array indexing to match up the pieces.
Remarks
Source values are double-quote encapsulated.
“Nulls” exist.
Commas exist in the DisplayName, so a strategy had to be worked out not to create bad splits.
It’s very late and Laura Norman has a lingering double quote.
add comma-comma option to the [Regex]::Split method.
the class method also needs an else statement.
Users (as in AD User) are shown in a patterning similar to DisplayName.
User can be renamed as Member.
I have come to prefer classes and methods since their introduction in PowerShell 5.
using namespace System.Collections;
using namespace System.Text.RegularExpressions;
<#
.Synopsis
Pivots CSV file into GroupMember object.
.DESCRIPTION
A Comma Separated Values (CSV) file is split into rows
with the elements in header row being matched to the elements in
the following rows.
.EXAMPLE
Get-GroupMembersFromCSV "C:\MyProjects\GroupMembers.csv"
.INPUTS
[string] URI of CSV file.
.OUTPUTS
Get-GroupMembersFromCSV.GroupMember
.NOTES
This code is not suited for use in production applications. It is considered unstable and moderately tested.
The file path must end in the .csv file extension.
The source file must have a header row as the first row in this version.
#>
Function Get-GroupMembersFromCSV
{
[CmdletBinding()]
Param(
[parameter(Mandatory = $True,
ValueFromPipelineByPropertyName = $True)]
[ValidatePattern("(\S+)\.csv$")]
[string]$SourceFile
)
Process
{
Class Group
{
[string[]]SpiltRow([string]$row)
{
if($row -match '^".+"$')
{
$row = $row.Substring(1,($row.Length -2))
}
return [Regex]::Split($row,'",[",]')
}
}
Class GroupMember : Group
{
[string] $GroupName;
[string] $UserName;
}
Class GroupCollection
{
[ArrayList] $Group;
GroupCollection()
{
$this.Group = [ArrayList]::new();
}
[void]AddGroup([GroupMember] $item)
{
$this.Group.Add($item);
}
}
$groupCollection = [GroupCollection]::new();
$group = [Group]::new();
$dataset = Get-Content $SourceFile;
$headers = $group.SpiltRow($dataset[0]);
foreach($row in $dataset[1..$dataset.Length])
{
$x = $group.SpiltRow($row)
foreach ($user in $x)
{
$groupMbr = [GroupMember]::new();
$groupMbr.GroupName = $headers | Where-Object{[array]::IndexOf($headers,$_) -eq [array]::IndexOf($x,$user)}
$groupMbr.UserName = $user;
$groupCollection.AddGroup($groupMbr);
}
}
}
End
{
return $groupCollection.Group | Where-Object{$_.UserName -ne ''} | Sort-Object GroupName;
}
}
Thanks for your help and providing wonderful solutions. Apologies for late reply. My end goal is to create a powershell functions that can read CSV file and Imports data without I have to manually each time change column headers name in powershell code, and It can automatically store data into variables, For example:- I can store Information of group 1 into Variable 1, then I can read properties of variable 1 as GroupName and GroupMembers. so that I can pass Group Names property value to my functions for creating groups and pass values of GroupMembers property to add members function. Instead of modify CSV file each time that I received in format(Attached Picture).
There are several ways to achieve this secondary action (and I recommend keeping that action out of the CSV-reading function). Using the Get-GroupMembersFromCSV, you can pass the results into another function, taking advantage of the denormalized data. The data may also be split into Group and Members objects and treated independently (not shown).
For the record, I’m not on a machine that has Active Directory access. I’d recommend looking at the reply @rob-simmers as it may work more efficiently with AD, while @ralphmwr and @Olaf’s replies may be better suited to the rest of your program structure.
Example
$results = Get-GroupMembersFromCSV C:\MyProjects\ds_15885.csv;
function Do-Something([psobject]$p)
{
foreach($i in $p)
{
# Write-Host used as fake internal function;
Write-host "$($i.UserName.Replace('"','').Trim()) added as member to $($i.GroupName) group."
}
}
cls;
Do-Something $results;
We’ll never kow when the OP does not reply and explains it.
