{"id":65913,"date":"2006-12-05T20:56:00","date_gmt":"2006-12-05T20:56:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/12\/05\/how-can-i-convert-a-tilde-delimited-file-to-microsoft-excel-format\/"},"modified":"2006-12-05T20:56:00","modified_gmt":"2006-12-05T20:56:00","slug":"how-can-i-convert-a-tilde-delimited-file-to-microsoft-excel-format","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-convert-a-tilde-delimited-file-to-microsoft-excel-format\/","title":{"rendered":"How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?"},"content":{"rendered":"

\"Hey,<\/p>\n

Hey, Scripting Guy! How can I convert a tilde-delimited file to Microsoft Excel format?<\/p>\n

— JN<\/p>\n

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

Hey, JN. You know, to tell you the truth we\u2019re beginning to rethink our approach to this column. After all, usually we tell people how easy things are, and how \u2013 without much effort \u2013 they can write a script that will let them accomplish some seemingly-impossible task. However, yesterday the Scripting Son was flipping through the channels when he stumbled upon an infomercial for a knife \u2013 wait, did we say \u201cknife?\u201d This was no knife; in fact, it doesn\u2019t even cut food, it \u201csonically separates food.\u201d We\u2019d like to see a knife do that<\/i>.<\/p>\n

Anyway, this actually looked like a pretty good product; however, what we thought was really<\/i> interesting was the commercial itself. \u201cYou know how hard it can be to cut a sandwich in two?\u201d asked the narrator. Now, to be honest, cutting a sandwich was always one of the few things the Scripting Guy who writes this column thought he was able to do. But it turns out he was just kidding himself: as the video plainly showed, it\u2019s impossible<\/i> to cut a sandwich with a regular knife.<\/p>\n

OK, granted, this sandwich was about a foot high; even if you could<\/i> cut it with a knife we have no idea how you\u2019d go about eating it. Regardless, as the poor guy on the commercial tried cutting his foot-high sandwich everything went wrong: the sandwich fell apart, the tomatoes squirted out on to the floor, it was excruciating to watch a perfectly-good sandwich get destroyed like that. \u201cDon\u2019t feel bad,\u201d said the narrator. \u201cThis has happened to all of us.\u201d They then showed how, although you can\u2019t cut a foot-high sandwich, you can<\/i> sonically separate a foot-high sandwich. Hallelujah! Finally the Scripting Guy who writes this column can do something with all those foot-high sandwiches that have been piling up in his kitchen.<\/p>\n

\u201cAnd best of all: no more tired muscles from cutting food,\u201d added the narrator. So there you go: if you\u2019ve been painstakingly cutting your toast every morning then feeling worn-out and tired the rest of the day, we have good news for you: relief is at hand. <\/p>\n

So what does any of that have to do with scripting? Well, it got us thinking. After all, if you\u2019re like most people, you probably have hundreds of tilde-delimited files like this lying around in your computer:<\/p>\n

Ken Myer~Fiscal Specialist~Accounting\nPilar Ackerman~Customer Service Representative~Accounting\nJonathan Haas~Manager~Accounting\n<\/pre>\n
No doubt you\u2019ve tried to open these files using Microsoft Excel. But what happens when you do that? Disaster! Instead of seeing the tilde character (~) as a field separator, Excel simply throws all the data into column A:<\/p>\n
\"Microsoft<\/p>\n
Factor in the sore muscles you get from trying to open a tilde-delimited file and your entire day has been completely ruined. Worst of all, there\u2019s absolutely nothing you can do about that! Your tilde-delimited files are useless, your muscles are sore and tired, and you\u2019re doomed to live a life of squalor and despair. <\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Welcome to the Scripting Guys world.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Or are you? Listen, don\u2019t throw away those tilde-delimited files; instead, use the Scripting Guys patented Sonic Data Separation Technology to convert these tilde-delimited files into something that Excel can<\/i> open properly:<\/p>\n
Const ForReading = 1\nConst ForWriting = 2\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\nstrContents = objFile.ReadAll\nobjFile.Close\nstrContents = Replace(strContents, \"~\", vbTab)\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\nobjFile.Write strContents\nobjFile.Close\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.txt\")\n<\/pre>\n
No doubt you\u2019re saying, \u201cSonic Data Separation Technology? How does that<\/i> work?\u201d Let\u2019s see if we can explain. Because JN specifically wanted to convert the tilde-delimited files, we need to get rid of the tildes and replace them with something Excel does<\/i> see as a delimiter (namely, the Tab character). Here\u2019s how we do that.<\/p>\n
We start off by defining a pair of constants (ForReading and ForWriting) needed when opening the tilde-delimited text file (C:\\Scripts\\Test.txt). With the constants in tow we then use this line of code to create an instance of the Scripting.FileSystemObject<\/b>:<\/p>\n
Set objFSO = CreateObject(\"Scripting.FileSystemObject\")\n<\/pre>\n
Once we have an instance of the FileSystemObejct we can then use this block of code to open the text file, read the entire contents into a variable named strContents, and then close the file:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\nstrContents = objFile.ReadAll\nobjFile.Close\n<\/pre>\n
What\u2019s the point of all that? Well, we can\u2019t actually manipulate the text file directly; the FileSystemObject doesn\u2019t allow for that. Therefore we need to open the text file, store a copy of the contents in memory, then manipulate that virtual copy of the file. After we\u2019ve made our changes (that is, after we replace all the tilde characters with Tab characters) we\u2019ll then reopen the file and write the modified contents back to Test.txt.<\/p>\n
So let\u2019s go ahead and do that right now. To replace the tilde characters we use the VBScript Replace<\/b> function, replacing all instances of ~ with a tab (represented by the VBScript constant vbTab<\/b>):<\/p>\n
strContents = Replace(strContents, \"~\", vbTab)\n<\/pre>\n
That changes our virtual copy of the text file. We then reopen the actual file itself (this time for writing), use the Write<\/b> method to replace the contents of the file, then close Test.txt:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\nobjFile.Write strContents\nobjFile.Close\n<\/pre>\n
Believe it or not, it\u2019s that simple: the patented Sonic Data Separation Technology has now converted Test.txt to a format Excel can open, and open properly. Hard to believe? We understand. That\u2019s why we added a block of code to the end of the script that creates an instance of Microsoft Excel, makes that instance visible, and then opens the file C:\\Scripts\\Test.txt:<\/p>\n
Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.txt\")\n<\/pre>\n
Here\u2019s what shows up on screen:<\/p>\n
\"Microsoft<\/p>\n
Is this the greatest thing that has ever happened in the history of the human race? Maybe not. But we can\u2019t think of anything better.<\/p>\n
You seem a little skeptical. \u201cThat is<\/i> pretty cool, Scripting Guys. But what if I don\u2019t want<\/i> to convert my text file? What if I just want to open the file, leaving the tildes in place? I guess I\u2019m out of luck, aren\u2019t I?\u201d<\/p>\n
Out of luck? Au contraire: this is your lucky<\/i> day. That\u2019s because, at no extra charge, we\u2019ve included our patented Sonic Data Import Filter attachment, enabling you to import tilde-delimited files into Excel, and without having to modify that file in any way!<\/i> We don\u2019t have time today to discuss how this all works, but simply attach the Data Import Filter and import away:<\/p>\n
Const xlDelimited  = 1\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nobjExcel.Workbooks.OpenText \"C:\\Scripts\\Test.txt\",,,xlDelimited,,,,,,,True,\"~\"\n<\/pre>\n
Yes, it\u2019s a crazy looking little script, or at least the last line is. But that\u2019s because the OpenText<\/b> method includes a number of parameters that are irrelevant for our purposes. We don\u2019t want to use them; however, we do<\/i> have to leave an empty placeholder for each one. Consequently, we end up with a bunch of blank parameters. The important thing here is that we tell Excel that we are working with a delimited text file (xlDelimited<\/b>); that we\u2019re using a non-standard delimiter (True<\/b>); and that non-standard delimiter happens to be the tilde character (~<\/b>).<\/p>\n
Did we mention that the Scripting Guys Sonic Data Separator is available now for just $29.95? Call today and we\u2019ll throw in the Sonic Data Import Filter attachment absolutely free<\/i>! This is a limited-time offer, so don\u2019t delay: operators are standing by. <\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. The Scripting Guys Sonic Data Separator is not available in stores. For good reason.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I convert a tilde-delimited file to Microsoft Excel format? — JN Hey, JN. You know, to tell you the truth we\u2019re beginning to rethink our approach to this column. After all, usually we tell people how easy things are, and how \u2013 without much effort \u2013 they can write 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":[710,711,38,40,48,49,3,5],"class_list":["post-65913","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-files","tag-filesystemobject","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I convert a tilde-delimited file to Microsoft Excel format? — JN Hey, JN. You know, to tell you the truth we\u2019re beginning to rethink our approach to this column. After all, usually we tell people how easy things are, and how \u2013 without much effort \u2013 they can write a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65913","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=65913"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65913\/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=65913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}