Powershell/VB/Batch/Magic - Add Text to Start and End of CSV Column

[JRA]

Evening all. I am sure this is dead simple but after honestly trying a while now I have come up very empty. And I'm sure it must be the easiest thing in the world I'm just not succeeding at all at this.

 

I have a .csv file. Example below:

 

Name,Surname,Reg

Freddy,Mercury,class1

Elvic,Presley,class2

Cher,Cher,class1

Jerry,Only,class3

 

I want something to add some text to the beginning and end of column 3/the Reg column, so it then all loos like:

 

Name,Surname,Reg

Freddy,Mercury,[email protected]

Elvic,Presley,[email protected]

Cher,Cher,[email protected]

Jerry,Only,[email protected]

 

I got lost after ages of trying to create arrays in powershell. This one is beyond me and it's so simple to run a concatenate and knock some columns up in an actual spreadsheet, I just can't seem to script this in any way shape or form?

 

Anyone feeling kind? :/

 

Thanks any/everybody.

[jb2201]

Evening all. I am sure this is dead simple but after honestly trying a while now I have come up very empty. And I'm sure it must be the easiest thing in the world I'm just not succeeding at all at this.

 

I have a .csv file. Example below:

 

Name,Surname,Reg

Freddy,Mercury,class1

Elvic,Presley,class2

Cher,Cher,class1

Jerry,Only,class3

 

I want something to add some text to the beginning and end of column 3/the Reg column, so it then all loos like:

 

Name,Surname,Reg

Freddy,Mercury,[email protected]

Elvic,Presley,[email protected]

Cher,Cher,[email protected]

Jerry,Only,[email protected]

 

I got lost after ages of trying to create arrays in powershell. This one is beyond me and it's so simple to run a concatenate and knock some columns up in an actual spreadsheet, I just can't seem to script this in any way shape or form?

 

Anyone feeling kind? :/

 

Thanks any/everybody.

 

$csv = import-csv “path to csv.csv”

Foreach ($row in $csv)
{
$row.reg = “new_” + $row.reg + “@school.com”
}

$csv | export-csv “path to new csv.csv”

 

Should work I think, written on my mobile though so it may need some tweaking / quotes changing!

[chaplic]

I'm not sure assigning a new value to row.reg will see it go back into the csv array (but am too lazy to try, perhaps JRA can tell us)

 

 

Alternatively, my original code modified to include that

 

$data=import-csv  "book1.csv"
$output = "book1new.csv"
foreach ($line in $data)
{

   if ($line.'Class Name' -eq "")
   {
       write-host -foregroundcolor Magenta "deleting row as blank class on row name $($line.name)"
   }
   elseif ($line.'Class Name' -match "^[0-9]" )
   {
       write-host -foregroundcolor Magenta "deleting row as Class starts with number: $($line.'class name')"
   }
   else
   {
       #If we are here the class column aint empty nor starts with a number"
       $newclassname = $line.'Class Name' -replace ('/','_')
       $newclassname = $newclassname -replace (':','_')
       $newclassname = $newclassname -replace (' ','_')
 [b]       $newclassname = $("new_" + $newclassname + '@school.com')[/b]
       $line.'Class Name' = $newclassname
       write-host -ForegroundColor Green "Writing out $line"
       $line | export-csv $output -Append -NoTypeInformation
   }
   
}

[JRA]

I honestly don't know how you folks do it. I was googling this and that and trying any old garbage for a SOLID afternoon and not even getting close...

 

Thank you both extremely much for all that.