Removing extraneous commas in VBS program

Posted on 2006-05-13
Last Modified: 2012-06-21
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
    for k=0 to 18

    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

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

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

Question by:Webcc
    LVL 32

    Expert Comment

    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?
    LVL 18

    Assisted Solution


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

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

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

    strOut = Left(strOut, Len(strOut) - 8)

    Author Comment

    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.

    LVL 14

    Accepted Solution

    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:


    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
          strOut=Left(strOut, i+1)


      Nayer Naguib

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
    This is about my first experience with programming Arduino.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    779 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

    17 Experts available now in Live!

    Get 1:1 Help Now