locked
PowerShell - users, start dates and departments CSV help RRS feed

  • Question

  • Hi,

    I am OK with PowerShell but struggling to get my head round how best to accomplish this task. 

    I have a CSV file (which is generated weekly from our HR system) and it contains (amongst others) a column for the user start date, the user leave date (if they have left otherwise is blank) and then 3 columns for different department levels. Lets say orgLevel1,orgLevel2,orgLevel3. Not every user has all of these departments populated so for a simplfied department i need to use the logic that if orgLevel3 exists use that, otherwise use orgLevel2, and then orgLevel1 if 2 doesn't exist. 

    What i am trying to do is create an output (ideally in a variable that i can reuse later if i wish) that shows this

    Department Name : Current Employed

    Example of the csv would look like this

    UserName : Start Date : Leave Date : OrgLevel1 : OrgLevel2 : OrgLevel3

    user1 : 01/01/17 : 01/01/18 : my main department : my sub department : blank

    i have selected all the unique department names across the 3 levels that we should be using, but can't get my head round to figuring out how to reuse this information to check against each user if they are currently employed and in that department. 


    Regards,

    Denis Cooper

    MCITP EA - MCT

    Help keep the forums tidy, if this has helped please mark it as an answer

    Blog: http://www.windows-support.co.uk  Twitter:   LinkedIn:

    Thursday, January 25, 2018 7:51 AM

Answers

  • That is exactly what I just showed you how to do.

    $_.DepartmentLevel1,$_.DepartmentLevel1,$_.DepartmentLevel1 | %{if($_){$departmebt = $_}}
     "$department" has the last non-empty department.

    Generate a custom object:

    [pscustomobject]@{Name=$_.Name;Department=$departmene}

    Just import the CSV and filter as shown above then enumerate the results to extract the "Name" and the department.  Group by department and select Nam, Count and you will have your results to export to a Csv.

    It is a very simple beginner script that would have about 6 lines.


    \_(ツ)_/


    • Edited by jrv Thursday, January 25, 2018 9:11 AM
    • Marked as answer by Denis Cooper Thursday, January 25, 2018 10:16 AM
    Thursday, January 25, 2018 9:11 AM

