Link to home
Start Free TrialLog in
Avatar of Webcc
WebccFlag for United States of America

asked on

Formatting output from a CSV to a fixed-width file - VBS

My source file:
111111111,JOHN,DOE,PO BOX 3333,ANYCITY,ST,55555,5555555,4139
111111111,JOHN,DOE,451A STANLEY AVE,ANYCITY,ST,55555,5555555,4139
111111111,JOHN,DOE,1606 S 21ST ST,ANYCITY,ST,55555,5555555,4139

Output needs to be:
AROADPR05052006
NEW      DOE      JOHN       PO BOX 3333      ANYCITY     ST55555      5555555        010065896MQVS4139111111111
NEW      DOE      ......
NEW      DOE      ......  and so on.....
ZROADPR050520060000003000000000000000000000

Explanation:
AROADPRDATE   (*Header)
NEW (Constant in first field)  Name (Last name first name)  Address   City STZIP  Phone  010065896MQVS(Constant value)4139(From last field of source)1111111111(From first field of source)
ZROADPRDATE0000003(Number of records in file)000000000000000000000(Trailing zero's are a constant value)   -  (*This the trailer record)

Positions:
NEW (1-8)
LAST (9-28)
FIRST(29-43)
ADDRESS (45-69)
CITY (95-114)
ST (115-116)
ZIP (117-121)
PHONE (129-138)
PROMO (150-162)
ACCT (163-182) LEFT JUSTIFY PAD SPACES

CODE STARTS:
Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\Projects\Test\In\ucs050506.txt", ForReading)
strOutputFile="C:\Projects\Test\Out\UCS.0"+Replace(FormatDateTime(Date()),"/","")+".txt"    ' current date, replacing forward slashes with dashes..
Set objOutputFile = objFSO.CreateTextFile(strOutputFile)

Do While not objTextFile.AtEndOfStream
    strL=objtextFile.Readline
    arrR=split(strL,",")
    for k=0 to 8
        arrR(k)=Trim(replace(arrR(k),chr(34),""))
next

NEED HELP!!!!
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just noticed I left out a few statements that should be part of the script (not strictly required for it to work but good programming practices should be followed)...

Add these 4 lines after "objOutFile.WriteLine strL"

objOutputFile.Close
Set objFSO = Nothing
Set objTextFile = Nothing
Set objOutputFile = Nothing
Avatar of Webcc

ASKER

Thanks Lynn, it looks like it will work great!!!

Just need to learn the code!

Bill