CSV searching, etc

Welcome Forums General PowerShell Q&A CSV searching, etc

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
3 weeks, 4 days ago.

  • Author
    Posts
  • #172912

    Participant
    Topics: 2
    Replies: 1
    Points: 24
    Rank: Member

    I have an interesting challenge.  I need to search for a string contained in column d of CSV 1 within any column of CSV2.  Once I find that value in CSV2, I need to take columns 1, 2 and 3 from CSV2 and insert that data into the same row we're searching from in CSV1.

    Example:

    CSV1 Columns: ID,FName,LName,Phone

    Empty,Empty,Empty,1111111111

    CSV2 Columns: ID, FName,Lname,Phone1,Phone2,Phone3,Phone4,etc...  (there are about 30 more)

    0000123456789,Test,User,1111111111,2222222222,333333333,4444444444,empty,5555555555

     

    I need to search for the value of the phone number from file 1 in file 2, capture the values from that line in file 2 for ID, FName and LName.  The caveat to this is that file 2 contains over 1 million lines...

    Any help would be very much appreciated.

  • #172930

    Participant
    Topics: 9
    Replies: 423
    Points: 676
    Helping Hand
    Rank: Major Contributor

    The easiest way to do this is to import the 2 CSV files as tables in a new SQL database, and runs SQL query to find the inner junction of csv1.d against csv2.* and dump the matches in a new third table for further processing.
    Same can be done without SQL but with large files you'll have several hurtles to cross..

You must be logged in to reply to this topic.