OK, first off, I'm pretty new to Powershell. I can do some useful things in it, but I'm pretty much just cobbling code together from what others have done. I also have some programming experience from the "dark ages" prior to object oriented programming, and unfortunately that's how I tend to think. So I'm having trouble wrapping my head around some of the logic of how Powershell works.

Here's what I'm doing:
I get a CSV file from our old email system containing user information. I then translate that information into a new format for the new email system. We're changing usernames and email domains in this process as well as cleaning up some of the data. (The new email system is Microsoft's Live @ EDU, and they've provided Powershell scripts for mass-creation of user accounts, given the data is in a properly formatted CSV file.)

I read in a CSV file containing user information:

FirstName,LastName,ID,Month,Day,Year,Email,Company,Department
John,Smith,0002375,02,24,1980,jsmith@email.org,XYZ Schools,High School
Jane,Doe,00032451,03,12,1974,jdoe@email.org,XYZ Schools,Middle School
Donald,Duck,00098765,04,03,1982,dduck@email.org,XYZ Schools,Elementary School


Several thousand users could be listed in one CSV file.


The output file needs to look like this:

Action,Type,Name,DisplayName,EmailAddress,FirstName,LastName,Password,Company,Department,Initials
Add,Mailbox,John Smith,John.Smith,John.Smith@newdomain.org,John,Smith,XM3YZ6,XYZ Schools,High School
Add,Mailbox,Jane Doe,Jane.Doe,Jane.Doe@newdomain.org,Jane,Doe,QUI87X,XYZ Schools,Middle School
Add,Mailbox,Donald Duck,Donald.Duck,Donald.Duck@newdomain.org,Donald,Duck,MH23YU,XYZ Schools,Elementary School


I've managed things like this, where I alter the data in a series of fields:

$oldfile = Import-Csv c:\oldfile.csv
$oldfile | foreach-object {
  $_.Email = $_.FirstName + "." + $_.LastName + "@newdomain.org"
}
$oldfile | Export-Csv c:\newfile.csv


But how do I create new columns of data in my object, such as the "Action" and the "Type"?

How do I remove columns of data from my object, such as "ID" so they aren't exported in the final CSV?

How do I rename columns of data within my object, such as "Email" to "EmailAddress"?

Your easiest option is probably to create a new object using data from the original object. You can Select-Object to create the new object with the properties in the correct order:

Import-Csv c:\oldfile.csv | Select-Object -Property @( @{ name = 'Action' ; expression = { 'Add' } }, @{ name = 'Type' ; expression = { 'Mailbox' } }, @{ name = 'DisplayName' ; expression = { "$($_.FirstName) $($_.LastName)" } }, @{ name = 'EmailAddress' ; expression = { "$($_.FirstName).$($_.LastName)@newdomain.org" } }, 'FirstName', 'LastName', @{ name = 'Password' ; expression = { '??' } }, 'Company', 'Department', @{ name = 'Initials' ; expression = { $_.FirstName[0] + $_.LastName[0] } } ) | Export-Csv c:\newfile.csv

Thanks a ton, Ghost! I appreciate the help.

Another related question:

I was forced to parse out my CSV loaded data from a single array into several separate arrays (one for each column of data in the CSV file). This was so I could process some of the information and compare it to another file. How do I combine those smaller arrays back into the big array for export?

Assuming that I have my altered data in arrays, such as:
$email = 'tom@mydomain.com','dave@mydomain.com','sue@mydomain.com','larry@mydomain.com'

Is it as simple as:
Select-Object -Property @(
@{ name = 'Action' ; expression = { 'Add'} },
@{ name = 'Type' ; expression = { 'Mailbox' } },
@{ name = 'DisplayName' ; expression = { $displayname } },
@{ name = 'EmailAddress' ; expression = { $email } },
@{ name = 'Password' ; expression = { $password } },
@{ name = 'Company' ; expression = { $district } },
@{ name = 'Department' ; expression = { $school } },
@{ name = 'Initials' ; expression = { $middleinit } }
) | Export-Csv c:\newfile.csv

Just some notes for anyone who later stumbles upon this post:

I discovered part of my problem in working with CSV files was that I did not understand how Powershell organized the logical unit that contained the data from the CSV after loading it with the Import-Csv command.

When the Import-Csv command creates an array, it is an array of arrays of strings. That becomes obvious after working with it for a little bit. But what wasn't obvious to me, and probably should have been, is that each line of data from the CSV file becomes an object. Each subfield in that line becomes a Member of that object with a Property setting (more specifically NoteProperty).

For example, in the CSV file I posted above, each object (line of data from the CSV) has multiple NoteProperties correlating with the data in the header line from the CSV file. In my example those note properties are: FirstName, LastName, ID, Month, Day, Year, Email, Company, and Department. Each Member of that object defines the contents of one of those NoteProperties, such as FirstName: Jane.

It is very easy to import data from a CSV into an array of arrays of objects:
$whatever = Import-Csv c:\myfile.csv

And, it is very easy to export that data back out to a CSV file for use by some other system:
$whatever | Export-Csv c:\otherfile.csv

But, once that data is in there ($whatever), it is hard to manipulate. For my purposes, I had to split the data out into separate arrays, so that I could manipulate the data. Like this:

$first = $last = $id = $m = $d = $y = $email = $company = $dept = @()
for ($i = 0; $i -le $whatever.length-1; $i++)
{
$first[$i] = $whatever[$i].FirstName
$last[$i] = $whatever[$i].LastName
$id[$i] = $whatever[$i].ID
$m[$i]= $whatever[$i].Month
...etc...
}

Once the data was parsed out into separate arrays, I could work with it easily. The index value was the same for each row of data in the CSV, so I could keep track of the lines of data as a unit even with them in separate arrays.

The problem became after I had done what needed done to the data, how did I get it back out in a CSV with the same format as the input? Sure, I could use the Out-File command and build strings and consequently build the file. But that seemed so archaic - or should I say "non-object oriented". :)

So I discovered how to build an array of arrays of objects with the proper members and properties so that it can be output with the Export-Csv command. First you have to declare your array of arrays and fill it with empty objects. Then you assign the NoteProperty fields and their data. You should know in advance how many lines of data you're going to have (such as using the .length method on the data you're putting in this array of arrays). Like this:

$myoutput = @(New-Object Object)
# Note the $i=1 in this for statement. You had to declare the array already and put one object in it.
# So you don't want to start with 0 like you might think.
for ($i = 1; $i -le $first.length-1; $i++) {$myoutput += New-Object Object}
# That populates the array of arrays with empty objects
for ($i = 0; $i -le $first.length-1; $i++) {
add-member -inputobject $myoutput[$i] NoteProperty FirstName $first[$i]
add-member -inputobject $myoutput[$i] NoteProperty LastName $last[$i]
add-member -inputobject $myoutput[$i] NoteProperty ID $id[$i]
add-member -inputobject $myoutput[$i] NoteProperty Month $m[$i]
...etc...
}

Then you can export your data to a CSV file easily and all formatted very nice and neat.

$myoutput | Export-csv c:\myoutput.csv

I hope the above helps someone with CSV files. I've seen a lot of post about CSV files, but not to many about manipulating the data and spitting it back out.

I would love it if the Powershell gurus here would look this over and comment. I'm sure there are better ways to do this, but this is what I've figured out with my very limited experience.