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

Discussion in 'Programming and Scripting' started by Polycount, Jun 14, 2016.

  1. Polycount

    Polycount New Member

    Joined:
    Jun 14, 2016
    Messages:
    3
    Likes Received:
    0
    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.
     
  2. Neemobeer

    Neemobeer Windows Forum Team
    Staff Member

    Joined:
    Jul 4, 2015
    Messages:
    2,412
    Likes Received:
    366
    So are the input files text or excel and what do you want the output file to be text or excel?
     
  3. Polycount

    Polycount New Member

    Joined:
    Jun 14, 2016
    Messages:
    3
    Likes Received:
    0
    The originals are text, which is my desired format. However excel tables would be useable.
     
  4. Neemobeer

    Neemobeer Windows Forum Team
    Staff Member

    Joined:
    Jul 4, 2015
    Messages:
    2,412
    Likes Received:
    366
    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.

    command.PNG

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

    Polycount New Member

    Joined:
    Jun 14, 2016
    Messages:
    3
    Likes Received:
    0
    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
     
    #5 Polycount, Jun 14, 2016
    Last edited: Jun 14, 2016

Share This Page

Loading...