Forum Discussion

user4444's avatar
user4444
Copper Contributor
Feb 25, 2022

Updating and appending a CSV

Hello,

I have a task I'd like to complete using Powershell. I'm not a complete noob, but not intermediate, let's say post beginner when it comes to .csv data manipulation.

I know what I'd like to achieve, but I'm not entirely sure how best to achieve it. I also have a tendency to over think my scripting.

 

While I've no doubt someone can quickly give me a script to solve this task I'd like to learn how I should approach solving this type of task myself.

 

The task...

 

I have a .csv with 5 columns, each with headers, and a few thousand rows. The first column has usernames, each ending with '$'. I would like to find specific details for each username (taken from AD) and add/append this data to the .csv. The usernames will need the '$' removed for searching.

I've tried to break this task down into steps to see if it will keep me from feeling overwhelmed.

 

Steps
1. extract usernames listed in column 1 and removed '$' from the end of each username.
2. check for username in AD
if found select displayname, givenname, surname.
3. create new columns, with headers, for each object (displayname, givenname, surname) in the existing .csv, unless the column already exists.
insert information selected at step 2.
If no information was found at step 2 insert "no information found".

 

I am unsure of the correct approach to take and as mentioned previously I tend to over think my scripts.

 

What puzzles me the most is how do I add/append the new data to the .csv at the correct row/column while also only creating the column headers once and making sure to insert "no information found" if nothing is found?

 

Any guidance most welcome.

Thanks.

Windows PowerShell
  • AndySvints's avatar
    AndySvints
    Steel Contributor
    Mar 09, 2022

    Hello user4444,

    Assuming that input file looks to something similar to this:

     

    Username         Colum2 Column3 Column4 Column5
--------         ------ ------- ------- -------
tony.stark$      Data   Data    Data    Data
steve.rogers$    Data   Data    Data    Data
bruce.banner$    Data   Data    Data    Data
…$               …      …       …       …
natasha.romanov$ Data   Data    Data    Data
wanda.vision$    Data   Data    Data    Data
peter.parker$    Data   Data    Data    Data

     

    One of the approaches would be the following:

     

    $InputFile=Import-csv .\Users.csv
foreach($i in $InputFile){
 $UserInfo=Get-ADUser $i.Username.replace('$','')
 if($UserInfo){
   $i | Add-Member -NotePropertyName DisplayName -NotePropertyValue $($i.Displayname)
   $i | Add-Member -NotePropertyName GivenName -NotePropertyValue $($i.GivenName)
   $i | Add-Member -NotePropertyName Surname -NotePropertyValue $($i.Surname)
   $i | Add-Member -NotePropertyName Notes -NotePropertyValue ""
}else{
   $i | Add-Member -NotePropertyName DisplayName -NotePropertyValue ""
   $i | Add-Member -NotePropertyName GivenName -NotePropertyValue ""
   $i | Add-Member -NotePropertyName Surname -NotePropertyValue ""
   $i | Add-Member -NotePropertyName Notes -NotePropertyValue "No Information Found"  
 }
 
}

     

     

    After that you can Export-Csv and have a file with consolidated Data.

    Hope that helps.

Resources