Solved

Change Titles and Dates in a powershell generated CSV File

Posted on 2016-08-18
2
21 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 39

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now