{"id":56233,"date":"2008-02-11T22:46:00","date_gmt":"2008-02-11T22:46:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/02\/11\/hey-scripting-guy-how-can-i-remove-extraneous-spaces-from-fields-in-a-text-file\/"},"modified":"2008-02-11T22:46:00","modified_gmt":"2008-02-11T22:46:00","slug":"hey-scripting-guy-how-can-i-remove-extraneous-spaces-from-fields-in-a-text-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-remove-extraneous-spaces-from-fields-in-a-text-file\/","title":{"rendered":"Hey, Scripting Guy! How Can I Remove Extraneous Spaces From Fields in a Text File?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! I have a text file in which each field has a bunch of extra spaces tacked on to the end. How can I remove all those extra spaces?<\/p>\n

— GC<\/p>\n

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

Hey, GC. Well, today is Monday, February 11th<\/sup>, which can mean only one thing: the 2008 Winter Scripting Games<\/b><\/a> are just a few days away. But we didn\u2019t need to tell you that, did we? After all, if you\u2019re anything like the Scripting Guys, you can feel the excitement in the air, can\u2019t you?<\/p>\n

Oh, wait, never mind: that\u2019s just more Seattle rain. We\u2019ve been getting a lot of that lately.<\/p>\n

At any rate, if you\u2019re still waffling about whether or not you want to enter the Scripting Games you might want to check out the Scripting Games home page<\/b><\/a>, where we\u2019ve added information about our User Group, International, Windows PowerShell 2.0, and Sudden Death challenges. What do all those things mean to you? Well, in a nutshell, that means that not only will the 2008 Games be even more<\/i> fun than last year\u2019s Games, but, on top of that, you\u2019ll also have all sorts of ways to win a prize:<\/p>\n\n\n\n\n\n\n\n\n
\u2022<\/td>\n\n

Score at least 60 points in any one division (e.g., Perl Beginners) and you\u2019ll earn a Scripting Games Certificate of Excellence.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Enter at least one event and you\u2019ll be eligible to win \u2013 via random drawing \u2013 one of the many outstanding prizes shown on our Prizes<\/b><\/a> page. Best of all, you\u2019re eligible for the drawing even if your script fails. How many other times in your life have you scored a 0 on a test and been given a prize for that? Well, except for those of you who went to Washington State University. <\/p>\n

Just kidding, Cougar fans.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Be a member of the user group that (on a percentage basis) has the most members participate in the Games. Do that and you\u2019ll win \u2026 well, something. (We\u2019re still working on that.)<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Be a representative of the country that (on a proportional basis) has the most people participate in the Games. Do that and you\u2019ll win \u2026 well, nothing. But your country will be featured throughout the month of April in the Script Center. And that is definitely the highest honor any nation could ever hope to receive. <\/p>\n

At least from the Scripting Guys.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Use a Windows PowerShell 2.0 feature in one of your solutions and get yourself a limited-edition PowerShell T-shirt.<\/p>\n<\/td>\n<\/tr>\n

\u2022<\/td>\n\n

Enter the Sudden Death Challenge and win \u2026 well, we haven\u2019t quite nailed that down, either. But we will have prizes for the Sudden Death Challenge; you can count on that.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

At any rate, we could go on and on except for one thing: that\u2019s about it, at least for the moment. But who knows what will happen between now and Friday? For some reason, this year\u2019s edition of the Scripting Games seems to have a life of its own.<\/p>\n

Of course, that\u2019s all well and good; however, it doesn\u2019t help GC much with his problem, does it? Fortunately, though the following bit of code should<\/i>:<\/p>\n

Const ForReading = 1Const ForWriting = 2Set objFSO = CreateObject(\"Scripting.FileSystemObject\")Set objRegEx = CreateObject(\"VBScript.RegExp\")objRegEx.Global = True   objRegEx.Pattern = \" {2,}\"Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)Do Until objFile.AtEndOfStream    strSearchString = objFile.ReadLine    strNewString = objRegEx.Replace(strSearchString,\"\")    strNewContents = strNewContents & strNewString & vbCrLfLoopobjFile.CloseSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)objFile.Write strNewContentsobjFile.Close<\/pre>\n
Before we go any further we should take a moment and explain the scenario. GC has a bunch of text files containing data similar to this:<\/p>\n
Emailaddr                            |Firstname                        |Lastname\"kmyer@fabrikam.com          \"|\"Ken                     \"|\"Myer            \"|\"aabudayah@fabrikam.com          \"|\"Ahmad          \"|\"Abu Dayah                \"|\"htandersen@fabrikam.com          \"|\"Henriette Thaulow              \"|\"Andersen\"|<\/pre>\n
What he would like to do is eliminate all the blank spaces that come at the end of each field. In other words, he\u2019d like his text files to look like this<\/i>:<\/p>\n
Emailaddr|Firstname|Lastname\"kmyer@fabrikam.com\"|\"Ken\"|\"Myer\"|\"aabudayah@fabrikam.com\"|\"Ahmad\"|\"Abu Dayah\"|\"htandersen@fabrikam.com\"|\"Henriette Thaulow\"|\"Andersen\"|<\/pre>\n
Interestingly enough, that was our first thought, too: why don\u2019t we just use the Replace function to remove all the blank spaces? (That is, replace each blank space with nothing?) That almost<\/i> works, except for one thing: if we remove all the blank spaces then a name like Henriette Thaulow<\/i> would end up looking like this:<\/p>\n
HenrietteThaulow<\/pre>\n
Not exactly what we wanted. <\/p>\n
If you take a careful look at the text file, however, you\u2019ll see that what we really<\/i> want to do is remove any instances of two or more blank spaces in succession. We can\u2019t remove single spaces; that messes up poor old Henriette Thaulow. If we stipulate that there must be at least two<\/i> blank spaces in a row (and any number of blanks spaces after that), well, then we can delete all those blank spaces while making allowances for Henriette Thaulow and Ahmad Abu Dayah. Did we just hear someone say, \u201cThat sounds like a job for regular expressions\u201d? Good call; this is<\/i> a job for regular expressions. As it turns out, we\u2019re going to use a regular expression to search for \u2013 and replace \u2013 any instances of two or more blank spaces in succession. That\u2019s going to let us eliminate all the extraneous blank spaces tacked on the end of each field, all the while preserving the blank spaces between two names like Henriette and Thaulow.<\/p>\n
To that end, we start our script off by defining a pair of constants, ForReading and ForWriting; we\u2019ll use these constants to open our text file. After creating an instance of the Scripting.FileSystemObject<\/b> (also needed for working with the text file), we then create an instance of VBScript\u2019s RegExp<\/b> object, the COM object that enables VBScript to use regular expressions:<\/p>\n
Set objRegEx = CreateObject(\"VBScript.RegExp\")<\/pre>\n
Once we have an instance of the RegExp object we then assign values to two of the object\u2019s properties. To begin with, we set the Global<\/b> property to True; this tells the regular expressions object that we want to search for all<\/i> instances of our target text, not just the first such instance. We then define that target text by assigning the following value to the Pattern<\/b> property:<\/p>\n
\" {2,}\"<\/pre>\n
As you can see, we have a blank space here followed by this construction: {2,}<\/b>. That simply means that we\u2019re looking for at least two consecutive instances of the preceding character (the blank space). The comma followed by nothing means that the target text needs to have at least 2 consecutive blank spaces, but can have as many as an infinite number of consecutive blank spaces. (Although in that<\/i> case the script would probably take a while to finish running.) In other words, this pattern will find 2 consecutive blank spaces. It will also find 3 consecutive blanks spaces, 11 consecutive blank spaces, 147 consecutive blank spaces, etc., etc.<\/p>\n
After configuring the regular expressions object we use this line of code to open the file C:\\Scripts\\Test.txt for reading:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)<\/pre>\n
Our next task is to set up a Do Until loop that runs until that file\u2019s AtEndOfStream<\/b> property is True; that simply means we\u2019re going to keep reading the file until there\u2019s nothing left to read. Inside that loop the first thing we do is use the ReadLine<\/b> method to read the initial line in the file and store it in a variable named strSearchString:<\/p>\n
strSearchString = objFile.ReadLine<\/pre>\n
That brings us to this line of code:<\/p>\n
strNewString = objRegEx.Replace(strSearchString,\"\")<\/pre>\n
What we\u2019re doing here is using the regular expression object\u2019s Replace<\/b> method to search for the target text and replace it with, well, nothing (\u201c\u201d). What\u2019s that going to do? That\u2019s going to take a line of text like this:<\/p>\n
Emailaddr                            |Firstname                        |Lastname<\/pre>\n
And turn it into a line of text that looks like this:<\/p>\n
Emailaddr|Firstname|Lastname<\/pre>\n
In the next line, we add this new value to a variable named strNewContents. Each time we remove the extraneous spaces from a line in the text file we\u2019ll append that modified line to strNewContents. When we\u2019ve finished modifying all the lines in the file we\u2019ll then replace the existing contents of Test.txt with the value of strNewContents.<\/p>\n
We should note that there\u2019s one potential flaw in this approach. Suppose a field looked like this:<\/p>\n
\"kmyer@fabrikam.com \"|\"Ken       \"|\"Myer     \"<\/pre>\n
As you can see, there\u2019s a single space between fabrikam.com and the closing double quote mark. That space probably should<\/i> be removed; however, because it\u2019s just one space our regular expression won\u2019t find it. We could try to write a more complicated regular expression, but it might be easier just to run our simple regular expression and then execute this line of code:<\/p>\n
strNewString = Replace(strNewString, \" \" & Chr(34), Chr(34))<\/pre>\n
All this line does is search for any instance of a blank space followed by a double quote mark (Chr(34)<\/b>) and then replaces that with just a double quote mark. That should do the trick. <\/p>\n
At any rate, after we\u2019ve read (and modified) all the lines in the text file we use the Close<\/b> method to close Test.txt. We then use this line of code to immediately reopen the file, this time for writing:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)<\/pre>\n
All we have to do now is use the Write<\/b> method (and the variable strNewContents) to overwrite the existing contents of Test.txt. After that we call the Close<\/b> method to close the file one final time, and then are free to concentrate on more important things.<\/p>\n
Like, say, the 2008 Winter Scripting Games<\/b><\/a>. Remember, the Games start this Friday, February 15th<\/sup>. And while you don\u2019t have<\/i> to show up on the first day, keep in mind that the deadline for submitting events 1 and 2 is 8:00 AM (Redmond time) on Wednesday, February 20th<\/sup>. If you\u2019re aiming for a perfect score in this year\u2019s Games make sure you leave yourself plenty of time to get events 1 and 2 submitted before the deadline.<\/p>\n
And what happens if you don\u2019t<\/i> get a perfect score? Will the Scripting Guys make fun of you? You bet we will. (But don\u2019t worry; the Scripting Editor will probably edit all that stuff out anyway.)<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. OK, if you want to know the truth, we have no desire or intention to make fun of anyone just because they don\u2019t get a perfect score in the Games. (And we can assure you that most people don\u2019t<\/i> get a perfect score in the Games; it actually means something to get a perfect score in the Scripting Games.) The truth is, the Games are all about having some fun and maybe learning a little something about scripting along the way. And the Scripting Guys would never make fun of anyone who wants to have some fun and learn a little something about scripting.<\/p>\n
Now, if you\u2019re talking about people who don\u2019t<\/i> participate in the Games, well, that\u2019s a different story \u2026.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a text file in which each field has a bunch of extra spaces tacked on to the end. How can I remove all those extra spaces? — GC Hey, GC. Well, today is Monday, February 11th, which can mean only one thing: the 2008 Winter Scripting Games are just a […]<\/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":[3,4,14,5],"class_list":["post-56233","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a text file in which each field has a bunch of extra spaces tacked on to the end. How can I remove all those extra spaces? — GC Hey, GC. Well, today is Monday, February 11th, which can mean only one thing: the 2008 Winter Scripting Games are just a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56233","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=56233"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56233\/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=56233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}