• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

manipulate txt file with VB Script

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?

Thanks,
McOz
0
McOz
Asked:
McOz
  • 3
  • 2
1 Solution
 
Bill PrewCommented:
Sure.  Can you provide a sample test data file?

~bp
0
 
Bill PrewCommented:
Need to know how many columns there are in total also.

~bp
0
 
McOzAuthor Commented:
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
SampleDataFile.txt
0
 
Bill PrewCommented:
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)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  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)
objFile.Close

' 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
Next

' Close output file
objFile.Close

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

~bp
0
 
McOzAuthor Commented:
Perfect!! Thanks a lot.

-McOz
0
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

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now