Windows 10 Copy lines of text between two files, Using list / string matches.

P

Polycount

New Member
Joined
Jun 14, 2016
Messages
3
After searching everywhere I'm still unable to find a solution. I have two text documents, one is a list of names the other is coordinates.
both documents start each line with an 8 digit code.
I want to lookup 8 digit line code from File1 and copy the line contents to only code matches in File2.

(File1) Only one occurence of Code / Name.
Code: 
136667ED ap1_01_a_ap1_01_rails_07
035B337C ap1_01_a_arrows_005
79546F82 ap1_01_a_centreline_010
0E1D31E7 prop_bush_med_02

(File2) Some have multiple Code copies, like 0E1D31E7. With different coordinates.
Code: 
136667ED -1294.6945,-2376.0317,21.8279
035B337C -1314.6719,-2721.7378,12.9467
79546F82 -1283.1066,-2529.9771,12.9635
0E1D31E7 1919.4160,-1814.3889,160.5210
0E1D31E7 1919.9885,-2628.2529,0.7537
0E1D31E7 192.0235,-2603.1790,4.9978
0E1D31E7 192.1050,4950.3540,389.4736

(Result) The 8 Digit code / name, copied into any code line match in file 2. The name only added to any match would suffice.
Code: 
136667ED -1294.6945,-2376.0317,21.8279           136667ED ap1_01_a_ap1_01_rails_07
035B337C -1314.6719,-2721.7378,12.9467           035B337C ap1_01_a_arrows_005
79546F82 -1283.1066,-2529.9771,12.9635           79546F82 ap1_01_a_centreline_010
0E1D31E7 1919.4160,-1814.3889,160.5210           0E1D31E7 prop_bush_med_02
0E1D31E7 1919.9885,-2628.2529,0.7537             0E1D31E7 prop_bush_med_02
0E1D31E7 192.0235,-2603.1790,4.9978              0E1D31E7 prop_bush_med_02
0E1D31E7 192.1050,4950.3540,389.4736             0E1D31E7 prop_bush_med_02
There are 75,000 names in File1, 370,000 lines of coordinates in File2.
Also I've converted them in excel, In hope there was a way to lookup and update all with names.
 
Solution
I did it in powershell, it's pretty basic, but it lets you specify Input1 (Unique entry file), Input2(Multiple instances file) -Output (merged output txt file) and -Index # the index for the column you want to match by. You would just need to save the code in a <Filename>.ps1 file and open an elevated Powershell promtp and type Set-ExecutionPolicy Unrestricted so you can run the script.



Code: 
param(
[string]$Input1,
[string]$Input2,
[string]$Output,
[int]$Index)


# Read in the file content
#unique entry file
$file1 = Get-Content -Path $Input1
#multiple instances
$file2 = Get-Content -Path $Input2

# create an empty array to hold the merged data
$merged = @()

# Index should start at 1, but we need to normalize it since arrays start at...
Sort by date Sort by votes
I did it in powershell, it's pretty basic, but it lets you specify Input1 (Unique entry file), Input2(Multiple instances file) -Output (merged output txt file) and -Index # the index for the column you want to match by. You would just need to save the code in a <Filename>.ps1 file and open an elevated Powershell promtp and type Set-ExecutionPolicy Unrestricted so you can run the script.



Code: 
param(
[string]$Input1,
[string]$Input2,
[string]$Output,
[int]$Index)


# Read in the file content
#unique entry file
$file1 = Get-Content -Path $Input1
#multiple instances
$file2 = Get-Content -Path $Input2

# create an empty array to hold the merged data
$merged = @()

# Index should start at 1, but we need to normalize it since arrays start at 0
if ($index -eq $null)
{
    $index = 0
} else
{
    $index--
}
ForEach ($line In $file2)
{
    $parsedLine = $line.Split(' ')


    ForEach ($uniqueLine In $file1)
    {
        $parseUniqueLine = $uniqueLine.Split(' ')
        If ($parsedLine[$index] -ieq $parseUniqueLine[$Index])
        {
            $merged = $merged + @("$parseUniqueLine `t$parsedLine")
            break
        }
    }
}

Out-File -InputObject $merged -FilePath $output
 
Upvote 1 Downvote
Solution
Tested it on a small section of data. It worked nicely
I'm processing the entire files now. It has been 30 mins so far. Blinking cursor
Thanks for the code Neemobeer.

Found a tutorial, for excel which worked
 
Last edited:
Upvote 0 Downvote
You must log in or register to reply here.