Powershell: Reading in a column from a .CSV file and then adding a specific string to the beginning and exporting again

Question

I'm attempting to write a script which will read in a CSV generated by querying AD for user information (that part is done) but then will allow me to add a string to the beginning of each value of a column in the CSV file and then export it.

For instance we have this CSV file:

"displayname","Office"
Bob,7142
Janet,8923
SantaClaus,0912
NicCage,0823

I want to take each entry for "Office", add the string "BUG" before it and then export it back out. The modified CSV should look like:

"displayname","Office"
Bob,BUG7142
Janet,BUG8923
SantaClaus,BUG0912
NicCage,BUG0823

At this point, I've been attempting to read in just the "Office" column and then displaying it with "Write-Host". The idea being that if I can do that then maybe I can create a new variable that would be something like:

$BUG = "BUG"
$NewVar = $BUG$Office

Which would hopefully look like the second CSV file. I am extremely new to powershell scripting.

The attempts I've made so far are these:

Attempt #1:

$UserList = Import-CSV C:\Users\username\CSV.csv
$UserList | ForEach-Object ($_.Office) { $UserList }

Attempt #2:

$projectName = import-csv  C:\Users\username\CSV.csv | % {$_.Office}
$BUG = "BUG"
$projectName | ForEach-Object ($_) {$projectName}

Attempt #3:

$UserList = Import-CSV C:\Users\username\CSV.csv
#ForEach ($Office in $Userlist) {
#Write-Host $UserList.Office
#}

Attempt #4:

Import-Csv "C:\Users\username\CSV.csv" -Header       ("displayname","Office","whenCreated","EmailAddress") | Select-Object Office | Export-CSV -Path C:\users\Username\test.csv

I have gotten it to read out just the Office numbers before using the ForEach-Object loop structure but then it never stops reading out the office numbers so that's unhelpful.

I think I'm going in the right direction, but I just can't figure out how to modify a column like this.

Mathias R. Jessen · Accepted Answer · 2015-10-22 15:48:18Z

2

Instead of trying to extract the Office column, just pipe the full data set (all columns) to ForEach-Object, change the value of the Office property and pipe it back to Export-Csv:

$Prefix = "BUG"
Import-Csv .\file.csv | ForEach-Object {
    $_.Office = $Prefix + $_.Office
    $_
} | Export-Csv .\file_modified.csv -NoTypeInformation

edited Oct 22, 2015 at 15:48

answered Oct 22, 2015 at 14:01

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Redcoatwright Over a year ago

Hmm, I attempted to run your code (changing to the correct paths, obviously) but it gave me just a blank .CSV file. I also tried to pipe it to Write-Host instead of Export-CSV to see if it would show what I was doing but it didn't show anything...weirdly.

Redcoatwright Over a year ago

Hey, that worked! Awesome, thank you very much! If I may, though, I tried (just out of curiosity) to pipe $_.Office instead of all of $_ to Export-CSV and it did something weird, it only exported the first number of each of the office numbers...what you gave me is good so no need to answer, I was just curious.

Mathias R. Jessen Over a year ago

@Redcoatwright Are your sure it didn't export the length of each Office string?

PeterX Over a year ago

I get the following at the top of the output file #TYPE System.Management.Automation.PSCustomObject

Collectives™ on Stack Overflow

Powershell: Reading in a column from a .CSV file and then adding a specific string to the beginning and exporting again

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related