0

I currently have this code that will compare between two files only if each file has one column:

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO.OpenTextFile("C:\Users\Downloads\Define Kickouts\Metadata_Account.txt", ForReading)
strCurrentDevices = objFile1.ReadAll
objFile1.Close

Set objFile2 = objFSO.OpenTextFile("C:\Users\Downloads\Define Kickouts\DataFile_Account.txt", ForReading)
Do Until objFile2.AtEndOfStream
    strAddress = objFile2.ReadLine
    If InStr(strCurrentDevices, strAddress) = 0 Then
        strNotCurrent = strNotCurrent & strAddress & vbCrLf
    End If
Loop
objFile2.Close

Wscript.Echo "Addresses without current devices: " & vbCrLf & strNotCurrent
Set objFile3 = objFSO.CreateTextFile("C:\Users\Downloads\Define Kickouts\Differences.txt")
objFile3.WriteLine strNotCurrent
objFile3.Close

However, I'm trying to figure out a way to create the script where the user can define which columns in the date file to compare against the same set of metadata files.

For example, in the data file, if we want to compare Account, Entity, and Department members, in the script, we would type in columns 1, 4, 5 based on the position in the headers...

Account, Project, Practice, Entity, Department  
GL1000,P5000,PP2000,USA,D120  
GL2000,P6000,PP3000,CANADA,D220

Then, the script will compare 'always' against the first column in each metadata file...

Account.csv 

First column sample values:  
GL5000,blah,blah,blah  
GL1000,blah,blah,blah

Entity.csv 

First column sample values:  
ASIA,blah,blah,blah  
CANADA,blah,blah,blah

Department.csv 

First column sample values:  
D100,blah,blah,blah  
D200,blah,blah,blah

The output file will have kick-outs from the data file that aren't in the metadata files for each column.

Account Kickout.txt
GL2000

Entity Kickout.txt
USA

Department Kickout.txt
D120
D220

Any help would be appreciated!

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
JHodges
  • 1
  • Do you need to do this is [tag:vba] or [tag:vbscript]? Similar but not interchangeable. – ashleedawg Jun 15 '18 at 21:27
  • Are your files all CSV? Might they contain values with embedded commas? One approach would be to read your "metadata" file into an array of lines (`ReadAll` then `Split()` on newline), then loop over that array and split each line into an array on the comma (assuming no embedded commas). Do something similar when reading the second file (split line by line, then pick out the items of interest and run against your jagged array from the first file) – Tim Williams Jun 15 '18 at 21:34
  • Vbscript. Actually, the data or metadata files can be csv or txt. – JHodges Jun 16 '18 at 02:11

0 Answers0