[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
    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

2 Solutions
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?

'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)
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.

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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