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

WebccAsked:
Who is Participating?
 
nayernaguibConnect With a Mentor Commented:
If the number of commas at the end of each output record will be the same for all input records, you can simply add the following statement before the Write statement:

      strOut=Replace(strOut,",,,,,,,,,","")

If the number of commas is variable, you can use the following piece of code:

      For i=Len(strOut) To 0 Step -1
        If Mid(strOut, i, 1)<>"," Then
          Exit For
        End If
      Next
      strOut=Left(strOut, i+1)

_______________

  Nayer Naguib
0
 
jhanceCommented:
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?
0
 
JR2003Connect With a Mentor Commented:
strOut=Join(arrR,",")

'Add this line
mid(strOut,len(strOut) - 8, 8) = Replace(mid(strOut,len(strOut) - 8, 8), ",,,,,,,,", "")

   
    objOutputFile.Write left(strOut,len(strOut)-1) & vbCRLF
Loop





OR if there are ALWAYS eight commas you just need the following line:

strOut = Left(strOut, Len(strOut) - 8)
0
 
WebccAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.