How Can I Remove the Last Carriage Return-Linefeed in a Text File?
Hey, Scripting Guy! How can I remove the last carriage return-linefeed in a text file?
Hey, LEK. From the rest of your email you note that you’re using the FileSystemObject to read a text file, then using the contents of that text file as a query in another application. Unfortunately, though, the text file invariably has a carriage return-linefeed at the end, and that carriage return-linefeed is causing problems when you try passing the contents of the file as a query.
What does all that mean? Well, consider the simple little text file below. We opened the file and then pressed Ctrl+End to position the cursor at the end of the file. Note that the cursor does not appear immediately after the period in line 1; instead, the cursor appears at the beginning of line 2. That’s because there’s a carriage return-linefeed immediately following the period (as if someone typed in the first line and then pressed ENTER). You might not see anything on the screen, but it’s there and it’s creating problems:
So how are we going to get rid of that pesky little carriage return-linefeed? Here’s how:
We’re going to open the file and read the contents of that file into a variable named strFile.
We’re going to grab the last two characters stored in that variable. Why the last two characters? Well, the carriage return-linefeed actually consists of two separate characters: the carriage return (with an ASCII value of 13) and the linefeed (with an ASCII value of 10). Although we could probably get by with stripping away just the linefeed character we’re going to do things nice and neat and get rid of both characters.
We’re going to check and see if the last characters have an ASCII value of 13 and an ASCII value of 10; an easy way to do that is to see if they are equal to the VBScript constant vbCrLf (which happens to be a carriage return-linefeed). If those last two characters are equal to vbCrLf, we’ll chop the two characters off the string and save the new value (minus the carriage return-linefeed) back to the original text file. If they don’t, then we won’t do anything at all; in that case there’s no reason to do anything.
Got all that? Here’s a script that carries out all those tasks:
Const ForReading = 1 Const ForWriting = 2
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\scripts\test.txt”, ForReading) strFile = objFile.ReadAll objFile.Close
intLength = Len(strFile) strEnd = Right(strFile, 2)
If strEnd = vbCrLf Then strFile = Left(strFile, intLength – 2) Set objFile = objFSO.OpenTextFile(“C:\scripts\test.txt”, ForWriting) objFile.Write strFile objFile.Close End If
We start off by defining a pair of constants – ForReading and ForWriting – that we’ll use when we work with the text file. We create an instance of the FileSystemObject and use the OpenTextFile method to open the file C:\Scripts\Test.txt. We then use the ReadAll method to read in the entire contents of the text file and store that information in a variable named strFile. After all that we immediately close the text file. (Why? Well, the FileSystemObject allows you to open a file for reading or for writing. Because we have to read the file we had to open it for reading; if we later need to make changes to the file we’ll have to reopen it for writing. We’re done reading the file so we might as well close it.)
Next we use the Len function to determine the length of the variable strFile (that is, how many characters are in the string). This value – which we’ll use in a minute – is stored in the variable intLength.
So far so good, right? Now we need to determine whether or not the existing file has a carriage return-linefeed on the end. As we noted earlier, this means checking to see if the last two characters in the file consist of a carriage return character and a linefeed character. Thus we use the Right function to grab the two characters at the very end of the string and store these in the variable strEnd. That’s what we do here:
strEnd = Right(strFile, 2)
We can then check to see if strEnd is equal to vbCrLf. If it is, we use this line of code to grab all the characters except the two at the very end:
strFile = Left(strFile, intLength – 2)
Remember when we used the Len function to determine how many characters are in the string? Well, now we use that value to take the first length – 2 characters; for example, if our string has 37 characters, we’ll take only the first 35 (37 – 2). That will effectively chop the carriage return-linefeed off the end of the string.
We then reopen our text file – this time for writing – and replace the existing contents with the new string value. That will leave us with our original text file, minus the trailing carriage return-linefeed. And that’s just exactly what you were hoping to end up with.