CSV searching, etc

PowerShell Help
1

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.

2

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…