Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    44
    Thanks
    5
    Thanked 1 Time in 1 Post

    Powershell - Search for Matching of Column in 2nd CVS file

    Hi,

    I have two comma separated csv files, File1.csv & File2.csv, 1st row header Date,Particulars,Amount,Type,Balance.

    Search starts from 1st record of File1.csv of column Particulars into File2.csv for search matching (alphanumeric string,match means partial 10% will do),
    and if it finds a partial match also it will display the complete row for acceptance or rejection of row.

    If accepted move the row to new file from both the csv file to reconresult.csv
    If rejection is pressed searching new possibles in the column Particulars in File2.csv to the last record (around 600 rows of data), if no search then scroll to next record in File1.csv

    For search ignore start tags like WIPE,CLIENT,DIRECT,PAIDTO: because they are default remarks which will scroll to each and every record better to start after '/' ':'

    File1.csv
    15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,,
    15-05-2017,WIPE/000015778881/10100002898/TECHNOLOGIES,450000,DR,545360,,
    15-05-2017,WIPE/000015113345556000026796/CREDIT CARD,15300,DR,5463800

    File2.csv
    15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,,
    15-05-2017, WIPE/000015778881/TURBO,45000.00,CR,6673880,,
    15-05-2017, WIPE/000034778483/CREDIT CARD,10000,DR,1275700,,
    15-05-2017,WIPE/CREDIT CARD,15300,CR,7563700,,

    Reconresult.csv
    15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,,
    15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,,

    15-05-2017,WIPE/000015778881/10100002898/TECHNOLOGIES,450000,DR,545360,,
    15-05-2017, WIPE/000015778881/TURBO,45000.00,CR,6673880,,

    15-05-2017,WIPE/000015113345556000026796/CREDIT CARD,15300,DR,5463800
    15-05-2017,WIPE/CREDIT CARD,15300,CR,7563700,,
    Last edited by zmagic; 2017-05-27 at 09:00.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,971
    Thanks
    424
    Thanked 1,600 Times in 1,450 Posts
    zMagic,

    That's a toughie and I'm still working on doing the fuzzy match! However, I've got the rest of the code down and just need to finalize the search, it it is even possible and I have my doubts!

    Code:
    <#+-----------------------------------------------------------------------+
      | Compare 2 CSV files and Output file of matching items.                |
      |                                                                       |
      | Version      : 1.0                                                    |
      | Programmed by: RetiredGeek of WSL                                     |
      | Date         : 27 May 2017                                            |
      +-----------------------------------------------------------------------+
    #>
    
    Clear-Host
    
    $BasePath       = "G:\BEKDocs\Scripts"
    $FileSpec1      = "$BasePath\File1.csv"   
    $FileSpec2      = "$BasePath\File2.csv"
    $OutputFileSpec = "$BasePath\Matches.csv"   
    
    
    #--- Load Files ---
    $File1 = import-csv -Path $FileSpec1  -Delimiter ',' 
    $File2 = import-csv -Path $FileSpec2  -Delimiter ',' 
    
    #Create Output Object
    [array]$Output = $File1[0].PsObject.Copy()
    
    #--- Compare File1 record to every File2 Record
    ForEach ($Rec1 in $File1) {
    
      ForEach ($Rec2 in $File2) {
    
       If ($Rec1.Particulars -ne $Rec2.Particulars) {
    
          "$($Rec1.Particulars)`n$($Rec2.Particulars)"
          If ((Read-Host -Prompt "Accept Match [Y/N]?") -eq 'y') {
    
            If ($Output.count -ne 1) {
              $Output+=$Rec1
            }
            Else {
              $Output = $Rec1
            }
            $Output+=$Rec2
          }  
    
        } #End If
    
      } #End ForEach ($Rec2...
    
    } #End ForEach (Rec1...
    
    $ExCSVArgs = @{Path              = "$OutputFileSpec"
                   Delimiter         = ',' 
                   NoTypeInformation = $True
                   Force             = $True}
     
    $output | Export-csv @ExCSVArgs 
    
    #--- If you want " around fields comment out the next 3 lines
    $a = (get-content $OutputFileSpec)
    $a[0..($a.count -1)] -replace '"' | 
       Set-Content $OutputFileSpec
    The line in Blue above is set so that all entries will match thus allowing me to test the rest of the code by making everything show up as a match (-ne , not equal)

    Sample Run:
    Code:
    WIPE/PPKKB17137737564/1328/TOP PLAST PRIV
    WIPE/TOP PLAST
    Accept Match [Y/N]?: y
    WIPE/PPKKB17137737564/1328/TOP PLAST PRIV
    WIPE/000015778881/TURBO
    Accept Match [Y/N]?: 
    WIPE/PPKKB17137737564/1328/TOP PLAST PRIV
    WIPE/000034778483/CREDIT CARD
    Accept Match [Y/N]?: 
    WIPE/PPKKB17137737564/1328/TOP PLAST PRIV
    WIPE/CREDIT CARD
    Accept Match [Y/N]?: 
    WIPE/000015778881/10100002898/TECHNOLOGIES
    WIPE/TOP PLAST
    Accept Match [Y/N]?: 
    WIPE/000015778881/10100002898/TECHNOLOGIES
    WIPE/000015778881/TURBO
    Accept Match [Y/N]?: y
    Output file:
    Code:
    Date,Particulars,Amount,Type,Balance
    15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000
    15-05-2017,WIPE/TOP PLAST,1000000,DR,500000
    15-05-2017,WIPE/000015778881/10100002898/TECHNOLOGIES,450000,DR,545360
    15-05-2017,WIPE/000015778881/TURBO,45000.00,CR,6673880
    15-05-2017,WIPE/000015113345556000026796/CREDIT CARD,15300,DR,5463800
    15-05-2017,WIPE/000034778483/CREDIT CARD,10000,DR,1275700
    15-05-2017,WIPE/000015113345556000026796/CREDIT CARD,15300,DR,5463800
    15-05-2017,WIPE/CREDIT CARD,15300,CR,7563700
    Of course the code could be enhanced to use dialog boxes or menus making it mouse enabled vs the text interface I used for proof of concept.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Oct 2013
    Posts
    44
    Thanks
    5
    Thanked 1 Time in 1 Post
    Hi,

    Thank you RG for helping, still i am facing difficulties in reconciling the entries, kindly help.

    1. Display complete row on screen because its manual reconciliation one to one entry.
    15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,, -File1.csv
    15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,, - File2.csv
    Accept Match [Y/N]?:

    2. Highlight the search area.
    15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,,
    15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,,
    Accept Match [Y/N]?:

    2. Accepted (y) means compulsorily rows to be moved from File1 & File2 to Matches.csv, and continue to next record in File1.csv
    Right now after Accept Match [Y/N]?: Y then to it is going for other search/match.

    3. Search to be based after "/" ":" ":-" starting remarks like WIPE,CLIENT,DIRECT,PAIDTO: Account:- have to be ignored.
    Else this is making us to go till last record searching,searching for match.

    4. The best search will be characters type then numberic type.
    TOP PLAST PRIV
    TOP PLAST

    5. Disable other keys only y or n enabled.

    6. If possible allow a skip key to skip search of particular record in File1.csv

    Thanking you in advance.
    Last edited by zmagic; 2017-05-27 at 15:33.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,971
    Thanks
    424
    Thanked 1,600 Times in 1,450 Posts
    zMagic,

    It appears you have posted this same problem in the Spreadsheets forum and received a workable solution from Maudibe. If you are going to post your problems in different forums at least cross reference them so we don't spin our wheels when you already have an answer.

    See: Lounge Rules, specifically #14.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •