{"id":65243,"date":"2007-03-24T02:48:00","date_gmt":"2007-03-24T02:48:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/03\/24\/how-can-i-parse-a-tab-delimited-file-and-then-save-that-as-a-comma-separated-values-file\/"},"modified":"2007-03-24T02:48:00","modified_gmt":"2007-03-24T02:48:00","slug":"how-can-i-parse-a-tab-delimited-file-and-then-save-that-as-a-comma-separated-values-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-parse-a-tab-delimited-file-and-then-save-that-as-a-comma-separated-values-file\/","title":{"rendered":"How Can I Parse a Tab-Delimited File and Then Save That as a Comma-Separated Values File?"},"content":{"rendered":"

\"Hey, <\/H2>\n

Hey, Scripting Guy! How can I parse a tab-delimited file, and then save the resulting information as a comma-separated values file?

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

Hey, DM. You know, yesterday morning the Scripting Guy who writes this column had to scrape the ice off his windshield; today it\u2019s much warmer \u2013 44 at last report \u2013 although it is<\/I> raining. Of course, you know what that means: the Seattle-area baseball season is in full swing!<\/P>\n

We mention that simply because it provides a sneaky way for us to also mention that the Script Son took the mound for his high school team yesterday afternoon. The results? Five shutout innings, one hit (on a checked-swing), no walks, eight strikeouts. Not bad for his first outing of the season, although it\u2019s a shame that he gave up that one hit; after all, that\u2019s one more hit than the Scripting Dad gave up during his entire high school career<\/I>.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. What\u2019s that? How many times did the Scripting Dad actually pitch<\/I> during his high school career? Well, that\u2019s hard to say; after all, that was<\/I> a long time ago, and pitching records from that era are spotty at best. Off the top of our heads, we\u2019d have to say the number of times he pitched was definitely greater than \u2013 or, at least, equal to \u2013 zero.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

But, hey, this is supposed to be about the Scripting Son. Let\u2019s let him have his moment and not concern ourselves with the Scripting Dad.<\/P>\n

At any rate, it\u2019s always a treat to watch your son mow down the opposition. He blew his fast ball right by several batters, and completely froze several more with his curve. <\/P>\n

Although, come to think of it, maybe the curveball didn\u2019t freeze the hitters as much as the weather did. The Scripting Dad, for example, was wearing long underwear, a long-sleeved shirt, a hooded sweatshirt, a coat, and a pair of gloves. If that doesn\u2019t scream \u201cbaseball season\u201d we don\u2019t know what does.<\/P>\n

Of course, in years past the start of baseball season meant that the Scripting Guy who writes this column disappears and isn\u2019t heard from again until August; that\u2019s because he finds baseball far more interesting than work. (Hard to believe, but he does.) Right now, however, he has to admit that he finds work far warmer<\/I> than he finds baseball. Which means that, rather than disappearing, he\u2019s going to sit in his nice warm office and see if he can figure out how to parse a tab-delimited file, and then save the resulting information as a comma-separated values (CSV) file.<\/P>\n

At least until it\u2019s time to leave work early and head for the next game.<\/P>\n

To begin with, DM has a text file that looks something like this, with fields separated by tabs:<\/P>

Cre Rec    Name=Jack    Address=5 XYZ Drive    Phone=555-4567\nCre Rec    Name=Jill    Address=7 XYZ Drive    Phone=555-6547\nCre Rec    Name=Jake    Address=9 XYZ Drive    Phone=555-9876\n<\/PRE>\n
And sure, that is<\/I> a very nice text file. But it\u2019s nowhere near as nice as this CSV file, which is the file DM would like<\/I> to have:<\/P>
Name,Address,Phone\n“Jack”,”5 XYZ Drive”,”555-4567″\n“Jill”,”7 XYZ Drive”,”555-6547″\n“Jake”,”9 XYZ Drive”,”555-9876″\n<\/PRE>\n
The question for today is this: how can DM get from point A (the tab-delimited file) to point B (the CSV file)? Here\u2019s how:<\/P>
Const ForReading = 1<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)<\/p>\n
Do Until objFile.AtEndOfStream\n    strLine = objFile.ReadLine\n    arrFields = Split(strLine, vbTab)\n    arrName = Split(arrFields(1), “=”)\n    strName = arrName(1)\n    arrAddress = Split(arrFields(2), “=”)\n    strAddress = arrAddress(1)\n    arrPhone = Split(arrFields(3), “=”)\n    strPhone = arrPhone(1)\n    strNewContent = strNewContent & Chr(34) & strName & Chr(34) &  “,” & Chr(34) & strAddress & Chr(34) & _\n        “,” & Chr(34) & strPhone & Chr(34) & vbCrLf\nLoop<\/p>\n
objFile.Close<\/p>\n
Set objFile = objFSO.CreateTextFile(“C:\\Scripts\\Test.csv”)<\/p>\n
objFile.WriteLine “Name,Address,Phone”\nobjFile.Write strNewContent<\/p>\n
objFile.Close\n<\/PRE>\n
As you can see, we start out by defining a constant named ForReading and setting the value to 1; we\u2019ll use this constant when we set out to open our tab-delimited file. We then use these two lines of code to create an instance of the Scripting.FileSystemObject<\/B> and to open the file C:\\Scripts\\Test.txt:<\/P>
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)\n<\/PRE>\n
So now what do we do? <\/P>\n
Oh, right: that\u2019s our<\/I> job to figure that out, isn\u2019t it? OK, well, here\u2019s one thing we can do: we can set up a Do Until loop that reads the tab-delimited file line-by-line. In other words, a Do Until loop that runs until the file\u2019s AtEndOfStream<\/B> property is True:<\/P>
Do Until objFile.AtEndOfStream\n<\/PRE>\n
Inside that loop we use this line of code to read the first line in the file and store it in a variable named strLine:<\/P>
strLine = objFile.ReadLine\n<\/PRE>\n
What does that mean? That means that strLine is equal to this:<\/P>
Cre RecName=JackAddress=5 XYZ DrivePhone=555-4567\n<\/PRE>\n
Of course, that doesn\u2019t do us much good; somehow we need to tease out the individual values for Name, Address, and Phone. Is that too much to ask for? Of course it is. But let\u2019s give it a try anyway.<\/P>\n
To begin with, we use the Split<\/B> function within the following line of code to split the value of strLine into an array:<\/P>
arrFields = Split(strLine, vbTab)\n<\/PRE>\n
By splitting the value on the tab character (using the VBScript constant vbTab<\/B>) we end up with an array named arrFields that consists of the following items:<\/P>\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
Cre Rec<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Name=Jack<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Address=5 XYZ Drive<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Phone=555-4567<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
OK, now we\u2019re getting somewhere. As you can see, the user\u2019s name (Jack) is part of the second item in the array. (And, remember, because the first item in an array has an index number 0 that means the second item in the array has an index number of 1). The only problem, of course, is that the name is prefaced with Name=<\/I>. So why don\u2019t we just get rid of that prefix:<\/P>
arrName = Split(arrFields(1), “=”)\nstrName = arrName(1)\n<\/PRE>\n
See what we\u2019re doing here? In the first line, we\u2019re again using the Split function to create an array; this time we\u2019re splitting the value of array item 1 (Name=Jack<\/I>) on the equals sign (=). What does that<\/I> give us? That gives us a little two-item array named arrName, an array that looks like this:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
Name<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Jack<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Well, what do you know: the second item in our mini-array (index number 1) just happens to be the user name. That means we can grab the name simply by assigning the value of array item 1 to the variable strName, which is what we do in our second line of code.<\/P>\n
Get the idea? We then repeat the process with index numbers 2 (address) and 3 (phone number), like so:<\/P>
arrAddress = Split(arrFields(2), “=”)\nstrAddress = arrAddress(1)\narrPhone = Split(arrFields(3), “=”)\nstrPhone = arrPhone(1)\n<\/PRE>\n
We now have three variables \u2013 strName, strAddress, and strPhone \u2013 that contain information parsed from the text file. That means that our next step is to begin constructing the CSV file. That\u2019s what this monstrosity is for:<\/P>
strNewContent = strNewContent & Chr(34) & strName & Chr(34) &  “,” & Chr(34) & strAddress & Chr(34) & _\n    “,” & Chr(34) & strPhone & Chr(34) & vbCrLf\n<\/PRE>\n
Don\u2019t let this line of code deter you: its bark is far worse than its bite. All we\u2019re doing here is assigning a value to a variable named strNewContent. What value are we assigning to strNewContent? Well, we\u2019re assigning the existing value of the variable plus the following:<\/P>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
Double quotes (that\u2019s what the Chr(34) is for).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The value of the variable strName.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
More double quotes.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
A comma (\u201c,\u201d).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Double quotes.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The value of the variable strAddress.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Double quotes.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Another comma.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Double quotes.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The value of the variable strPhone.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Double quotes.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
A carriage return-linefeed character (vbCrLf).<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Why such a complicated construction? What we\u2019re trying to do is guard against any items that might include a comma. For example, suppose a user has the name Ken Myer, Jr<\/B>. If we simply write that name as-is to a CSV file, the CSV file will assume that we have two<\/I> values: Ken Myer<\/B> and Jr<\/B>. That\u2019s because the comma is used as the delimiter. In turn, a simple thing like that can cause our CSV file (and any scripts\/applications that need to read that file) to go completely haywire. To prevent that from happening we enclose each value in double quotes, like so:<\/P>
“Ken Myer, Jr.”\n<\/PRE>\n
In a CSV file, items enclosed in double quotes are treated as a single value, even if the value includes commas.<\/P>\n
See? We\u2019re doing this for your own good!<\/P>\n
After that we then loop around and repeat the process with the next line in the tab-delimited file.<\/P>\n
Once we\u2019ve read and processed the entire file we\u2019re ready to create the CVS file. To do that we first close the file Test.txt, then use this line of code to create a new text file named C:\\Scripts\\Test.csv:<\/P>
Set objFile = objFSO.CreateTextFile(“C:\\Scripts\\Test.csv”)\n<\/PRE>\n
Because DM needed a header line the first thing we do to our new file is use this line of code to write a header to the thing:<\/P>
objFile.WriteLine “Name,Address,Phone”\n<\/PRE>\n
And then all we have to do is use the Write<\/B> method to write the value of strNewContent to the file. (And then use the Close<\/B> method to close Test.csv.) At that point we \u2013 just like the hitters that had to go up against the Scripting Son \u2013 are finished.<\/P>\n
Hope that helps. DM. It\u2019s a little complicated, but not if you take a deep breath and work your way through it. And we know that a lot of people are faced with similar tasks. We hope this helps all of you as well.<\/P>\n
Meanwhile, we \u2013 what\u2019s that? Oh, right: you noticed that small patch of blue sky off in the distance. If the weather\u2019s improving, does that mean that baseball season is already over?<\/P>\n
No, far from it: if the weather is improving that simply means that the Scripting Son doesn\u2019t have a game tonight. But don\u2019t worry: the forecast for tomorrow\u2019s game includes a temperature of 49 degrees and rain. Now that\u2019s<\/I> baseball weather.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I parse a tab-delimited file, and then save the resulting information as a comma-separated values file?— DM Hey, DM. You know, yesterday morning the Scripting Guy who writes this column had to scrape the ice off his windshield; today it\u2019s much warmer \u2013 44 at last report \u2013 although it […]<\/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,38,3,4,12,5],"class_list":["post-65243","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-files","tag-scripting-guy","tag-scripting-techniques","tag-storage","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I parse a tab-delimited file, and then save the resulting information as a comma-separated values file?— DM Hey, DM. You know, yesterday morning the Scripting Guy who writes this column had to scrape the ice off his windshield; today it\u2019s much warmer \u2013 44 at last report \u2013 although it […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65243","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=65243"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65243\/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=65243"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65243"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65243"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}