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

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!!!!
0
Webcc
Asked:
Webcc
  • 2
1 Solution
 
JesterTooCommented:
If I understood your specs correctly then this script should work for you...

' CSV2SDF.VBS

Option Explicit

Const ForReading = 1

Dim objFSO
Dim objTextFile
Dim objOutputFile
Dim strOutputFile
Dim strL, arrR
Dim strDate
Dim k, nRecCount

nRecCount = 0

strDate = PadL(Month(Date()),2) & PadL(Day(Date()),2) & Year(Date())

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)

objOutputFile.WriteLine "AROADPR" & strDate

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
   strL = "NEW     "                       ' constant............. (1-8)
   strl = strl & PadR(arrR(2),20)           ' LAST    20 characters (9-28)
   strl = strl & PadR(arrR(1),15)           ' FIRST   15 characters (29-43)
   strl = strl & " "                       ' filler...             (44-44)
   strl = strl & PadR(arrR(3),25)           ' ADDRESS 25 characters (45-69)
   strl = strl & Space(25)                 ' filler...             (70-94)
   strl = strl & PadR(arrR(4),20)           ' CITY    20 characters (95-114)
   strl = strl & PadR(arrR(5),2)            ' ST       2 characters (115-116)
   strl = strl & PadR(arrR(6),5)            ' ZIP      5 characters (117-121)
   strl = strl & Space(7)                  ' filler...             (122-128)
   strl = strl & PadR(arrR(7),10)           ' PHONE   10 characters (129-138)
   strl = strl & Space(11)                 ' filler...             (139-149)
   strL = strL & "010065896MQVS"           ' PROMO   13 characters (150-162)
   strL = strL & PadR(arrR(8) & arrR(0),20) ' ACCT    20 characters (163-182)
   objOutputFile.WriteLine strL
   nRecCount = nRecCount + 1
Loop

strL = "ZROADPR" & strDate & PadL(nRecCount,7) & "000000000000000000000"

objOutputFile.WriteLine strL


Function PadR(strText,nLength)
   PadR = Left(strText & Space(nLength), nLength)
End Function


Function PadL(strText,nLength)
Dim strTemp
   strTemp = Space(nLength)
   strTemp = Replace(strTemp," ","0")
   PadL    = Right(strTemp & strText, nLength)
End Function


HTH,
Lynn
0
 
JesterTooCommented:
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
0
 
WebccAuthor Commented:
Thanks Lynn, it looks like it will work great!!!

Just need to learn the code!

Bill
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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