Jump to content
dreamscapeuk

VBScript scripting dictionary help needed

Recommended Posts

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"

Share this post


Link to post
Share on other sites

' 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"

Share this post


Link to post
Share on other sites

' 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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...