Hey, Scripting Guy! I have a text file containing latitudes and longitudes. The numbers look like this: 36000000. I need to format these numbers so they look like this: 36.000000. How can I reformat numbers using a script?

— BE

Hey, BE. You know, if you have a question about numbers then you came to the right place. After all, one of the Scripting Guys is the proud owner of a degree in advanced mathematics. And the other Scripting Guys … well, never mind about the other Scripting Guys. Did we mention that one of the Scripting Guys is the proud owner of a degree in advanced mathematics?

Fortunately, you don’t need a degree in advanced mathematics to solve this problem. (And fortunately we don’t need one either, or this might be a really short column today.) As you noted, BE, you have a couple of text files containing latitude and longitude entries similar to this:

36000000
-076000000
92000000
105000000

What you want to do is open the text file, reformat the numbers, and then save the reformatted text file. When all is said and done you’d like your text file to look like this:

36.000000
-76.000000
92.000000
105.000000

Can we accomplish this feat using a script? Piece of cake:

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“c:\scripts\test.txt”, ForReading)
Do Until objFile.AtEndOfStream
    intLine = objFile.Readline
    intLine = intLine/ 1000000
    intLine = FormatNumber(intLine, 6)
    strText = strText & intLine & vbCrLf
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(“c:\scripts\test.txt”, ForWriting)
objFile.Write strText
objFile.Close

And don’t worry: we’ll explain how this all works. After all, that’s what we’re here for, right?

As you can see, the script starts out simple enough. We begin by defining a pair of constants – ForReading and ForWriting – which we’ll use when working with the text file itself. We create an instance of the FileSystemObject and then use the OpenTextFile method to open the file C:\Scripts\Test.txt for reading. (As you might well know, you can open a text file for reading or you can open a text file for writing, but you can’t perform both operations at the same time.)

That brings us to this block of code:

Do Until objFile.AtEndOfStream
    intLine = objFile.Readline
    intLine = intLine/ 1000000
    intLine = FormatNumber(intLine, 6)
    strText = strText & intLine & vbCrLf
Loop

This is where all the excitement takes place. To begin with, we set up a Do Until loop to read through the file line-by-line, then continue reading until we’ve read every last line in the file (that is, when the AtEndOfStream property is True). Inside that loop we use the ReadLine method to read the first line of the file and store that value (in our sample file, 36000000) in a variable named intLine.

The next step is easy. We want to take the number 36000000 and turn it into 36.000000. How do we do that? For starters, we divide the value by 1000000:

intLine = intLine/ 1000000

That’s going to give us – in this case – the value 36. To display that value with 6 decimal points all we have to do is call on the FormatNumber function:

intLine = FormatNumber(intLine, 6)

All we’re doing here is assigning a new value to the variable intLine. What’s that new value going to be? Well, it’s going to be the result of running the existing value of intLine (36) through the FormatNumber function, asking FormatNumber to display the result using 6 decimal places. (That’s what the parameter 6 is for: it indicates the number of decimal places we want displayed.) The end result: after turning 36000000 into 36, we’ve now turned 36 into 36.000000. Which is the very thing we set out to do in the first place.

After converting the first line (first number) in the text file we then add the new value plus a carriage return-linefeed (vbCrLf) to a variable named strText:

strText = strText & intLine & vbCrLf

As you probably figured out, strText is simply a variable that keeps track of our reformatted numbers. After we’ve read through all the lines in the text file the value of strText will look like this:

36.000000
-76.000000
92.000000
105.000000

If you’re thinking, “Say, that looks a lot like the way we want the numbers in the text file to be formatted,” well, give yourself a gold star: this looks exactly like the way we want the numbers in the text file to be formatted.

Because of that we can achieve our end goal simply by saving the value of strText to the text file. To do that we first close the file C:\Scripts\Test.txt. Why? Remember, we opened the file for reading; in order to write to it, we have to close the file and then reopen it for writing. That’s what we do here:

objFile.Close
Set objFile = objFSO.OpenTextFile(“c:\scripts\test.txt”, ForWriting)

We use the Write method to write the value of strText to the text file and then close the file one last time. The net result: our text file now looks like this:

36.000000
-76.000000
92.000000
105.000000

Not bad for a bunch of Scripting Guys with limited math skills, is it?