Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Change Titles and Dates in a powershell generated CSV File

Posted on 2016-08-18
2
38 Views
Last Modified: 2016-08-18
I am using powershell to generate a CSV File. I am using Quest ActiveRoles. Not sure if that matters but figured I'd mention it. I am exporting a listing of AD users. Another program is going to ingest this file but there's a few problems. The date field is not formatted correctly. I need to shave off the time from the date. I was told the application requires the header names to be specific values. Is there a way to reformat the headers to be called something different?  The script is below

get-qaduser -SearchRoot "OU=San Diego,OU=domain,DC=domain,DC=com","OU=New York,OU=domain,DC=domain,DC=com" -enabled -SizeLimit 0 -NotMemberOf "Test Accounts","Service Accounts","Hitachi-ID Exclude" | Select Email,FirstName,LastName,Department,WhenCreated  | Export-Csv 'C:\Scripts\Active Directory\InfoSec-UserExport.txt' -NoClobber -NoTypeInformation -Force

The output I get is

"Email","FirstName","LastName","Department","whenCreated"
"User1@domain.com","UserF1","UserL1l","Cashiering","9/8/2006 11:33:53 AM"
"User2@domain.com","UserF2","UserL2l","Cashiering","9/13/2006 11:50:59 AM"
"User3@domain.com","UserF3","UserL3l","Cashiering","12/6/2012 2:51:43 PM"


In the "When Created" field, I need to shave off the time and just include the date MM/DD/YYYY.

these titles need to change from:
"Email","FirstName","LastName","Department","whenCreated"
To:
"emailAddress","firstName","lastName","Department","whenCreated"


I'm assuming I need to do an import-csv but not sure exactly how to go about editing a particular field in a CSV in each record and also modify the column headers :(



Thank you in advance!
0
Comment
Question by:jbla9028
2 Comments
 
LVL 40

Accepted Solution

by:
footech earned 500 total points
ID: 41761271
You can do the work with calculated properties in the Select-Object command.  The below should work if the WhenCreated property is a datetime object (if not, a slight modification would be needed).
get-qaduser -SearchRoot "OU=San Diego,OU=domain,DC=domain,DC=com","OU=New York,OU=domain,DC=domain,DC=com" -enabled -SizeLimit 0 -NotMemberOf "Test Accounts","Service Accounts","Hitachi-ID Exclude" |
 Select @{n="EmailAddress";e={$_.Email}},
        FirstName,LastName,Department,
        @{n="WhenCreated";e={($_.WhenCreated).ToShortDateString()}} |
 Export-Csv 'C:\Scripts\Active Directory\InfoSec-UserExport.txt' -NoClobber -NoTypeInformation -Force

Open in new window

0
 
LVL 1

Author Closing Comment

by:jbla9028
ID: 41761409
Perfect. Thank you!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question