Author Posts

September 11, 2018 at 9:23 pm

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.

September 11, 2018 at 10:10 pm

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

 

September 12, 2018 at 4:15 am

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.

September 12, 2018 at 2:15 pm

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

September 12, 2018 at 2:51 pm

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.

September 12, 2018 at 5:36 pm

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

September 12, 2018 at 10:25 pm

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 ?

September 13, 2018 at 5:13 pm

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.

September 13, 2018 at 5:54 pm

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

September 13, 2018 at 8:55 pm


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