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

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

0
Webcc
Asked:
Webcc
2 Solutions
 
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
 
JR2003Commented:
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
 
nayernaguibCommented:
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

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