Replace values in a CSV based on another CSV

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.

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

 

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.

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:
https://gist.github.com/LimitedWisdom/5261827d31ad40f73912ef74e9524929

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
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.

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…

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 ?

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.

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
$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…