Add and Populate Column in CSV File

PowerShell Help
1

by slash85 at 2012-08-24 03:57:34

Hi All,

I have a CSV like this:

Col1,Col2,Col3
slash,uk,male
jbloggs,uk,male

I want to add a 4th column and populate with a string so i’d end up with this:

Col1,Col2,Col3,Col4
slash,uk,male,hello
jbloggs,uk,male,hello

Any ideas how this can be done with powershell?

Many Thanks,
Slash.
by JeffH at 2012-08-24 04:39:32
I would import the csv and save objects to a variable. This way the file can be closed. Then pipe the objects to Select-Object and add your 4th "column" with a custom hash table entry, Pipe those results back to Export-CSv.
by slash85 at 2012-08-24 05:03:57
Hi Jeff,

Thanks for your reply. I will look into what you have suggested and give it a go. I can sense a day of googling coming up.

Cheers,
Slash.
by JeffH at 2012-08-24 05:08:26
Start with PowerShell help. Look at full help and examples for Import-CSV, Select-Object and Export-CSV
by slash85 at 2012-08-24 05:20:55
Thanks Jeff,

I’ve had a quick read and constructed the below:

$b = Import-CSV C:\dk\test.csv
$b | select-object col1,col2,col3,"col4"

which outputs:

col1 col2 col3 col4
---- ---- ---- ----
slash uk male
jblogg uk male

Which seems like a good start but you’ve lost me on the custom hash table entry?

Cheers,
Slash.
by JeffH at 2012-08-24 05:35:47
You could do this:

$b | select-object col1,col2,col3,@{Name="col4";Expression={ Some powershell code that returns a value to be used for col4 for each entry }} | Export-Csv file.csv -notypeinformation

The tricky part is the code you put in the Expression scriptblock. It can be as simple or as complicated as you need it to be.
by slash85 at 2012-08-24 05:40:26
Thanks Jeff,

I’ve managed to do what I want with your help so thank you very much. I’ve done it slightly different and used a for_each instead of the hash table as i didn’t quite understand that:

$b | select-object col1,col2,col3,"col4" | ForEach-Object { $.col4 = "hello"; return $ } | Export-CSV -Path file.csv -NoTypeInformation

Thanks again slash.
by JeffH at 2012-08-24 05:51:15
I’m glad you got it to work, but you are making this more complicated than it needs to be.

$b | Select *,@{Name="Col4";Expression={"Hello"}} | Export-CSV -Path file.csv -NoTypeInformation
by slash85 at 2012-08-24 06:37:07
Ahh ok I get you now.

On another note how simple would it be to do a find & replace on say col2?

$b | select *,@{Name="Col2";Expression={-replace("UK","US")}} | export-csv -path file.csv -NoTypeInformation

Would something like this work?

Cheers!
by DexterPOSH at 2012-08-24 07:08:02
Hi Slash,
I tried the following and it work, but I don’t know if it is the best way to do this.
Jeff will be able to point us in the right direction.

$b| select col1,@{Name="col2";Expression={$_.col2 -replace ("uk","us")}},col3,@{Name="col4";Expression={"Hello"}}
by slash85 at 2012-08-24 07:13:43
Hi Posh

That works for me! Thank you very much!

Slash.
by JeffH at 2012-08-24 07:39:37
That’s the way I would do it.
by DexterPOSH at 2012-08-27 07:55:12
Glad that helped you Slash.

Thanks Jeff, you explained it neatly.

2

Adding a new column works fine, but if you want to add a column if it does not exist and update the value if the column exist, how can that be achieved?

3 
This topic kept coming up on conditional population search - the code below populates/adds field 4 based on the contents of field 2:

Original file users.csv:
Col1,Col2,Col3
slash,uk,male
jbloggs,uk,female
flash,ca,female
fbloggs,ca,female
xlash,ca,male
xbloggs,ca,female

Final file 
"Col1","Col2","Col3","col4"
"slash","uk","male","Eastern"
"jbloggs","uk","female","Eastern"
"flash","ca","female","Western"
"fbloggs","ca","female","Western"
"xlash","ca","male","Western"
"xbloggs","ca","female","Western"

Code:
Import-Csv .\users.csv | ForEach-Object {
	if ($_.Col2 -like 'u*') {
		$Hemisphere = 'Eastern'
	}
	elseif ( $_.Col2 -like 'c*' ) {
		$Hemisphere = 'Western'
	}
	else {
	$Hemisphere = ''
	}
	$_
} | select-object Col1,Col2,Col3,"col4" | ForEach-Object { $_.col4 = $Hemisphere; return $_ } | Export-Csv .\test-modified.csv
4

There is no reason to enumerate two times, just use a calculated property or build a new object in the first loop:

$results = foreach ($user in (Import-Csv .\users.csv)) {
    switch -Wildcard ($user.Col2) {
        "u*"{$Hemisphere = 'Eastern'}
        "c*"{$Hemisphere = 'Western'}
        default{$Hemisphere = $null}
    }

    $user |
    Select *,
           @{Name="Col4";Expression="$Hemisphere"}
}

$results | Export-Csv .\test-modified.csv -NoTypeInformation
5

Hi Rob,Dunlap,

Thanks for the simplified version of the script from Dunlap, like the logic and would love to use it (as i have several unique values to go in Col4), however, i am afraid the script does not return the expected results. It does creates the new col4 fine, however, does not transfers the values/data from the variable. I does however, transfers all the existing values in Col 1-3 to the new CSV. (Ddunlap’s script works fine out of the box, but i have few unique values, i am unsure if adding too many ElseIf statements is a good idea?! but thanks to Dunlap as well for this post).
I tried to hash out “default{$Hemisphere = $null}” but that did not help i am afraid. The “write-host $Hemisphere” does print the value, western as an example.

more importantly, I am trying to do add bunch of users (5000+) to relevant AD Groups. I have a CSV (File1.csv) with just two attributes/columns, employeeNumber (Col1) JobFamily (Col2). (I don’t have any other AD attributes on this report, like SAM, UPN, Email etc.). I have a need to add these users behind these employeeNumbers to relevant AD Groups that match the JobFamily of that user. While EmployeeNumber is populated with a Unique number in AD, JobFamily is not an AD Attribute. However, I have a relevant AD Group that matches closely with each JobFamily. So I am able to use JobFamily as a string and use it to filter relevant group names and add it as a new Column in CSV.
In my head, I can accomplish this by:

  1. first using the JobFamily (Col2) string to convert to relevant AD Group names (example: GL_AppName_RoleName) and populate that in Col3, then export the results to File2.csv using either of your scripts.

  2. Then import the File2.csv into ForEach loop to extract Samaccountname of each user and put that in Col4 (maybe using [Get-ADUser -Filter “EmployeeNumber -eq ‘$($_.EmpNo)’”]) and Export this to File3.csv (or a variable)

  3. Finally use the File4.csv to add user $SAM (Col4) to relevant group $Group (Col3).

  4. optionally, there is also a need to remove the users from groups if the Jobfamily changes. Basically, use Filters [ Where-Object -FilterScript {$_.Name -ne “GL_APP1”}] to remove any of the specific groups from all users and then run the script again with the new data?! Hope this all makes some sense!?

thanks much for your time!