by ingrdude at 2013-03-18 12:11:33
I have been tasked with creating a report, which I thought would not be too terribly difficult. I have worked on this so long and am so frustrated that I don’t know what I’m doing is right or wrong. Any help would be appreciated.by DexterPOSH at 2013-03-18 15:42:00
This is the information pulled from AD, the samaccountname and department field.
Name Department
---- ----------
CRR - Department 1 (75401694)
V3B - D 2 (15791413)
BZ9 - Dept 3 (50519999)
PLB - Dept 3 (50519999)
L04 - Dept-4 (40700116)
L11 - Dept-4 (40700116)
L10 - D7 (10935476)
L09 - D7 (10935476)
L08 - 5thDept (10035)
L19 - 5thDept (10035)
L15 - Dept-4 (40700116)
E8N - Department 1 (75401694)
D5G - D 2 (50501413)
QA5 - Depart6 (5056333)
KGR - Org. Code: 00000000
I need to reference just the data in (). All valid dept #'s are in ().
I have set up an Excel file, conomap.xlsx to map the Dept number to Cost Center. We have hundreds of Dept numbers but only 6 cost centers.
75401694 = CostCenter1
15791413 = CostCenter2
50519999 = CostCenter3
40700116 = CostCenter4
10035 = CostCenter5
5056333 = CostCenter6
10935476 = CostCenter6
I am trying to get an Excel spreadsheet with a sheet with a list of accounts per Costcenter, accounts without a valid department, and a Master Summary Sheet
There are x accounts that fall under CostCenter1
There are x accounts that fall under CostCenter2
There are x accounts that fall under CostCenter3
There are x accounts that fall under CostCenter4
There are x accounts that fall under CostCenter5
There are x accounts that fall under CostCenter6
Hi,by MasterOfTheHat at 2013-03-19 08:43:04
To accomplish what you can do is use the switch statment in PowerShell along with RegEx matching. What I did in the below code is imported your csv which is in this format]Name - Department
CRR - Department 1 (75401694)
V3B - D 2 (15791413)
BZ9 - Dept 3 (50519999)
PLB - Dept 3 (50519999)
L04 - Dept4 (40700116)[/code]
Then used switch to do regex matching and increase the count for every cost center by 1 when a match is found$VerbosePreference = "continue"
Import-Csv -Delimiter "-" -Path C:\temp\test.csv |#
ForEach-Object -Process {
switch -regex ($.department) {
75401694 {
Write-Verbose "$ matched C1 "
$costcenter1 += 1
break
}
15791413 {
Write-Verbose "$_ matched C2 "
$costcenter2 += 1
break
}
50519999 {
Write-Verbose "$_ matched C3 "
$costcenter3 += 1
break
}
40700116 {
Write-Verbose "$_ matched C4 "
$costcenter4 += 1
break
}
10035 {
Write-Verbose "$_ matched C5 "
$costcenter5 += 1
break
}
5056333 {
Write-Verbose "$_ matched C6 "
$costcenter6 += 1
break
}
10935476 {
Write-Verbose "$_ matched C7 "
$costcenter7 += 1
break
}
default {
Write-Verbose "Not supported Cost Center"
break
}
}
}
"There are $CostCenter1 accounts that fall under CostCenter1"
"There are $CostCenter2 accounts that fall under CostCenter2"
"There are $CostCenter3 accounts that fall under CostCenter3"
"There are $CostCenter4 accounts that fall under CostCenter4"
"There are $CostCenter5 accounts that fall under CostCenter5"
"There are $CostCenter6 accounts that fall under CostCenter6"
This solution is not the most efficient one but you can build upon it.
Hope it helps
Regards
DexterPOSH’s way will work, but it requires hardcoding all of those dept code to cost center matchups inside the script, which is a lot of work and limits the ability to expand to other departments/cost centers later.by DexterPOSH at 2013-03-19 13:32:27
I would suggest pulling the dept code to cost center "table" into the script as a hash table and then reading the account name to dept code file line-by-line, looking for the dept code in the hash table, and then updating counters and output tables accordingly.
Maybe this will get you started…
Input files
costcenter.csv:DeptCode,CostCenter
75401694,CostCenter1
15791413,CostCenter2
50519999,CostCenter3
40700116,CostCenter4
10035,CostCenter5
5056333,CostCenter6
10935476,CostCenter6
deptnames.csv:SamAccountName,Department
CRR,Department 1 (75401694)
V3B,D 2 (15791413)
BZ9,Dept 3 (50519999)
PLB,Dept 3 (50519999)
L04,Dept-4 (40700116)
L11,Dept-4 (40700116)
L10,D7 (10935476)
L09,D7 (10935476)
L08,5thDept (10035)
L19,5thDept (10035)
L15,Dept-4 (40700116)
E8N,Department 1 (75401694)
D5G,D 2 (50501413)
QA5,Depart6 (5056333)
KGR,Org. Code: 00000000
The script:$f_costcenter = Import-Csv C:\temp\costcenter.csv
$f_deptnames = Import-Csv C:\temp\deptnames.csv
$h_costcenter = @{}
$f_costcenter | ForEach-Object { $h_costcenter.Add($.DeptCode,$.CostCenter) }
$h_costcentercounts = @{}
$h_costcenteracct = @{}
$a_invaliddeptcode = @()
# matches only numbers inside parentheses
$deptcode_regex = [regex]"((\d+))"
$deptname_regex = [regex]"[1]+"
$f_deptnames | ForEach-Object {
# valid dept code was found
if([regex]::Match($.Department, $deptcode_regex).Success)
{
$deptcode = [regex]::Match($.Department, $deptcode_regex).Groups[1].Value
$deptname = [regex]::Match($.Department, $deptname_regex).Groups[0].Value
# make sure there is a cost center associated with the dept code
if($h_costcenter.ContainsKey($deptcode))
{
$costcenter = $h_costcenter.Get_Item($deptcode)
# if there is already a record in the "counts" hash table for the dept code, increment it
if($h_costcentercounts.ContainsKey($costcenter))
{
$counter = [int]$h_costcentercounts.Get_Item($costcenter)
$counter++
$h_costcentercounts.Set_Item($costcenter, $counter)
}
# if there isn't already a record in the "counts" hash table for the dept code, create it
else
{
$h_costcentercounts.Add($costcenter,1)
}
# if there is already a record in the "accounts" hash table for the cost center, add the account to it
if($h_costcenteracct.ContainsKey($costcenter))
{
$accts = $h_costcenteracct.Get_Item($costcenter)
$accts += $.SamAccountName
$h_costcenteracct.Set_Item($costcenter, $accts)
}
# if there isn't already a record in the "accounts" hash table for the cost center, create it
else
{
$h_costcenteracct.Add($costcenter,@($.SamAccountName))
}
}
else
{
"No cost center matched to department $deptcode"
}
}
# if the department code is invalid, add it to the "invalide dept code" array
else
{
$a_invaliddeptcode += "$($.SamAccountName) - $($_.Department)"
}
}
"cost center counts:"
$h_costcentercounts.GetEnumerator() | Sort-Object Name
"ncost center depts:"<br>$h_costcenteracct.GetEnumerator() | Sort-Object Name<br>"ninvalid dept code:"
$a_invaliddeptcode.GetEnumerator() | Sort-Object Name
And the output:No cost center matched to department 50501413
cost center counts:
Name Value
---- -----
CostCenter1 2
CostCenter2 1
CostCenter3 2
CostCenter4 3
CostCenter5 2
CostCenter6 3
cost center depts:
CostCenter1 {CRR, E8N}
CostCenter2 {V3B}
CostCenter3 {BZ9, PLB}
CostCenter4 {L04, L11, L15}
CostCenter5 {L08, L19}
CostCenter6 {L10, L09, QA5}
invalid dept code:
KGR - Org. Code: 00000000
And now I should probably get some actual work done…
Great Work Charles !!by ingrdude at 2013-03-20 05:37:18
Your approach is much better…thanks for sharing it.
Wow, thanks to both of you!! I think this is the hump I needed to get over. I will see if I can get it all put together.by MasterOfTheHat at 2013-03-20 06:57:10
Come back and ask questions if you don’t understand something! That’s what the forum is for!
Glad you could use it.
^( ↩︎