Add and Populate Column in CSV File

This topic contains 3 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 6 months, 1 week ago.

  • Author
    Posts
  • #5044

    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.

  • #43542

    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?

  • #60207
    Profile photo of ddunlap
    ddunlap
    Participant
    
    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
    
    
  • #60222
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    

You must be logged in to reply to this topic.