Solved

manipulate txt file with VB Script

Posted on 2011-10-01
5
246 Views
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?

Thanks,
McOz
0
Comment
Question by:McOz
  • 3
  • 2
5 Comments
 
LVL 51

Expert Comment

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

~bp
0
 
LVL 51

Expert Comment

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

~bp
0
 
LVL 9

Author Comment

by:McOz
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
SampleDataFile.txt
0
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 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)
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
 
LVL 9

Author Closing Comment

by:McOz
ID: 36897554
Perfect!! Thanks a lot.

-McOz
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now