How Can I Use ADO to Read a Text File Where All the Data is on a Single Line?

ScriptingGuy1

Hey, Scripting Guy! I have a fixed-width text file which includes first name (6 characters), last name (13 characters), and department (9 characters) all on a single line. How do I read that using ADO?

— DW

Hey, DW. Well, to be perfectly honest, we don’t think you can read a file like this using ADO (ActiveX Data Objects). ADO is a fantastic way to interact with text files (for more information, see the article Much ADO About Text Files), but – really – ADO is a bit of a prima donna: everything has to be set up just right or ADO will refuse to work. (Unlike the Scripting Guys, who refuse to work even if everything is set up just right.) As a database technology, ADO requires – to steal a term from the XML world – a “well-formed” database; it’s simply not equipped to take a file like yours and make a database out of it. (In this case, by chopping your huge line into individual records.)

But that’s all right; after all, we can use good old, reliable VBScript to create a well-formed database. What we’ll do today is show you how you can quickly and easily carve your huge string of data into individual records. We’ll do that simply by echoing those individual records (i.e., individual records in the text file) back to the screen. However, you could easily write those lines to another text file and then use ADO to read in that second text file. We’re just going to help you get started, then let you take it from there.

To begin with, we’re assuming you have a text file that looks something like this, with field names and a couple of records all contained on a single line:

First Last         Dept     Ken   Meyer        Finance  Pilar Ackerman     HR

What we want to end up with is something that looks more like this:

First Last         Dept     
Ken   Meyer        Finance  
Pilar Ackerman     HR

Is that possible? Of course it is:

Const ForReading = 1

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
strContents = objFile.ReadAll
objFile.Close
i = False
Do Until i = True 
    intLength = Len(strContents)
    If intLength < 28 Then
        Exit Do
    End If
    strLines = strLines & Left(strContents, 28) & vbCrLf
    strContents = Right(strContents, intLength – 28)
Loop
Wscript.Echo strLines

We start out by defining a constant named ForReading and set the value of that constant to 1; we’ll use that to open our text file for reading.

Next we create an instance of the FileSystemObject and use the OpenTextFile method to open the file C:\Scripts\Test.txt. We call the ReadAll method in order to read the entire contents of that file into the variable strContents, then use the Close method to close the file.

After that we have this odd little line of code:

i = False

What we’re going to do in a second is set up a Do loop that enables us to read the contents of the file (well, OK, the value of the variable strContents) in 28-character increments. Why 28 characters? Well, we have 6 characters in each record reserved for the first name, 13 characters set aside for the last name, and 9 characters allotted for the department. Thus:

6 + 13 + 9 = 28

In order to keep our loop going until we’ve read through the entire file, we set the variable i to False, then tell our loop to keep going until i is equal to True:

Do Until i = True

As it turns out, i never will equal True, but don’t worry: we’ll still be able to get out of the loop, and we’ll show you that Houdini-like feat in a moment.

Inside the loop we first use the Len function to determine the number of characters in the string strContents (with our sample file, and counting the header line as a record, that should be 28 characters per record times 3 records, or 84). We then have this little block of code:

If intLength < 28 Then
    Exit Do
End If

What we’re doing here is checking to see if the length of our string is less than 28 characters. If it is, then we must have reached the end of the file. Therefore, we use the Exit Do command to exit the loop. (And that’s how the amazing Scripting Guys managed to escape the clutches of the never-ending Do loop!)

Assuming the length is 28 characters or more, we then execute this line of code:

strLines = strLines & Left(strContents, 28) & vbCrLf

Here we’re creating a new string – strLines – that divides the contents of our text file into separate records, each record consisting of 28 characters plus a carriage return-linefeed (vbCrLf). To do that, we simply combine the current contents of strLine with the first 28 characters in the string (that’s what the Left function does) and the VBScript constant vbCrLf. The first time through the loop strLines will look like this:

First Last         Dept

See how that works?

As soon as our first line (that is, our first record) is safely tucked away in the variable strLines, we then need to delete that information from strContents. That’s what we do here:

strContents = Right(strContents, intLength – 28)

This time we’re using the Right function to take x number of characters from the end of the string, working our way backwards. What, exactly, do we mean by x number of characters? To calculate x we take the length of strContents (84) and subtract 28 (the length of an individual record). That leaves us with 56 (84 – 28 = 56) so we start at the end of the string and count back 56 characters. That means, the first time through the loop, we’ll end up with this:

Ken   Meyer        Finance  Pilar Ackerman     HR

As you can see, all we’ve done is remove the first record. We’re now ready to go back through the loop and repeat the process. This time, of course, the length of strContents will be 56; that’s because we just eliminated 28 characters. In turn, that means we’ll end up pulling out this data and adding it to the variable strLines:

Ken   Meyer        Finance

We continue this process until we run out of characters in the variable strContents. At that point we simply echo back the values of strLines:

First Last         Dept     
Ken   Meyer        Finance  
Pilar Ackerman     HR

If that’s not a well-formed database, well, we don’t know what is.

Like we said, if you wanted to you could save the data to a text file and read it using ADO, or you could use simply use VBScript’s string-handling functions to divvy each line into individual records. We’ll leave that up to you.

Oh, and one other thing before we go: didn’t we tell you to put your hood up?

ScriptingGuy1

Follow