dreamscapeuk 0 Posted January 14, 2011 Report Share Posted January 14, 2011 Hi there, I'm new to vbscripting so please help..I have 2 txt files with data like belowFile 1A/C Value40000 -1908363.0040100 20000.0040200 350004.0040300 -3498.99File 2A/C Value40000 -1908363.0040105 -234567.0040200 350.0040300 -3498.99Both files are in the same format (finally!) and I'm trying to compare them to list only the lines that are different in each file. First I did the standard read each line in file 1 and compare to all the lines of file 2 but this will miss anything in file 2 that isn't in file 1 unless i do the same in reverse. Someone suggested I put both files in a dictionary and compare. I have made an attempt but keep getting this error: Line 33: This key is already associated with an element of this collection.Also I'm not sure the results will be what i'm expecting as I wanted to get something like this:A/C File 1 File 240100 20000.00 ' where the account only exists in file 140105 -234567.00 ' where the account only exists in file 240200 350004.00 350.00 ' where the accoun exists in both files but the values are different.Any help please??????Here's my code: Const ForReading = 1, ForWriting = 2Set objDictionary = CreateObject("Scripting.Dictionary")' Declare the dictionaries.Dim Dict1 Dim Dict2 'Set txt filesSet objFSO = CreateObject("Scripting.FileSystemObject")Set objTextFile = objFSO.OpenTextFile("C:\VBScript\ReadFiles\OracleFile.txt", ForReading)Set objTextFile2 = objFSO.OpenTextFile("C:\VBScript\ReadFiles\HyperionFile.txt", ForReading)Set f = objFSO.OpenTextFile("C:\VBScript\ReadFiles\ResultsFile.txt", ForWriting, True)Set Dict1 = objDictionaryWith Dict1 'set compare mode.CompareMode = BinaryComparei = 0Do Until objTextFile.AtEndOfStream strNextLine = objTextFile.Readline .Add i, strNextLine i = i + 1LoopEnd WithSet Dict2 = objDictionaryWith Dict2 'set compare mode.CompareMode = BinaryCompare i = 0Do Until objTextFile2.AtEndOfStream strNextLine2 = objTextFile2.Readline .Add i, strNextLine2 i = i + 1LoopEnd With' Compare the two dictionaries.For Each objItem In Dict1 If Dict2.Exists(objItem) Then Dict2.Remove(objItem) Dict1.Remove(objItem) Else f.writeline Dict1(objItem) & vbCrLf f.writeline Dict2(objItem) & vbCrLf End if Nextf.CloseWScript.Echo "Done" Quote Link to post Share on other sites
gppkuntz 0 Posted March 23, 2012 Report Share Posted March 23, 2012 ' you set the keys to be integers starting at zero so your first line is in key 0, zero' I am assuming the first five charcters are account numbers and everything after that is the account value' You can change the msgboxes to writing to files without difficulty' do a split on each line using the space, arr(0) will be the key and arr(1) will be the valueConst ForReading = 1, ForWriting = 2'Set txt filesSet objFSO = CreateObject("Scripting.FileSystemObject")Set objTextFile1 = objFSO.OpenTextFile("C:\wherever\OracleFile.txt", ForReading)Set objTextFile2 = objFSO.OpenTextFile("C:\wherever\HyperionFile.txt", ForReading)'Set f = objFSO.OpenTextFile("C:\wherever\ResultsFile.txt", ForWriting, True)' setup the dictionariesSet Dict1 = CreateObject("Scripting.Dictionary")Set Dict2 = CreateObject("Scripting.Dictionary")Do Until objTextFile1.AtEndOfStream strNextLine = objTextFile1.Readline splitarr = split(strNextLine," ") if Dict1.exists(splitarr(0)) Then ' do nothing Else Dict1.Add splitarr(0), splitarr(1) End IfLoopDo Until objTextFile2.AtEndOfStream strNextLine = objTextFile2.Readline splitarr = split(strNextLine," ") if Dict2.exists(splitarr(0)) Then ' do nothing Else Dict2.Add splitarr(0), splitarr(1) End IfLoop' compare the keys and values of the two dictionariesarrDict1 = Dict1.keysarrDict2 = Dict2.keysstrDict1Keys = Join(arrDict1)strDict2Keys = Join(arrDict2)copyStrDict1Keys = strDict1KeyscopystrDict2Keys = strDict2Keys'msgbox "strDict2Keys = " & strDict2keyss = "accounts that exist in both files"sDifferentValues = "account is the same but values are different"for i = 0 to uBound(arrDict1) if InStr(strDict2Keys, Trim(arrDict1(i))) Then copyStrDict1Keys = replace(copyStrDict1Keys, Trim(arrDict1(i)), "") copystrDict2Keys = replace(copyStrDict2Keys, Trim(arrDict1(i)), "") s = s & vbcrlf & arrDict1(i) if Trim(Dict1.item(arrDict1(i))) <> Trim(Dict2.item(arrDict1(i))) Then sDifferentValues = sDifferentValues &vbcrlf & arrDict1(i) & ": " & Trim(Dict1.item(arrDict1(i))) & ": " & Trim(Dict2.item(arrDict1(i))) End If End IfNextmsgbox smsgbox sDifferentValuesmsgbox "accounts after removing matching: OracleFile " & copyStrDict1Keysmsgbox "accounts after removing matching: HyperionFile " & copyStrDict2Keys'f.closemsgbox "finished" Quote Link to post Share on other sites
gppkuntz 0 Posted March 23, 2012 Report Share Posted March 23, 2012 ' you set the keys to be integers starting at zero so your first line is in key 0, zero' I am assuming the first five charcters are account numbers and everything after that is the account value' You can change the msgboxes to writing to files without difficulty' do a split on each line using the space, arr(0) will be the key and arr(1) will be the valueConst ForReading = 1, ForWriting = 2'Set txt filesSet objFSO = CreateObject("Scripting.FileSystemObject")Set objTextFile1 = objFSO.OpenTextFile("C:\wherever\OracleFile.txt", ForReading)Set objTextFile2 = objFSO.OpenTextFile("C:\wherever\HyperionFile.txt", ForReading)'Set f = objFSO.OpenTextFile("C:\wherever\ResultsFile.txt", ForWriting, True)' setup the dictionariesSet Dict1 = CreateObject("Scripting.Dictionary")Set Dict2 = CreateObject("Scripting.Dictionary")Do Until objTextFile1.AtEndOfStream strNextLine = objTextFile1.Readline splitarr = split(strNextLine," ") if Dict1.exists(splitarr(0)) Then ' I assume an account will never occur twice but this is where you should write code just in case ' do nothing Else Dict1.Add splitarr(0), splitarr(1) End IfLoopDo Until objTextFile2.AtEndOfStream strNextLine = objTextFile2.Readline splitarr = split(strNextLine," ") if Dict2.exists(splitarr(0)) Then ' do nothing Else Dict2.Add splitarr(0), splitarr(1) End IfLoop' compare the keys and values of the two dictionariesarrDict1 = Dict1.keysarrDict2 = Dict2.keysstrDict1Keys = Join(arrDict1)strDict2Keys = Join(arrDict2)copyStrDict1Keys = strDict1KeyscopystrDict2Keys = strDict2Keys'msgbox "strDict2Keys = " & strDict2keyss = "accounts that exist in both files"sDifferentValues = "account is the same but values are different"for i = 0 to uBound(arrDict1) if InStr(strDict2Keys, Trim(arrDict1(i))) Then copyStrDict1Keys = replace(copyStrDict1Keys, Trim(arrDict1(i)), "") copystrDict2Keys = replace(copyStrDict2Keys, Trim(arrDict1(i)), "") s = s & vbcrlf & arrDict1(i) if Trim(Dict1.item(arrDict1(i))) <> Trim(Dict2.item(arrDict1(i))) Then sDifferentValues = sDifferentValues &vbcrlf & arrDict1(i) & ": " & Trim(Dict1.item(arrDict1(i))) & ": " & Trim(Dict2.item(arrDict1(i))) End If End IfNextmsgbox smsgbox sDifferentValuesmsgbox "accounts after removing matching: OracleFile " & copyStrDict1Keysmsgbox "accounts after removing matching: HyperionFile " & copyStrDict2Keys'f.closemsgbox "finished" hth Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.