{"id":63393,"date":"2007-12-14T01:17:00","date_gmt":"2007-12-14T01:17:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/12\/14\/hey-scripting-guy-how-can-i-remove-duplicate-values-from-a-pair-of-text-files\/"},"modified":"2007-12-14T01:17:00","modified_gmt":"2007-12-14T01:17:00","slug":"hey-scripting-guy-how-can-i-remove-duplicate-values-from-a-pair-of-text-files","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-remove-duplicate-values-from-a-pair-of-text-files\/","title":{"rendered":"Hey, Scripting Guy! How Can I Remove Duplicate Values From a Pair of Text Files?"},"content":{"rendered":"

\"Hey, <\/H2>\n

Hey, Scripting Guy! I have two text files that contain numbers. I need to write a script that can determine whether a given number exists in both of these text files. If it does, I need to delete that line from each of the two files. How do I do that?

— KT<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, KT. Before we get started today, we have a little business to take care of. First of all, we\u2019d like to thank those of you who have joined our new Scripting Guys Facebook<\/B><\/A> group. We decided to start a Facebook group largely because we wanted to experiment with different ways of interacting with, and getting information to, system administration scripters. <\/P>\n

Oh, and, while we were at it, we also wanted to get more friends (and more group members) than either TechNet or MSDN. Well, so far so good: at last report TechNet had 43 group members, MSDN had 84, and the Scripting Guys \u2013 despite getting into the game much later than the other two \u2013 had 90.<\/P>\n

Oops. Make that 94<\/I> members.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. You know, that\u2019s a good idea: all things considered, maybe TechNet should<\/I> be a subsidiary of the Script Center rather than the other way around. We\u2019ll bring that up the next time we meet with the folks from TechNet. Can\u2019t wait to see their faces when we suggest that<\/I>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Incidentally, we realize that, up to this point, we haven\u2019t done much with our Facebook group. That will change come January. In January, we hope to start posting a few informative videos; we also plan to start using the group as a way to keep people informed about important developments in the scripting world. You know, things like the 2008 Winter Scripting Games<\/B><\/A>. (February 15-March 3, 2008. Thanks for asking.)<\/P>\n

Speaking of the Scripting Games, if you received a perfect score in any one of the four divisions in the 2007 Games (VBScript Beginner; VBScript Advanced; PowerShell Beginner; PowerShell Advanced) you still have plenty of time to submit your biography (and picture) for our Profiles in Perfection<\/B><\/A> series. We\u2019ll start featuring people on January 2nd<\/SUP>, so the sooner you can get your information to us the better.<\/P>\n

Oh, right: just email a little bit about yourself (and any pictures you don\u2019t mind being posted on the Internet) to scripter@microsoft.com (in English, if possible)<\/B><\/A>. We\u2019ll take it from there.<\/P>\n

One last note: the I<\/B>T Forum Challenge<\/B><\/A> is now officially over, and the final scores are being tabulated. And yes, we know, we had hoped to post a running tally of scores along the way. Let\u2019s just say that the past couple weeks have been \u2026 interesting \u2026 ones for the Scripting Guys and leave it at that. We can tell you this, however: if you got a score of 701 then you tied for first. And, as near as we can tell, quite a few of you tied for first.<\/P>\n

So what are we going to do about that? Well, according to the rules, ties are broken by a random drawing: we\u2019ll toss the names of all the high scorers in a hat and draw out 10 names. If your name is drawn you\u2019re a winner; if you\u2019re name is not drawn \u2013 well, then you\u2019re not<\/I> a winner. <\/P>\n

You know what? That doesn\u2019t seem very fair to us, either. So here\u2019s what we\u2019re going to do. We\u2019re going to draw 10 names and those 10 people will receive a Dr. Scripto bobblehead; that\u2019s pretty much all the bobbleheads we have left. But don\u2019t despair; the other top scorers will receive a comparable prize, but they\u2019ll have to wait until after the Scripting Games to receive that prize. But trust us, it will be worth the wait.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. So what is<\/I> the prize? Sorry, but we can\u2019t tell you that. All we can tell you, for now, is that it will be the same prize as the big giveaway in the 2008 Scripting Games. And you know that\u2019s<\/I> going to be good.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Whew; that\u2019s more work than the Scripting Guy who writes this column usually does in a month. And that\u2019s not even the half of it; after all, we haven\u2019t begun to address today\u2019s question. Which means that maybe we ought to get started on that.<\/P>\n

For the sake of illustration, we\u2019re assuming that KT has two text files that look something like these:<\/P>\n\n<\/THEAD>\n\n\n
\n

1
2
3
9
15
19
20<\/P><\/TD>\n

\n

1
4
9
15
19
20
21
29<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

What KT needs to do is delete all the numbers that appear in both files (1, 9, 15, 19, and 20). In other words, when all is said and done, he\u2019d like the two text files to look like this:<\/P>\n\n<\/THEAD>\n\n\n
\n

2
3<\/P><\/TD>\n

\n

4
21
29<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

How can he do that? Well, here\u2019s one way:<\/P>

Const ForReading = 1\nConst ForWriting = 2<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)<\/p>\n
Set objFile1 = objFSO.OpenTextFile(“C:\\Scripts\\File1.txt”, ForReading)\nstrText1 = objFile1.ReadAll\nobjFile1.Close<\/p>\n
Set objFile2 = objFSO.OpenTextFile(“C:\\Scripts\\File2.txt”, ForReading)\nstrText2 = objFile2.ReadAll\nobjFile2.Close<\/p>\n
arrItems1 = Split(strText1, vbCrLf)\narrItems2 = Split(strText2, vbCrLf)<\/p>\n
Set objDictionary = CreateObject(“Scripting.Dictionary”)<\/p>\n
For Each strItem2 in arrItems2\n    objDictionary.Add strItem2, strItem2  \nNext<\/p>\n
For i = 0 to Ubound(arrItems1)\n    If objDictionary.Exists(arrItems1(i)) Then\n         objDictionary.Remove(arrItems1(i))\n         arrItems1(i) = “Void”\n    End If\nNext<\/p>\n
For Each strItem in arrItems1\n    If strItem <> “Void” Then\n        strNewText1 = strNewText1 & strItem & vbCrLf\n    End If\nNext<\/p>\n
Set objFile1 = objFSO.OpenTextFile(“C:\\Scripts\\File1.txt”, ForWriting)\nobjFile1.Write strNewText1\nobjFile1.Close<\/p>\n
For Each strKey in objDictionary.Keys\n    strNewText2 = strNewText2 & strKey & vbCrLf\nNext<\/p>\n
Set objFile2 = objFSO.OpenTextFile(“C:\\Scripts\\File2.txt”, ForWriting)\nobjFile2.Write strNewText2\nobjFile2.Close\n<\/PRE>\n
So how does this script work? Let\u2019s see if we can figure that out. We start off by defining a pair of constants, ForReading (which we\u2019ll need in order to open and read from each of our text files) and ForWriting (which we\u2019ll need in order to open and write to each of our text files). After we define the two constants, we use the following line of code to create an instance of the Scripting.FileSystemObject <\/B>object:<\/P>
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\n<\/PRE>\n
That brings us to these three lines of code:<\/P>
Set objFile1 = objFSO.OpenTextFile(“C:\\Scripts\\File1.txt”, ForReading)\nstrText1 = objFile1.ReadAll\nobjFile1.Close\n<\/PRE>\n
All we\u2019re doing here is using the FileSystemObject to open the file C:\\Scripts\\File1.txt for reading. As soon as the file is open, we use the ReadAll<\/B> method to read the entire contents of the file into memory, storing that information in a variable named strText1. Once we have a virtual copy of the file tucked away into memory, we then call the Close<\/B> method to close File1.txt. <\/P>\n
And then we repeat this process with our second file (C:\\Scripts\\File2.txt), storing that<\/I> information in a variable named strText2. The net result? We now have two variables \u2013 strText1 and strText2 \u2013 that contain the contents of our two text files.<\/P>\n
That\u2019s great, but what exactly are we going to do<\/I> with those two variables? Well, the first thing we\u2019re going to do is execute this line of code:<\/P>
arrItems1 = Split(strText1, vbCrLf)\n<\/PRE>\n
What we\u2019re doing here is using the VBScript Split<\/B> function to convert the values in strText1 into an array, creating a new array item each time we encounter a carriage return-linefeed (vbCrLf). Why would we do that? Well, when we\u2019re all done arrItems1 will be equal to this:<\/P>
1\n2\n3\n9\n15\n19\n20\n<\/PRE>\n
In other words, we now have an array that just happens to be a perfect replica of the file File1.txt.<\/P>\n
Yes, we know: that still doesn\u2019t tell you what we\u2019re going to do<\/I> with this array, does it? We\u2019ll get to that in just a second. Before we do, however, we need to mention that we next do the exact same thing with the variable strText2, this time creating an array named arrItems2. That gives us an array that perfectly replicates the text file File2.txt.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Couldn\u2019t we have created these arrays by reading in the files line-by-line<\/B><\/A>, adding each line to the appropriate array as it was read from the text file? Sure. We just thought this approach was a tiny bit easier, especially for people with limited experience working with arrays.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
As you may or may not remember, our goal today is to delete any values that appear in both File1.txt and File2.txt. That\u2019s a little trickier than it might first sound. Why? Well, the first value in File1.txt is the number 1. That might lead you to believe that we could just search for the number 1 in both files and, if found, delete any instances of that number. But that\u2019s not going to work. After all, if we simply delete all instances of the number 1 we\u2019re going to turn the value 212 into this:<\/P>
22\n<\/PRE>\n
Not good. Of course, the number 1 does<\/I> appear on a line by itself; can\u2019t we just search for a 1 followed by a carriage return-linefeed? Unfortunately, the answer here is the same: no. Why not? Because a number like 651 includes a 1 followed by a carriage return-linefeed. If we searched for a 1 followed by a carriage return-linefeed our script would end up turning 651 into this:<\/P>
65\n<\/PRE>\n
Eep.<\/P>\n
So what are we going to do about that?<\/P>\n
Oh, you mean other than give up and tackle an easier problem, don\u2019t you?<\/P>\n
Well, the solution we came up with (and no doubt there are many other ways to do this) involves creating an instance of the Scripting.Dictionary<\/B> object. Once we have that object in hand, we then use this block of code to loop through all the values in the array arrItems2 and add each of those values to the Dictionary:<\/P>
For Each strItem2 in arrItems2\n    objDictionary.Add strItem2, strItem2  \nNext\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. We\u2019re assuming that values are unique in each file; that is, File1.txt will contain only one instance of the number 322. If values are not unique, well, that definitely complicates the script. If anyone needs to tackle that<\/I> problem, let us know and we\u2019ll see what we can do about it.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
That brings us to this block of code, a block of code that makes up the heart and soul of the script:<\/P>
For i = 0 to Ubound(arrItems1)\n    If objDictionary.Exists(arrItems1(i)) Then\n         objDictionary.Remove(arrItems1(i))\n         arrItems1(i) = “Void”\n    End If\nNext\n<\/PRE>\n
Admittedly, there are just six lines of code here; however, all sorts of action and excitement are packed into these six lines. As you can see, we kick things off by setting up a For Next loop that loops through all the items in the array arrItems1. (The loop starts at 0, the index number of the first item in the array, and continues on through the Ubound<\/B> item, the last item in the array.) Inside this loop, we use the Exists<\/B> method to determine whether or not the array item in question (e.g., the number 1) can be found in the Dictionary:<\/P>
If objDictionary.Exists(arrItems1(i)) Then\n<\/PRE>\n
Suppose the Exists method returns False; that means that the value in question appears only in File1.txt; it doesn\u2019t appear in both File1.txt and File2.txt. Because we\u2019re only interested in values that appear in both files we simply go back to the top of the loop and repeat the process with the next item in the array.<\/P>\n
However, suppose that the Exists method returns True. That means that the value in question does<\/I> appear in both files. That also means that we need to execute these two lines of code:<\/P>
objDictionary.Remove(arrItems1(i))\narrItems1(i) = “Void”\n<\/PRE>\n
In line 1 we\u2019re using the Remove<\/B> method to remove the item in question from the Dictionary. If the item in question happens to be the number 1 that means that the number 1 will no longer be included in the Dictionary. In addition, the number 1 will no longer be included in the array arrItems1. Why not? Because in line 2 we set the value of that array item to the string Void<\/I>.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Why don\u2019t we just remove the item from the array? We\u2019d like to. But removing items from an array isn\u2019t particularly fun. And we Scripting Guys don\u2019t like to do things that aren\u2019t particularly fun. Setting the value to Void<\/I> is much easier, and ensures that we\u2019ll still be able to quickly pick out the values that are unique to File1.txt.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
The point of all this? Well, after we finish looping through all the items in the array arrItems1 our Dictionary will contain the following items:<\/P>
4\n21\n29\n<\/PRE>\n
Those happen to be the numbers that appear only<\/I> in File2.txt. Any numbers that appear in both File1.txt and File2.txt have been deleted from the Dictionary.<\/P>\n
Meanwhile, arrItems1 will look like this:<\/P>
Void\n2\n3\nVoid\nVoid\nVoid\nVoid\n<\/PRE>\n
As you can see, the only numbers<\/I> that are left are those values that appear only in File1.txt. Numbers that appeared in both our text files have been replaced with the word Void<\/I>.<\/P>\n
All we have to do now is write these unique values back to our text files. To construct our new text for File1.txt we use this block of code:<\/P>
For Each strItem in arrItems1\n    If strItem <> “Void” Then\n        strNewText1 = strNewText1 & strItem & vbCrLf\n    End If\nNext\n<\/PRE>\n
Here we\u2019re grabbing all the values in arrItems1 that are not<\/I> equal to Void<\/I> (in this case, the numbers 2 and 3). To create the new text we simply add each of these values, plus a carriage return-linefeed to a variable named strNewText1:<\/P>
strNewText1 = strNewText1 & strItem & vbCrLf\n<\/PRE>\n
After that we reopen the file C:\\Scripts\\File1.txt (this time for writing), then use the Write<\/B> method to write the value of strNewText1 to the file:<\/P>
objFile1.Write strNewText1\n<\/PRE>\n
Once the old contents of File1.txt have been replaced with the value of strNewText1 we then close File1.txt.<\/P>\n
We then use this block of code to construct a similar string for File2.txt:<\/P>
For Each strKey in objDictionary.Keys\n    strNewText2 = strNewText2 & strKey & vbCrLf\nNext\n<\/PRE>\n
When that\u2019s done, we write the value of strNewText2 to File2.txt. At that point, and at long last, we\u2019re done.<\/P>\n
One last bit of business before we go. We should note that tomorrow\u2019s column (December 14, 2007) will be the last column of the year; we\u2019ll resume publication on January 2, 2008. Having filed columns from such diverse locales as Walla Walla, WA; Orlando, FL; Venice, Italy; and Barcelona, Spain the Scripting Guy who writes this column is taking a well-deserved break. (Well, OK. But he\u2019s taking a break anyway, whether he deserves it or not.) But don\u2019t worry; after a brief hiatus he\u2019ll definitely be back on the job in January. After all, 2008 is<\/I> the year the Scripting Son starts college.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have two text files that contain numbers. I need to write a script that can determine whether a given number exists in both of these text files. If it does, I need to delete that line from each of the two files. How do I do that?— KT Hey, KT. Before […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[18,40,3,4,14,5],"class_list":["post-63393","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-arrays-hash-tables-and-dictionary-objects","tag-filesystemobject","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have two text files that contain numbers. I need to write a script that can determine whether a given number exists in both of these text files. If it does, I need to delete that line from each of the two files. How do I do that?— KT Hey, KT. Before […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63393"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63393\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}