All replies

  • Yes.  You are very confused and the question reflects that because there is no real question.  Yu have described all of what you have and how it works but you have not sked a clear question.  Reread what you wrote as if you know nothing and figure out how to change it so that it is a clear question.

    Start with: "Check 'what' against each user?  Why?

    Stop thinking about things and just try to write a clear question.  Forget about technology or scripting.  Just ask a question.  When you can write a clear question then it is likely you will see the answer.  If not then, if the question is accurate, we will be able to help.


    \_(ツ)_/

    Thursday, January 25, 2018 8:11 AM
  • Ok let me try and explain better assuming i have no experience with PS. 

    I have a CSV file with these headings

    Name,StartDate,LeaveDate,DepartmentLevel1,DepartmentLevel2,DepartmentLevel3

    For the 3 department headings one or more may be populated. Where more than one is populated we need to use the highest one - so that would be department3 first, then 2, then 1. 

    I need to use PowerShell to create a task i can automate to read the CSV. Then create an output that shows how many people we have currently employed in each department. 

    We have about 3000 records, and ~200 unique departments. The output should look like this

    Department Name , Currently Employed. 

    Hope thats clearer. 


    Regards,

    Denis Cooper

    MCITP EA - MCT

    Help keep the forums tidy, if this has helped please mark it as an answer

    Blog: http://www.windows-support.co.uk  Twitter:   LinkedIn:

    Thursday, January 25, 2018 8:16 AM
  • Well that is even more confusing.  You want a CSV with two fields - "department" and "currently employed"

    What is "currently employed" in the first CSV?  What is "Department" in the first CSV?


    \_(ツ)_/

    Thursday, January 25, 2018 8:20 AM
  • Hi,

    I suggest you to use temporary file in which you will store Departments names and then count it how is described here

    So logic of your script will be next:

    1) Read line from input csv

    2) Check for LeaveDate, if empty then check Departments starting from Level 3. If Department exist then write its name to temp file

    3) After parsing whole input file check temp file and count all duplicated values

    Thursday, January 25, 2018 8:37 AM
  • From this interpretation it sounds like the question is "How can I count the number of employees in each department?"  It does not answer any of my questions.

    There is no need for a temporary file.

    To find the last non-empty value of a list we can do this:

    'x', 'y', '' | %{if ($_){$x = $_}}

    The value of $x will always be the last item that is not empty.

    To check LeaveDate.

    Import-Csv filename | Where{ -not $_.LeaveDate}

    Now you will have a list of employees and departments which you can use "Group-Object" to generate the count by department.


    \_(ツ)_/

    Thursday, January 25, 2018 8:44 AM
  • A picture may help to clarify...

    Example CSV

    So we can see from the CSV the different departments, where more than 1 are shown per user we use the highest one, from Department 3 to 1. 

    Current employees are employees where the leave date is blank. 

    Example output csv..

    I need to select each unique department from the input CSV and create an output that shows how many people are employed for each unique department. 

    @jrv - i'm not familiar with the group-object command but will look into that. 


    Regards,

    Denis Cooper

    MCITP EA - MCT

    Help keep the forums tidy, if this has helped please mark it as an answer

    Blog: http://www.windows-support.co.uk  Twitter:   LinkedIn:

    Thursday, January 25, 2018 9:04 AM
  • That is exactly what I just showed you how to do.

    $_.DepartmentLevel1,$_.DepartmentLevel1,$_.DepartmentLevel1 | %{if($_){$departmebt = $_}}
     "$department" has the last non-empty department.

    Generate a custom object:

    [pscustomobject]@{Name=$_.Name;Department=$departmene}

    Just import the CSV and filter as shown above then enumerate the results to extract the "Name" and the department.  Group by department and select Nam, Count and you will have your results to export to a Csv.

    It is a very simple beginner script that would have about 6 lines.


    \_(ツ)_/


    • Edited by jrv Thursday, January 25, 2018 9:11 AM
    • Marked as answer by Denis Cooper Thursday, January 25, 2018 10:16 AM
    Thursday, January 25, 2018 9:11 AM
  • i got it to work, thankyou

    a bit more than 6 lines, but

    $departmentTable = @()
    
    import-csv 'C:\data\temp\starters and leavers.csv' | Where-Object{-not $_.LeaveDate} | foreach{ 
    
        $firstName = $_.forename
        $lastName = $_.surname
        $displayname = "$lastname, $firstName"
    
        $_.orglevel1,$_.orglevel2,$_.orglevel3 | %{if($_){$department = $_}}
    
        $obj = New-Object -TypeName PSObject -property(@{'Department'=$department;
                                                         'Name'=$displayname
        })
    
    
        $departmentTable += $obj 
    
        }
    
        $departmentTable | Group-Object -Property department | select count,name


    Regards,

    Denis Cooper

    MCITP EA - MCT

    Help keep the forums tidy, if this has helped please mark it as an answer

    Blog: http://www.windows-support.co.uk  Twitter:   LinkedIn:

    Thursday, January 25, 2018 10:16 AM
  • Great.  And you learned a bit of PowerShell along the way.

    This is actually the easiest approach.

    import-csv 'C:\data\temp\starters and leavers.csv' | 
    	Where-Object{-not $_.LeaveDate } | 
    	ForEach-Object{	
    		$_.orglevel1, $_.orglevel2, $_.orglevel3 | %{if ($_) {$department = $_}	}
    		[pscustomobject]@{
    				'Department' = $department;
    				'Name'	     = $_.surname + ', ' + $foreName
    			}
    	} |
    	Group-Object Department | select count, name
    Avoid interim variables as they create confusion and lead to error.


    \_(ツ)_/

    Thursday, January 25, 2018 4:32 PM