{"id":55803,"date":"2008-04-11T01:02:00","date_gmt":"2008-04-11T01:02:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/04\/11\/hey-scripting-guy-how-can-i-delete-duplicate-entries-in-a-tab-delimited-file\/"},"modified":"2008-04-11T01:02:00","modified_gmt":"2008-04-11T01:02:00","slug":"hey-scripting-guy-how-can-i-delete-duplicate-entries-in-a-tab-delimited-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-delete-duplicate-entries-in-a-tab-delimited-file\/","title":{"rendered":"Hey, Scripting Guy! How Can I Delete Duplicate Entries in a Tab-Delimited File?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! How can I delete the duplicate entries in a tab-delimited file?
— ST<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, ST. Before we start, is anyone out there familiar with the book Not Quite What I Was Planning: Six-Word Memoirs by Writers Famous and Obscure<\/b><\/a>? Not Quite What I Was Planning is an interesting little book in which the editors of SMITH magazine<\/b><\/a> asked people to summarize their lives in six words. Examples from both the book and the magazine include the following:<\/p>\n\n\n\n\n\n\n
\u2022<\/td>\n\n

Found true love, married someone else.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

I still make coffee for two.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Born good. Went bad. Good again.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Road less traveled, now know why<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Oh, and this one from Amy Sedaris:<\/p>\n\n\n\n
\u2022<\/td>\n\n

Mushrooms. Clowns. Wands. Five. Wig. Thatched.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Well, OK; never mind about that last one. <\/p>\n

At any rate, the Scripting Guy who writes this column thought it might be cool to apply this idea to Hey, Scripting Guy!<\/i> After all, if he could answer the day\u2019s question in just six words, well, that would save him a heck<\/i> of a lot of time and effort. Unfortunately, though, he had to quickly give up on that idea; as it turns out, the Scripting Guy who writes this column can\u2019t even refer to himself<\/i> using just six words:<\/p>\n\n\n\n\n\n\n\n\n\n
\n

1.<\/p>\n<\/td>\n

\n

The<\/p>\n<\/td>\n<\/tr>\n

\n

2.<\/p>\n<\/td>\n

\n

Scripting<\/p>\n<\/td>\n<\/tr>\n

\n

3.<\/p>\n<\/td>\n

\n

Guy<\/p>\n<\/td>\n<\/tr>\n

\n

4.<\/p>\n<\/td>\n

\n

who<\/p>\n<\/td>\n<\/tr>\n

\n

5.<\/p>\n<\/td>\n

\n

writes<\/p>\n<\/td>\n<\/tr>\n

\n

6.<\/p>\n<\/td>\n

\n

this <\/p>\n<\/td>\n<\/tr>\n

\n

7.<\/p>\n<\/td>\n

\n

column<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Needless to say, he tends to be a little long-winded from time-to-time. <\/p>\n\n<\/thead>\n\n\n
\n

Actually he did come up with one<\/i> possibility: \u201cHere is the code. Good luck.\u201d However, he wasn\u2019t sure if anyone would count that as having sufficiently explained the script and how it works.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Therefore, he decided to compromise a little: we\u2019ll do part<\/i> of today\u2019s column in six-word blocks, enclosing those six-word explanations in square brackets. The rest of the column will be done in typical Hey, Scripting Guy!<\/i> fashion. [Typical fashion. Why are you booing?]<\/p>\n

So let\u2019s get started. How can you delete duplicate entries from a tab-delimited file? [Beats us. Try reading the article.]<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. Say what you want, but it\u2019s still better than the one Amy Sedaris came up with. And she\u2019s a real<\/i> writer and comedian.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Here\u2019s a script that can delete duplicate entries from a tab-delimited file (although we\u2019re ashamed to admit that the script requires more than six words):<\/p>\n

Const ForReading = 1\nConst ForWriting = 2\n\nSet objDictionary = CreateObject(\"Scripting.Dictionary\")\n\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n\nstrContents = objFile.ReadAll\nobjFile.Close\n\nstrContents = Replace(strContents, vbCrLf, vbTab)\narrContents = Split(strContents, vbTab)\n\nFor Each strItem in arrContents\n    If Not objDictionary.Exists(strItem) Then\n        objDictionary.Add strItem, strItem   \n    End If\nNext\n\ni = 1\n\nFor Each strKey in objDictionary.Keys\n    If i < 3 Then\n        strNewContents = strNewContents & strKey & vbTab\n        i = i + 1\n    Else\n        strNewContents = strNewContents & strKey & vbCrLf\n        i = 1\n    End If\nNext\n\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\nobjFile.Write strNewContents\nobjFile.Close\n<\/pre>\n
As you can see, our script starts out by creating a pair of constants, ForReading and ForWriting, a pair of constants we\u2019ll need when we open our text file. [Two constants. Must open file twice.] After we define the constants we create instances of the objects Scripting.Dictionary<\/b> and Scripting.FileSystemObject<\/b>, then use the following line of code to open the file C:\\Scripts\\Test.txt for reading:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n<\/pre>\n
As soon as the file is open we use the ReadAll<\/b> method to read the entire contents of the file into a variable named strContents, then call the Close<\/b> method to (temporarily) close Test.txt. [We\u2019ve got the file. Now what?]<\/p>\n
Before we do anything else let\u2019s take a look at the file we just opened (and, not coincidentally, the value of the variable strContents):<\/p>\n
Apple    Apple    Apple\nBanana   Cherry   Cherry\nCherry   Date     Fig\nFig      Lemon    Orange\nOrange   Peach    Peach\nPear     Pear     Pear\n<\/pre>\n
As you can see, this is a simple three-column list of various fruits. As you can also see, many of these fruits appear more than once; for example, we have three listings for Apple and two for Peach. [One date. Story of our lives.] What we want to do now is eliminate all the duplicate listings. [Originals only. Duplicates need not apply.] In other words, we want a three-column list that looks like this:<\/p>\n
Apple    Banana    Cherry\nDate     Fig       Lemon\nOrange   Peach     Pear\n<\/pre>\n
Good question: how are<\/i> we going to do that? Well, for starters, we need a list of words that\u2019s a bit easier to work with. In fact, what we really need is a one<\/i>-column list of words rather than this fancy three-column format. [One column\u2019s great; three\u2019s a crowd.] In order to combine our collection of words into a single list the first thing we do is replace any instances of the carriage return-linefeed character (vbCrLf<\/b>) with a tab character (vbTab<\/b>):<\/p>\n
strContents = Replace(strContents, vbCrLf, vbTab)\n<\/pre>\n
What this does is give us a single line of words, with each word in that line separated by a tab. In other words, something similar to this (except that this single line would continue to scroll on and on and on):<\/p>\n
Apple    Apple    Apple    Banana  Cherry   Cherry\n<\/pre>\n
[One lone banana. Life isn\u2019t fair.]<\/p>\n
Granted, that might not seem like we\u2019ve made much progress. Believe it or not, however, we have. [Holy smokes: Scripting Guys make progress!] Now that all<\/i> our words are separated by tabs we can use the Split<\/b> function to split strContents on the tab character:<\/p>\n
arrContents = Split(strContents, vbTab)\n<\/pre>\n
Why would we want to do that? Because that gives us an array named arrContents, an array consisting of the following elements:<\/p>\n
Apple \nApple \nApple\nBanana\nCherry\nCherry\nCherry\nDate  \nFig\nFig   \nLemon \nOrange\nOrange\nPeach \nPeach\nPear  \nPear  \nPear\n<\/pre>\n
Now that we have a nice little array like this one it\u2019s actually quite easy to eliminate the duplicate items. To that end, we first set up a For Each loop designed to loop through all the items in the array arrContents. [For Each: everyone gets a turn!]:<\/p>\n
For Each strItem in arrContents\n<\/pre>\n
Inside that loop we take the first item in the array, and use the Exists<\/b> method to see if that item exists in the Dictionary [See? We never<\/i> forget the Dictionary.]:<\/p>\n
If Not objDictionary.Exists(strItem) Then\n<\/pre>\n
If the item does<\/i> exist in the Dictionary that means this is a duplicate item; in that case, we simply go back to the top of the loop and repeat the process with the next item in the array. If the item doesn\u2019t<\/i> exist in the Dictionary then we use the following line of code to add item to the Dictionary, using the word itself as both the Dictionary key and item:<\/p>\n
objDictionary.Add strItem, strItem\n<\/pre>\n
By the time we\u2019re done our Dictionary should contain the following keys (and items):<\/p>\n
Apple \nBanana\nCherry\nDate  \nFig\nLemon \nOrange\nPeach \nPear\n<\/pre>\n
That\u2019s pretty good, except now we need to turn this one-column list back into a three-column list. [Sometimes three is<\/i> better than one.] To do that we first assign the value 1 to a counter variable named i:<\/p>\n
i = 1\n<\/pre>\n
We then set up another For Each loop, this one designed to walk through all the keys in the Dictionary object [Can never have too many loops.]:<\/p>\n
For Each strKey in objDictionary.Keys\n<\/pre>\n
Because we want a three-column list, the first thing we do inside the loop is check to see if the value of our counter variable i is less than 3. If it is, we execute this block of code:<\/p>\n
strNewContents = strNewContents & strKey & vbTab\ni = i + 1\n<\/pre>\n
As you can see, there\u2019s nothing too complicated going on here. [Scripting Guys: Simple minds, simple scripts.] In line 1 we\u2019re assigning a value to a variable named strNewContents; to be a little more specific, we\u2019re assigning this variable the existing value of strContents plus<\/i> the value of the Dictionary key plus<\/i> a tab character. In line 2, we then increment the value of our counter variable by 1. What does all that mean? That means that, after three trips through the loop, strContents will be equal to this:<\/p>\n
Apple    Banana  Cherry\n<\/pre>\n
Now, what\u2019s going to happen the next<\/i> time through the loop? Well, the next time through the loop the counter variable i will be equal to 3. That means we\u2019re going to execute these two lines of code instead:<\/p>\n
strNewContents = strNewContents & strKey & vbCrLf\ni = 1\n<\/pre>\n
Notice the differences? In this block of code, we\u2019re tacking a carriage return-linefeed character onto the end of the string instead of adding a tab character. On top of that, we also reset the value of i to 1. Those two things ensure that the next item in our list (the fourth item) will show up in the first column of row 2 rather than the fourth column of row 1.<\/p>\n
As soon as we\u2019ve reformatted strNewContents we reopen the file Test.txt, this time for writing:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\n<\/pre>\n
We use the Write<\/b> method to replace the existing contents of Test.txt with the value of strNewContents, then use the Close<\/b> method to close the file one more time, this time for good. [File closed? Column must be over.]<\/p>\n
That should do it, SS. (Shoot, that\u2019s only five words. Double shoot: that\u2019s<\/i> only five words, too. Dang; and that last sentence was seven<\/i> words!) Let us know if you have any questions. Also, let us know if you have any six-word descriptions for the Script Center and the Scripting Guys; we\u2019ll publish those in a future column. For example, here\u2019s one we came up with to describe this column:<\/p>\n\n\n\n
\u2022<\/td>\n\n
OK \u2026. But why no scripting information?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
And here\u2019s one that summarizes our beloved Scripting Editor:<\/p>\n\n\n\n
\u2022<\/td>\n\n
Loves people. Had one for breakfast.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
See you all tomorrow.<\/p>\n
Wait; check that. How about this: <\/p>\n\n\n\n
\u2022<\/td>\n\n
New column tomorrow; see you then.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That\u2019s better.<\/p>\n
Wait; check that. How about this: <\/p>\n\n\n\n
\u2022<\/td>\n\n
That\u2019s better; must use six words.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I delete the duplicate entries in a tab-delimited file?— ST Hey, ST. Before we start, is anyone out there familiar with the book Not Quite What I Was Planning: Six-Word Memoirs by Writers Famous and Obscure? Not Quite What I Was Planning is an interesting little book in which the […]<\/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":[169,3,4,5],"class_list":["post-55803","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I delete the duplicate entries in a tab-delimited file?— ST Hey, ST. Before we start, is anyone out there familiar with the book Not Quite What I Was Planning: Six-Word Memoirs by Writers Famous and Obscure? Not Quite What I Was Planning is an interesting little book in which the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55803","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=55803"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55803\/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=55803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55803"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}