All 1 entries tagged Export-Csv

No other Warwick Blogs use the tag Export-Csv on entries | View entries tagged Export-Csv at Technorati | There are no images tagged Export-Csv on this blog

May 20, 2013

How to export your own data using the PowerShell export–csv commandlet

You’ve created a bunch of your own data in a PowerShell script, and want to export it as a csv file. Maybe you’ve tried using the Export-Csv commandlet, but it didn’t work as you expected, so you’ve resorted to hacking strings about and ‘exporting’ it using Write-Host and Start-Transcript, or something similar?

The trick is to turn your data into objects. Once your data is in object form you can pass it straight to export-csv, or to any other commandlet. Here’s how I did it:

Scenario

I’m extracting data from Active Directory accounts to pass to a colleague who needs to email each account-holder. I’ve ended-up with the following PowerShell variables, each containing about 1000 records that I want to export as a .csv file.

My Powershell Variable

Where the data came from

$AdAccountname

SamAccountname

$PrimarySMTP

Extracted by looping through proxyAddresses looking for the one that starts “SMTP:”

$MonthsRegisted

Date calculation that compares registration date to now, and records the number of months


Approach


Block diagram showing how to arrange data in PowerShell so that it will export to a csv file correctly


1. Create an empty array to store my stuff

2. Create an object for each line of our results

3. Add each column from each line as an attribute of this object

4. Pipe the array to export-csv


Steps

Decide on a name for my new object. I’ll go with $contactObject

Create an empty array ($resultsarray) to hold my 1000 new $contactObject objects

Loop for each “Contact”-----------------------------------------------------

Make a new empty “Contact” object

Add $AdAccountname attribute to “Contact”

Add $PrimarySMTP attribute to “Contact”

Add $MonthsRegisted attribute to “Contact”

Add the “Contact” object to the results array ($resultsarray)

------------------------------------------------------------------------------

When the loop is finished, Export $resultsarray as a .csv file

PowerShell Code

Example Powershell code that will output data using the export-csv commandlet

# Declare an array to collect our result objects
$resultsarray =@()

# $contacts will be the ‘loop counter’, so set it to the same as any of our variables; $AdAccountname will do
$contacts=$AdAccountname

# For every $contact held in the $contacts, do this loop
foreach ($contact in $contacts){

# Create a new custom object to hold our result.
$contactObject = new-object PSObject

# Add our data to $contactObject as attributes using the add-member commandlet
$contactObject | add-member -membertype NoteProperty -name "AD Account" -Value $AdAccountname
$contactObject | add-member -membertype NoteProperty -name "SMTP" -Value $PrimarySMTP
$contactObject | add-member -membertype NoteProperty -name "Registered" -Value $MonthsRegisted

# Save the current $contactObject by appending it to $resultsArray ( += means append a new element to ‘me’)
$resultsarray += $contactObject
}
$resultsarray| Export-csv Contacts.csv -notypeinformation

February 2023

Mo Tu We Th Fr Sa Su
Jan |  Today  |
      1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28               

Search this blog

Tags

Galleries

Blog archive

Loading…
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder
© MMXXIII