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’re beginning to rethink our approach to this column. After all, usually we tell people how easy things are, and how – without much effort – 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 – wait, did we say “knife?” This was no knife; in fact, it doesn’t even cut food, it “sonically separates food.” We’d like to see a knife do that.

Anyway, this actually looked like a pretty good product; however, what we thought was really interesting was the commercial itself. “You know how hard it can be to cut a sandwich in two?” 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’s impossible to cut a sandwich with a regular knife.

OK, granted, this sandwich was about a foot high; even if you could cut it with a knife we have no idea how you’d 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. “Don’t feel bad,” said the narrator. “This has happened to all of us.” They then showed how, although you can’t cut a foot-high sandwich, you can 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.

“And best of all: no more tired muscles from cutting food,” added the narrator. So there you go: if you’ve 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.

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

Ken Myer~Fiscal Specialist~Accounting
Pilar Ackerman~Customer Service Representative~Accounting
Jonathan Haas~Manager~Accounting

No doubt you’ve 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:

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’s absolutely nothing you can do about that! Your tilde-delimited files are useless, your muscles are sore and tired, and you’re doomed to live a life of squalor and despair.

Or are you? Listen, don’t 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 open properly:

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close
strContents = Replace(strContents, "~", vbTab)
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.Write strContents
objFile.Close
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.txt")

No doubt you’re saying, “Sonic Data Separation Technology? How does that work?” Let’s 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 see as a delimiter (namely, the Tab character). Here’s how we do that.

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:

Set objFSO = CreateObject("Scripting.FileSystemObject")

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:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close

What’s the point of all that? Well, we can’t actually manipulate the text file directly; the FileSystemObject doesn’t 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’ve made our changes (that is, after we replace all the tilde characters with Tab characters) we’ll then reopen the file and write the modified contents back to Test.txt.

So let’s go ahead and do that right now. To replace the tilde characters we use the VBScript Replace function, replacing all instances of ~ with a tab (represented by the VBScript constant vbTab):

strContents = Replace(strContents, "~", vbTab)

That changes our virtual copy of the text file. We then reopen the actual file itself (this time for writing), use the Write method to replace the contents of the file, then close Test.txt:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.Write strContents
objFile.Close

Believe it or not, it’s 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’s 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:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.txt")

Here’s what shows up on screen:

Is this the greatest thing that has ever happened in the history of the human race? Maybe not. But we can’t think of anything better.

You seem a little skeptical. “That is pretty cool, Scripting Guys. But what if I don’t want to convert my text file? What if I just want to open the file, leaving the tildes in place? I guess I’m out of luck, aren’t I?”

Out of luck? Au contraire: this is your lucky day. That’s because, at no extra charge, we’ve 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! We don’t have time today to discuss how this all works, but simply attach the Data Import Filter and import away:

Const xlDelimited  = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,,xlDelimited,,,,,,,True,"~"

Yes, it’s a crazy looking little script, or at least the last line is. But that’s because the OpenText method includes a number of parameters that are irrelevant for our purposes. We don’t want to use them; however, we do 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); that we’re using a non-standard delimiter (True); and that non-standard delimiter happens to be the tilde character (~).

Did we mention that the Scripting Guys Sonic Data Separator is available now for just $29.95? Call today and we’ll throw in the Sonic Data Import Filter attachment absolutely free! This is a limited-time offer, so don’t delay: operators are standing by.