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