Using ADO to read a text file with all the data on one line ask:
Hello, Scripting Guy! I have a fixed-width text file that contains the first name (6 characters), last name (13 characters), and department (9 characters), and they are all on the same line. How do I use ADO to read it?
--DW
For:
Hello, DW. Frankly speaking, we don't think you can use ADO (ActiveX data object) to read such a file. ADO is a great way to interact with text files (see the article for more information about ADO about text files (English)), but, indeed, ADO is a bit picky: each of its settings must be completely correct, or it will refuse to work. (unlike the Scripting Guys, even if everything is ready, they will refuse to work.) As a database technology, ADO needs a database that is "structurally sound" (borrowing a term in the XML domain), and it cannot directly take advantage of your file format and generate a database from it. (In this case, you can divide your macro row into several individual records.) ）
But it doesn't matter; after all, we can create a well-formed database using the old but good and reliable VBScript. What we're going to do today is show you how to split your huge data string into a single record quickly and easily. We can accomplish this by simply echoing the individual records (that is, a single record in a text file) back to the screen. However, you can write these record segments to another text file and then use ADO to read data from them. We're just going to give you a head start, and the rest is on your own.
First, we assume that you already have a text file similar to the following, where the field name and two records of the file contain the Yutong line:
Dept Ken Meyer Finance Pilar Ackerman HR
We hope that the final results will be more similar to those shown below:
Dept
Ken Meyer Finance
Pilar Ackerman HR
Is that possible? Of course it might:
Copy Code code as follows:
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 < Then
Exit do
End If
Strlines = Strlines & Left (strContents,) & VbCrLf
StrContents = Right (strContents, intLength-28)
Loop
WScript.Echo Strlines
First define a constant named ForReading and set its value to 1; we'll use it to open the text file that we want to read.
Note: Do you know why your mother keeps telling you to take your hat-pocket or look both ways across the street? Yes, we Scripting Guys have been telling you that you can't open a text file without specifying how it will be opened: Read, write, or Add. Only one operation can be performed at a time: The text file cannot be opened both in read and write mode. Therefore, constant ForReading is required.
Oh, also put on your hat pocket. It's very cold outside.
Next we create an instance of FileSystemObject and use the OpenTextFile method to open the file C:\Scripts\Test.txt. We call the ReadAll method to read the entire contents of this file into the variable strContents, and then close the file using the closing method.
The following line of code is short and somewhat quirky:
i = False
What we have to do immediately is to create a Do loop that reads the contents of the file (that is, the value of the variable strContents) in increments of 28 characters. Why is it 28 characters? Because, in each record, we use 6 characters to save the first name, 13 characters to save the last name and 9 characters to save the department. So:
6 + 13 + 9 = 28
To ensure that the loop always continues until the entire file is read, we set the variable I to False and then execute the loop until I equals True:
Do Until i = True
We find that I will never equal True, but don't worry: we can still jump out of this loop and show you this magical feature later.
Inside the loop, we first use the Len function to determine the number of characters in the string strContents (for our sample file, plus the header row as a record, a total of 3 records, each 28 characters, that is, 84 characters in total). Then this little piece of code:
If Intlength < Then
Exit do
End If
All we have to do here is check if the string length is less than 28 characters. If so, then we must have reached the end of the file. So we use the Exit Do command to exit the loop. (This is the way the very great Scripting Guys managed to escape the clutches of the never-ending do loops!) )
If the length is 28 characters or more, the following line of code is executed:
Strlines = Strlines & Left (strContents,) & VbCrLf
Here we create a new string strlines, which divides the contents of the text file into individual records, each consisting of 28 characters and a carriage return line feed (vbCrLf). To do this, we simply connect the current content of the StrLine with the first 28 characters in the string (exactly this operation performed by the Left function) and the constant vbCrLf of VBScript. After the first cycle strlines results are as follows:
Dept
Do you understand how this works?
After the first line (that is, our first record) is safely placed in the variable strlines, we then remove the information from the strContents. That's what the following code is doing:
StrContents = Right (strContents, intLength-28)
This time we're going to use the right function to remove x characters from the end of the string, which is reverse execution. So what do we mean by the X-character? To get the value of x, we need to take the length of the StrContents (84) and subtract 28 (the length of a single record). The resulting result is 56 (84-28 = 56), so we start at the end of the string and return the number 56 characters. This means that after the first cycle we will get the following results:
Ken Meyer Finance Pilar Ackerman HR
Note: Yes, there are other ways to achieve the same goal, some of which may save one or two of lines of code. However, we consider this method to be the simplest.
As you can see, all we've done is delete the first record. Now you are ready to return to the loop and repeat this process. Of course, the length of this strContents is 56, because we have just removed 28 characters. Naturally, this means that we want to stop extracting this data and stop adding it to the variable strlines:
Ken Meyer Finance
We continue this process until we delete all the characters in the variable strContents. At this point, we just echo back the Strlines value:
Dept
Ken Meyer Finance
Pilar Ackerman HR
If it's not a well-formed database, we don't know what the result will look like.
If you want to be able to save data to a text file and you can read this data using ADO, do as we say, or you can divide each row into individual records by using only the string-handling capabilities of VBScript. We have left you with the right to decide.
