- Thread Author
-
- #1
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.
(File2) Some have multiple Code copies, like 0E1D31E7. With different coordinates.
(Result) The 8 Digit code / name, copied into any code line match in file 2. The name only added to any match would suffice.
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.
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_02Also I've converted them in excel, In hope there was a way to lookup and update all with names.
Solution
- Joined
- Jul 4, 2015
- Messages
- 8,998
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
Last edited by a moderator:
- Thread Author
-
- #5
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
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: