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

asked on

Removing extraneous commas in VBS program

Have a program that I adapted to manipulate a file format.  Trying to get rid of the unwanted commas at the END of each record, think there is eight of them.       Here is the sample input file:
A,-V-RX-NU-NG-M-H-FO-EP-CE-C-,111111111,abe,maytag,,533 E US HIGHWAY,,BUTLERVILLE,ST,77777,,,4139,,,,,5555555555
A,-V-RX-NU-NG-M-H-FO-EP-CE-C-,333333333,Allen,Doe,,451A anystreet AVE,,GREENWOOD,ST,99999,,,4139,,,,,5555555555

Here is the output generated by the following code:
abe,maytag,,533 E US HIGHWAY,,BUTLERVILLE,ST,77777,5555555555,4139,111111111,,,,,,,,
Allen,Doe,,451A anystreet AVE,,GREENWOOD,ST,99999,5555555555,4139,333333333,,,,,,,,

Option Explicit

Const ForReading = 1

Dim objFSO
Dim objTextFile
Dim strOutputFile
Dim objOutputFile
Dim strL
Dim strOut
Dim arrR, arrR2
Dim strDate
Dim k

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

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\Projects\Processing\NewBen\In\ucs" & strDate & ".txt", ForReading)
strOutputFile="C:\Projects\Processing\Legal\UCS-Additions-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 18
        arrR(k)=Trim(replace(arrR(k),chr(34),""))
    next

    arrR(0) = empty  
    arrR(1) = empty
    arrR(11) = empty
    arrR(12) = empty
    arrR(14) = empty
    arrR(15) = empty
    arrR(16) = empty
    arrR(17) = empty

           arrR2 = arrR
           
   
    arrR(0) = arrR2(3)
    arrR(1) = arrR2(4) & ","
    arrR(2) = arrR2(6) & ","
    arrR(3) = arrR2(8)
    arrR(4) = arrR2(9)
    arrR(5) = arrR2(10)
    arrR(6) = arrR2(18)
    arrR(7) = arrR2(13)
    arrR(8) = arrR2(2)
    arrR(9) = empty
    arrR(10) = empty
    arrR(11) = empty
    arrR(12) = empty
    arrR(13) = empty
    arrR(14) = empty
    arrR(15) = empty
    arrR(16) = empty
    arrR(17) = empty
    arrR(18) = empty
     
    strOut=Join(arrR,",")

   
    objOutputFile.Write left(strOut,len(strOut)-1) & vbCRLF
Loop
   
objTextFile.close
objOutputFile.Close
SET objFSO=nothing

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

Avatar of jhance
jhance

It's not clear to me what you want to do, exactly.  Do you want to remove ALL the duplicated commas or only SOME of them?  If SOME, which ones?
SOLUTION
Avatar of JR2003
JR2003

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
Avatar of Webcc

ASKER

IF you look at the end of each line on the output file there are eight commas after the 111111111 and 333333333 in this example and I need to remove those commas only.  The other commas that come before must stay to delimit the fields and serve as placeholders.  So, it's just the commas at the end of each record.

Thanks
ASKER CERTIFIED SOLUTION
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