Replace values in a CSV based on another CSV

Welcome Forums General PowerShell Q&A Replace values in a CSV based on another CSV

This topic contains 9 replies, has 3 voices, and was last updated by

 
Participant
3 months ago.

  • Author
    Posts
  • #111619

    Participant
    Points: 1
    Rank: Member

    I'm a relative novice in PowerShell. As a result, sometimes there's just a basic mechanic I'm missing or misunderstand that slows me down an awful lot. In this case, it feels like there's something really basic with a foreach and maybe a hashtable  (I don't fully understand hashtables yet) that would solve this – but the answer has eluded me after a couple hours of using bing and experimenting. That said, I love learning PS – it's been crazy helpful in my day to day tasks. Any help on this issue would be greatly appreciated!

    I have a CSV I've imported into $s_file. It has a bunch of columns in it. The last 5 columns have numerical values.

    I have another CSV file I've imported into $lookup_file. It has two columns: lookup_id and name.

    I want to replace every instance of lookup_id in $s_file and replace it with name.

    I'd prefer the result was exported into $r_file and that $s_file was unmodified (so I can go back to the source later if needed).

    Thanks in advance.

  • #111622

    Participant
    Points: 85
    Rank: Member

    This code region will generate sample data:

    #region Replicate environment
    
    1..5 | % { # Generate sfile sample
    
    [PSCustomObject][Ordered]@{
    
    ComputerName = "Alpha-$(Get-Random -Minimum 1 -Maximum 999)"
    
    IPAddress    = "$(Get-Random -Min 1 -Max 254).$(Get-Random -Min 1 -Max 254).$(Get-Random -Min 1 -Max 254).$(Get-Random -Min 1 -Max 254)"
    
    RAM          = (Get-Random -Min 1 -Max 4) * 16
    
    Disk1        = (Get-Random -Min 1 -Max 4) * 32
    
    Disk2        = (Get-Random -Min 1 -Max 4) * 64
    
    Disk3        = (Get-Random -Min 1 -Max 4) * 48
    
    Disk4        = (Get-Random -Min 1 -Max 4) * 96
    
    Lookup_ID    = $PSItem
    
    }
    
    } | Export-Csv .\sFile.csv -NoTypeInformation
    
     
    
    1..5 | % { # Generate lookupFile sample
    
    [PSCustomObject][Ordered]@{
    
    Lookup_ID = $PSItem
    
    Name      = "Lookup Name $PSItem"
    
    }
    
    } | Export-Csv .\lookupFile.csv -NoTypeInformation
    
    #endregion
    
    

    These 2 lines will read the CSV files

    $s_fileData      = Import-Csv .\sfile.csv
    
    $lookup_fileData = Import-Csv .\lookupFile.csv
    
    

    Each of the 2 variables above will have an array which has 5 elements. Each element is a PS Object corresponding to a row in the source CSV.

    Now we stitch the two objects together based on the common property 'Lookup_ID'. This is the same thing as 'joining' two tables in a database:

    $myNewJointArray = foreach ($sRecord in $s_fileData) {
    
    foreach ($lookupRecord in $lookup_fileData) {
    
    if ($sRecord.Lookup_ID -eq $lookupRecord.Lookup_ID) {
    
    [PSCustomObject][Ordered]@{
    
    ComputerName = $sRecord.ComputerName
    
    IPAddress    = $sRecord.IPAddress
    
    RAM          = $sRecord.RAM
    
    Disk1        = $sRecord.Disk1
    
    Disk2        = $sRecord.Disk2
    
    Disk3        = $sRecord.Disk3
    
    Disk4        = $sRecord.Disk4
    
    Lookup_ID    = $sRecord.Lookup_ID
    
    Name         = $lookupRecord.Name
    
    }
    
    }
    
    }
    
    }
    
    

    Now the $myNewJointArray  has both the Lookup_ID and Name properties. If you don't need the Lookup_ID property/column, you can just remove the line

    Lookup_ID    = $sRecord.Lookup_ID
    
    

    The output can be put back to CSV as in:

    $myNewJointArray | Export-Csv .\myOutFile1.csv -NoTypeInformation
    
    

     

  • #111629

    Participant
    Points: 861
    Helping Hand
    Rank: Major Contributor

    If I assumed your CSV correctly, below lines of code with a combination of Hashtable and Calculated property will help you.

    $Lookup_Hash = Import-Csv -Path C:\LookupFilePath.csv | ForEach-Object -Process { $_.lookup_id = $_.name }
    $S_File      = Import-Csv -Path C:\S_FilePath | Select-Object -Property *,@{E={$Lookup_Hash.($_.lookup_id)};L='Lookup_Id'}
    
    $S_File | Export-Csv -Path C:\NewCsv.csv -NoTypeInformation
    

    It would be great if you share(a sample) of you source, so that whoever tries to help you can have more picture.

  • #111659

    Participant
    Points: 1
    Rank: Member

    Thanks so much for your responses. I sincerely appreciate it.

    @kvprasoon – you're right, some sample of the CSVs would be helpful. I also don't understand what line two of your code is doing – can you explain?

    $s_file = Import-CSV -Path c:\sfoo.csv
    $lookup_file = Import-CSV -Path c:\lf.csv
    

    Here's a sample of the $s_file:

    And here's a sample of the $lookup_file:

    lookup_id	lookup_value
    9	        Sunday
    10	        Monday
    11	        Tuesday
    12	        Wednesday
    13	        Thursday
    14	        Friday
    15	        Saturday
    32	        Women
    855	        Group Leader
    984	        Captain
    10010	       Men
    10334	       Married
    10373	       Mentor
    10421	       Men
    
  • #111670

    Participant
    Points: 861
    Helping Hand
    Rank: Major Contributor
    Import-Csv -Path C:\S_FilePath | Select-Object -Property *,@{E={$Lookup_Hash.($_.lookup_id)};L='Lookup_Id'}

    the above line of code does.
    Imports the csv, selects all the properties from the output + create a new Note property with custom values.
    $Lookup_Hash is a hashtable with lookup_id as key and name as value. The calculated noteproperty uses the hash table to pick names for each respective lookup_ids .

    More info on calculated properties here.

    • #111676

      Participant
      Points: 1
      Rank: Member

      When I attempt to run this, I get the following error repeatedly across the screen:

      Exception setting "lookup_id": "The property 'lookup_id' cannot be found on this object. Verify that the property exists and can be set."

      It's like it's not finding the column...

  • #111686

    Participant
    Points: 861
    Helping Hand
    Rank: Major Contributor

    I was assuming, lookup_id in source file as well, there should be at least one property in common.
    What property is in common across both the CSVs ?

    • #111739

      Participant
      Points: 1
      Rank: Member

      The values in the lookup_id column of the $lookup_table file exist in the field_834, field_835, field_836, field_837, and field_838 fields of the $s_file file.

      Said differently, they don't have a column header in common. The ID values from the lookup table file fill the last five columns of the s_file. I want to replace all those numbers with the appropriate names.

  • #111743

    Participant
    Points: 861
    Helping Hand
    Rank: Major Contributor

    Extend your thoughts...

    Import-Csv -Path C:\S_FilePath | Select-Object -Property worker_name,requester_name,@{E={$Lookup_Hash.($_.field_834)};L='field_834'},@{E={$Lookup_Hash.($_.field_835)};L='field_835
    '},@{E={$Lookup_Hash.($_.field_836)};L='field_836
    '},@{E={$Lookup_Hash.($_.field_837};L='field_837
    '},@{E={$Lookup_Hash.($_.field_838)};L='field_838
    '}
    
    

    or

    $Poproperties = @(
    'worker_name',
    'requester_name',
    @{E={$Lookup_Hash.($_.field_834)};L='field_834'},
    @{E={$Lookup_Hash.($_.field_835)};L='field_835'},
    @{E={$Lookup_Hash.($_.field_836)};L='field_836'},
    @{E={$Lookup_Hash.($_.field_837};L='field_837'},
    @{E={$Lookup_Hash.($_.field_838)};L='field_838'}
    )
    Import-Csv -Path C:\S_FilePath | Select-Object -Property $Poproperties
  • #111758

    Participant
    Points: 85
    Rank: Member
    
    $s_fileData      = Import-Csv .\sfile2.csv
    
    $lookup_fileData = Import-Csv .\lookupFile2.csv
    
     
    
    $New_S_File = Copy-Object -Object $s_fileData
    
     
    
    $FieldNameList = ($s_fileData | Get-Member -MemberType NoteProperty).Name | where { $PSItem -match 'field' }
    
    foreach ($sRecord in $New_S_File) {
    
    foreach ($FieldName in $FieldNameList) {
    
    $ReplacementValue = ($lookup_fileData | where { $sRecord.$FieldName -eq $PSItem.lookup_id }).lookup_value
    
    if ($ReplacementValue) {
    
    $sRecord.$FieldName = $ReplacementValue
    
    } else {
    
    $sRecord.$FieldName = "$($sRecord.$FieldName) (No matching value found in lookup file)"
    
    }
    
    }
    
    }
    
     
    
    $New_S_File
    
    

    output:

    
    worker_name    : Riley, Tom
    
    requester_name : Flanagan, Levi
    
    field_834      : 10434 (No matching value found in lookup file)
    
    field_835      : 10431 (No matching value found in lookup file)
    
    field_836      : 335 (No matching value found in lookup file)
    
    field_837      :  (No matching value found in lookup file)
    
    field_838      :  (No matching value found in lookup file)
    
     
    
    worker_name    : Riley, Tom
    
    requester_name : Flanagan, Joseph
    
    field_834      : 10434 (No matching value found in lookup file)
    
    field_835      : 10431 (No matching value found in lookup file)
    
    field_836      : Sunday
    
    field_837      :  (No matching value found in lookup file)
    
    field_838      :  (No matching value found in lookup file)
    
     
    
    worker_name    : Gastineau, Jeff
    
    requester_name : Watson, Tiffany
    
    field_834      : 10432 (No matching value found in lookup file)
    
    field_835      : 10431 (No matching value found in lookup file)
    
    field_836      : 335 (No matching value found in lookup file)
    
    field_837      :  (No matching value found in lookup file)
    
    field_838      :  (No matching value found in lookup file)
    
    

    You can remove the 'else' part of the if statement if you don't want the (No matching...) comment..

     

The topic ‘Replace values in a CSV based on another CSV’ is closed to new replies.