manipulate txt file with VB Script

Posted on 2011-10-01
Medium Priority
Last Modified: 2012-05-12
I have a tab-delimited text file (with headers) where the third column is a check number.

Is there a way using VB Script to open/read the txt file, pad all the check numbers out to n digits using zeros and close it?

Question by:McOz
  • 3
  • 2
LVL 60

Expert Comment

by:Bill Prew
ID: 36896960
Sure.  Can you provide a sample test data file?

LVL 60

Expert Comment

by:Bill Prew
ID: 36896970
Need to know how many columns there are in total also.


Author Comment

ID: 36896977
Here is a very simple example. The user wants to be able to just drop the txt file onto the script, so you can just grab the filepath from WScript.Arguments(0)

Tx McOz
LVL 60

Accepted Solution

Bill Prew earned 2000 total points
ID: 36896992
Here's my first shot at this, should get the job done.  You just have to adjust the length in the LeftPad call.

It can be run with either 1 or 2 command line parms.  If one it rewrites the input file.  If 2, then it preserves the input file, and writes the changed version to the second parm.  Helpful for testing...

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Delim = Chr(9)
' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
  WScript.Echo "No filename specified."
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
  strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)

' Output file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Loop through all lines of the file, pad 3rd field on left with leading zeros
for i = 0 To UBound(arrLines)
    If arrLines(i) <> "" Then
        arrFields = Split(arrLines(i), Delim)
        ' First line will be header, don't pad on it
        If i > 0 Then
          arrFields(2) = LeftPad(arrFields(2), 20, "0")
        End If
        objFile.WriteLine Join(arrFields, Delim)
    End If

' Close output file

Function RightPad( strText, intLen, chrPad )
  RightPad = Left( strText & String( intLen, chrPad ), intLen )
End Function

Function LeftPad( strText, intLen, chrPad )
  LeftPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Open in new window


Author Closing Comment

ID: 36897554
Perfect!! Thanks a lot.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question