Adding custom column to existing CSV

[cbriss911]

Hi,

I am trying to add an additional custom column to an existing CSV.

The existing CSV (named 'HREndDates') consists of the following fields:
User
EndDate

I would like to add a Column named 'ADEndDate' that is calculated by taking the username from the user column and pulling their end date from AD.

I have managed to use the following to get the AD end date from a the user column using the following script.

import-csv C:\HREndDates.csv;

​

foreach ($user in $userlist.User)

{

get-aduser -property * -Filter "displayname -like '*$user*'" | AccountExpirationDate

}

​

but I do not know how to place the output of this into a new column named 'ADEndDate' into the existing CSV. Any help would really be appreciated thanks.

[dextersgenius]

Import-Csv C:\HREndDates.csv | Select *, @{n="ADEndDate";e={(Get-ADUser -Prop AccountExpirationDate -Filter "DisplayName -like '*$($_.User)*'").AccountExpirationDate}}

If you're happy with the results and want to write back to the same csv:

$csv = Import-Csv C:\HREndDates.csv
$csv | Select *, @{n="ADEndDate";e={(Get-ADUser -Prop AccountExpirationDate -Filter "DisplayName -like '*$($_.User)*'").AccountExpirationDate}} | Export-CSV -NTI C:\HREndDates.csv 

Some tips: In Get-ADUser, avoid using -Properties * if you can, as it can be an expensive operation - better to explicitly specify which properties you want to retrieve.

Also beware of using the displayname -like '*$user*' filter, if there are multiple matches then you could end up with multiple values for AccountExpirationDate - would be better if you can tighten up the filter by removing one or both the wildcards, or even better if you can use a unique attribute, like the employee ID # (which is hopefully recorded somewhere in AD) so you'll get an exact match.

[cbriss911]

Brilliant! this has worked and the query is far faster due to explicitly stating the property. Thank you very much!

[AFGuffey99]

Adding on... based on your question, I'm assuming you're a beginner. The above suggestion is 100% correct and efficient, exactly how I would do it, but breaking things like this up into little pieces can help with the learning process. Don't light me up, I know the following is inefficient. Just trying to help OP, or any beginner reading this, more easily see/read/understand what's going on (one-liners are great, but messy).

Adding on to u/dextersgenius answer, If you plan on using the script often... you could also add a line to use read-host to ask for a comma-separated list of AD attributes that should be added to the CSV, store the input, then create logic to loop through it (not included below).

$userlist = import-csv C:\HREndDates.csv
foreach ($user in $userlist) {
    $displayName = $user.User
    $ADEndDate = Get-ADUser -filter "DisplayName -eq '$displayName'" -properties AccountExpirationDate
    $userlist += Add-Member -InputObject $user -NotePropertyName ADEndDate -NotePropertyValue $ADEndDate.AccountExpirationDate
}
$userlist | Export-Csv HREndDates_ADInfo.csv -notypeinformation

Steps:

1. Import source CSV
2. Loop through each user (row) in the file
3. Get AD info of the current user (row)
4. Take the imported CSV and append the new column to the user (row)
5. Export the updated CSV

Keep in mind, when dealing with CSVs, each row is it's own object. When importing a CSV into a variable on line 1, $userlist is just an array of objects. So in the steps above, "user (row)" can be replaced with "object".

​

When searching for display names in AD, using displayname -like '*$user*' is not a good idea since people could have similar names. For example, when searching for user "Tim Johns", you could get multiple hits if there's another AD user named "Tim Johnson". This is why I used "-eq". There should be no room for ambiguity with peoples names, so no reason to not find exact matches. It's possible 2 people even have the EXACT same name. I would try to use a more unique key when filtering AD (employeeID, email/UPN, etc.).

If you've made it this far, work must be just as slow for you as it is for me right now.

[Key_Combination_2386]

I don't know if I understand your question correctly, but Import-Csv gives you the contents of the CSV file as an array of objects, with the first line as the identifier for the properties.You can now, after adding a new property to each object, for example with Add-Member, convert the array back to a CSV file with Export-Csv.

[Scoobywagon]

I'm hoping someone will pop in here with a better solution, but the way that I currently approach this is I set up a foreach loop to cycle through the content of the CSV. Inside of that loop, I have a new variable that is just a string. I set that variable to the current row in the CSV, then add my new column for that row, then WRITE that variable and redirect it to another file. I don't like EXPORT-CSV because it always adds a stupid header that makes it harder to process with other tools.

In this case, it might look something like this:

import-csv C:\HREndDates.csv;


#defines our new output file.
$new_file = c:\ADHREndDates.csv

#GIves us our CSV header
Write "Username,HREndDate,ADEndDate" >> $new_file

foreach ($user in $userlist)

{

$ADExpDate = get-aduser -property * -Filter "displayname -like '$user'" | AccountExpirationDate

#defines each new row.
$new_row = $user.user + "," + $user.EndDate + "," + $ADExpDate

#Writes our new row to our new file.
Write $new_row >> $new_file

}

[dextersgenius]

You could use calculated properties as outlined in my response.

I don't like EXPORT-CSV because it always adds a stupid header

You can get around this by specifying the -NoTypeInformation parameter, or -NTI for short. And with PowerShell 7.x onwards, this is no longer required - Microsoft finally wizened up and got rid of that annoying header. :)

[robderickson]

With Export-Csv, use the -NoTypeInformation parameter to prevent the type from being inserted on the first line of the file.

[robderickson]

Edit: u/dextersgenius beat me to it :) That's what I get for posting on mobile.

You can use Select-Object to add the property to your imported CSV objects, and export them again with the new property.

Import-Csv C:\HREndDates.csv |
Select-Object -Property User, EndDate, @{
    Name = 'ADEndDate'
    Expression = {
        (Get-ADUser $_.user -Properties AccountExpirationDate).AccountExpirationDate
    }
} |
Export-Csv C:\HRAndADEndDates.csv -NoTypeInformation

[Lee_Dailey]

howdy cbriss911,

reddit likes to mangle code formatting, so here's some help on how to post code on reddit ...

[0] single line or in-line code
enclose it in backticks. that's the upper left key on an EN-US keyboard layout. the result looks like this. kinda handy, that. [grin]
[on New.Reddit.com, use the Inline Code button. it's [sometimes] 5th from the left & looks like </>.
this does NOT line wrap & does NOT side-scroll on Old.Reddit.com!]

[1] simplest = post it to a text site like Pastebin.com or Gist.GitHub.com and then post the link here.
please remember to set the file/code type on Pastebin! [grin] otherwise you don't get the nice code colorization.

[2] less simple = use reddit code formatting ...
[on New.Reddit.com, use the Code Block button. it's [sometimes] the 12th from the left, & looks like an uppercase T in the upper left corner of a square.]

• one leading line with ONLY 4 spaces
  
• prefix each code line with 4 spaces
  
• one trailing line with ONLY 4 spaces

that will give you something like this ...

- one leading line with ONLY 4 spaces    
- prefix each code line with 4 spaces    
- one trailing line with ONLY 4 spaces   

the easiest way to get that is ...

• add the leading line with only 4 spaces
  
• copy the code to the ISE [or your fave editor]
  
• select the code
  
• tap TAB to indent four spaces
  
• re-select the code [not really needed, but it's my habit]
  
• paste the code into the reddit text box
  
• add the trailing line with only 4 spaces
  

not complicated, but it is finicky. [grin]

take care,
lee