How to Compare two columns of one csv file with another in VBScript

Question

I have two csv files one with four columns and another with four columns, for eg

File 1                     File 2
ID,Contry,state,amt        ID,Contry,state,amt 
1,US,01,7                  13,AU,0,7 
2,UK,11,7                  11,UK,0,7 
3,AF,10,7                  10,AF,0,7 
4,US,08,7                  14,SG,0,7 

I need two compare the second two columns of file 1 with the first two coulmns of file2 and remove the duplicates of matched rows in file2 using vbscript. for eg the output needs to be

file3
ID,Contry,state,amt 
13,AU,0,7
14,SG,0,7

Sample codes will be helpful.

Answer 1

The standard way to work with .CSV is to use ADO/Text Drivers (cf here). If the data are funny, or specs are too vague for nice SQL statements (or you never used SQL before), a hack involving the FileSystemObject, string ops, and a Dictionary to store the distinct values in the first file's second column may be acceptable:

  Dim oFS   : Set oFS   = CreateObject("Scripting.FileSystemObject")
  Dim dicC  : Set dicC  = CreateObject("Scripting.Dictionary")
  Dim tsOut : Set tsOut = oFS.CreateTextFile("..\data\25909493-3.txt")
  Dim tsIn
  Set tsIn = oFS.OpenTextFile("..\data\25909493-1.txt")
  tsOut.WriteLine tsIn.ReadLine() ' header
  Do Until tsIn.AtEndOfStream
     dicC(Split(tsIn.ReadLine(), ",")(1)) = 0 ' col 2 into dicC.Keys
  Loop
  tsIn.Close
  WScript.Echo "to filter:", Join(dicC.Keys(), ", ")
  Set tsIn = oFS.OpenTextFile("..\data\25909493-2.txt")
  tsIn.ReadLine
  Do Until tsIn.AtEndOfStream
     Dim sLine : sLine = tsIn.ReadLine()
     If Not dicC.Exists(Split(sLine, ",")(1)) Then
        tsOut.WriteLine sLine
     End If
  Loop
  tsIn.Close
  tsOut.Close
  WScript.Echo "..\data\25909493-3.txt:"
  WScript.Echo oFS.OpenTextFile("..\data\25909493-3.txt").ReadAll()

output:

to filter: US, UK, AF
..\data\25909493-3.txt:
ID,Contry,state,amt
13,AU,0,7
14,SG,0,7