The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

munchingbill (2) [Avatar] Offline
#1
I am new to PowerShell and am trying to write a script that
will join the lines from 2 text files keyed by a common field
into a third file. This would be the equivalent of the linux
"join" command.

Example: File1 contains lines like:
KEY1~FLD1~FLD2~FLD3~FLD4~FLD5
KEY2~FLD1~FLD2~FLD3~FLD4~FLD5

File2 contains lines like:
KEY1~KEY11~FLD6~FLD7~FLD8
KEY2~KEY22~FLD6~FLD7~FLD8

I would like to create a third file containing:
KEY11~FLD1~FLD2~FLD3~FLD4~FLD5~FLD6~FLD7~FLD8
KEY22~FLD1~FLD2~FLD3~FLD4~FLD5~FLD6~FLD7~FLD8

The third file would replace the key field in file1
with a new key from file2 but retain all other fields
from both files. In this example, the ~ is the field separator.
The fields and lines may vary in length. File1 may contain
multiple lines with the same key, but file2 will only contain
1 line per key. Lastly, file1 may contain up to 500,000 lines.

What's the most efficient way to do this in PowerShell?
Thanks.
munchingbill (2) [Avatar] Offline
#2
Re: Join text files on key field
Here's my first draft. It works, but I'm a newby, so any suggestions to improve this are much appreciated.
Put this into a .ps1 file:
<<
param([String] $join1, [String] $lookup2, [String] $out1)
# Step1: load hash table to lookup keys; values are remaining fields
get-content "$lookup2" | foreach { $hash = @{}} {
$d, $f = $_.split('~');
$hash[$d] = $f;} {
# Some output for debugging if desired
# '$d is: ' + $d + '; $hash[$d] is: ' + $hash[$d]
'Number of records in $hash: ' + $hash.psbase.keys.count
# $hash
}
# Step2: parse each line, split fields, compose new line, write to file
foreach ($line in (Get-Content "$join1")){
$a, $b = $line.split('~');
#'$a is: ' + $a + '; $hash[$a] is: ' + $hash[$a]
[String[]]$g = $hash[$a];
#'$g[0] is: ' + $g[0]
$b = [system.string]::Join("~",$b)
$newln = $g[0] + '~' + [system.string]::Join("~",$b) + '~' +
[system.string]::Join("~",$g[1..($g.length - 1)]);
$newln | Out-File "$out1" -append -encoding ASCII -width 100}
# Step3: display outfile contents
Get-Content "$out1"
>>