Webcc
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-,111111 111,abe,ma ytag,,533 E US HIGHWAY,,BUTLERVILLE,ST,77 777,,,4139 ,,,,,55555 55555
A,-V-RX-NU-NG-M-H-FO-EP-CE -C-,333333 333,Allen, Doe,,451A anystreet AVE,,GREENWOOD,ST,99999,,, 4139,,,,,5 555555555
Here is the output generated by the following code:
abe,maytag,,533 E US HIGHWAY,,BUTLERVILLE,ST,77 777,555555 5555,4139, 111111111, ,,,,,,,
Allen,Doe,,451A anystreet AVE,,GREENWOOD,ST,99999,55 55555555,4 139,333333 333,,,,,,, ,
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.Fi leSystemOb ject")
Set objTextFile = objFSO.OpenTextFile("C:\Pr ojects\Pro cessing\Ne wBen\In\uc s" & strDate & ".txt", ForReading)
strOutputFile="C:\Projects \Processin g\Legal\UC S-Addition s-0"+Repla ce(FormatD ateTime(Da te()),"/", "")+".txt" ' current date, replacing forward slashes with dashes..
Set objOutputFile = objFSO.CreateTextFile(strO utputFile)
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
A,-V-RX-NU-NG-M-H-FO-EP-CE
A,-V-RX-NU-NG-M-H-FO-EP-CE
Here is the output generated by the following code:
abe,maytag,,533 E US HIGHWAY,,BUTLERVILLE,ST,77
Allen,Doe,,451A anystreet AVE,,GREENWOOD,ST,99999,55
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.Fi
Set objTextFile = objFSO.OpenTextFile("C:\Pr
strOutputFile="C:\Projects
Set objOutputFile = objFSO.CreateTextFile(strO
Do While not objTextFile.AtEndOfStream
strL=objtextFile.Readline
arrR=split(strL,",")
for k=0 to 18
arrR(k)=Trim(replace(arrR(
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)
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.