Replace Pipes with Dashes in 1 column only? 2

[misscrf]

I am not a vb programmer. I dabble in vba. This script was inherited from a former co-worker.

What I need this script to do is replace all pipe (|) characters in the 71st column only, to a dash (-).

Right now, the code is not doing that, but it is adding a double quote comma and double quote (",") at the end of every line.

Here is the code:

Const ForAppending = 8
Const ForReading = 1

strFileName = InputBox("Enter IDX Filename: ")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile (strFileName, ForReading)
intPos = InStr(1,strFileName,".",1)
strFileName = Left(strFileName,(intPos-1))
Set objTextFile2 = objFSO.OpenTextFile (strFileName & "-Cleaned_SC.idx", ForAppending, True)


linenumber = 1

Do Until objTextFile.AtEndOfStream
    strNextLine = objTextFile.Readline
    arrServiceList = Split(strNextLine , Chr(34) & "," & Chr(34))
    If linenumber > 0 Then
       For i = 0 to Ubound(arrServiceList)
          If i <= Ubound(arrServiceList) Then	   		 		
	   		 		If (i = 71) Then 	     				
 	     					strTemp = Replace(arrServiceList(i), "|", "-")
	     					objTextFile2.Write(strTemp&Chr(34)&","&Chr(34))   						
	   				ElseIf (i = 95)  Then 	   						
	   						strTemp = Replace(arrServiceList(i), ",", "|")  
	   						objTextFile2.Write(strTemp)
	   				ElseIf (i = 20) Or (i = 22) or (i = 26)Or (i = 64)  Then 	   						
	   						strTemp = Replace(arrServiceList(i), ",", "|")  
	   						objTextFile2.Write(strTemp&Chr(34)&","&Chr(34))
	   				Else 	   					 
	     					objTextFile2.Write(arrServiceList(i)&Chr(34)&","&Chr(34))
         		End If     		 
         End If
       Next 
       objTextFile2.Write(vbCrLf)
    End if
linenumber = linenumber + 1
Loop

objTextFile.Close
objTextFile2.Close

MsgBox "Replace Pipes script is finished running."

I have been looking some of this stuff up online, but I am not understanding where in the code it is adding that stupid "," at the end of the file, and why it is not replacing | with - es.

Any help would be greatly appreciated!

Thank you,


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Sorry. I forgot to mention that this file is a comma delimited file with a double quote text qualifier.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

where in the code it is adding that stupid "," at the end of the file
&Chr(34)&","&Chr(34)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Geates]

A) Eliminate the ELSEIF's, they are pointless per your question.
B) As far a I can tell, linenumber will never be less than 1 and is not used anywhere - it is also pointless.
C) The IF that checks if i <= ubound(arrServiceList) is also pointless as that condition is satisfied by the parent for loop[/blue]
D) The extra "," is coming from the ELSE - pointless.

This

[red][s]linenumber = 1[/s][/red]
Do Until objTextFile.AtEndOfStream
	strNextLine = objTextFile.Readline
	arrServiceList = Split(strNextLine , Chr(34) & "," & Chr(34))
	[red][s]If linenumber > 0 Then[/s][/red]
		[blue]For i = 0 to Ubound(arrServiceList)[/blue]
			[red][s]If i <= Ubound(arrServiceList) Then[/s][/red]
				If (i = 71) Then
					strTemp = Replace(arrServiceList(i), "|", "-")
					objTextFile2.Write(strTemp & Chr(34) & "," & Chr(34))
				[red][s]ElseIf (i = 95)  Then[/s][/s][/red]
					[red][s]strTemp = Replace(arrServiceList(i), ",", "|")[/s][/red]
					[red][s]objTextFile2.Write(strTemp)[/s][/red]
				[red][s]ElseIf (i = 20) Or (i = 22) or (i = 26)Or (i = 64)  Then[/s][/red]
					[red][s]strTemp = Replace(arrServiceList(i), ",", "|")[/s][/red]
					[red][s]objTextFile2.Write(strTemp&Chr(34)&","&Chr(34))[/s][/red]
				[red][s]Else[/s][/red]
					[red][s]objTextFile2.Write(arrServiceList(i) & Chr(34) & "," & Chr(34))[/s][/red]
				End If
			[red][s]End If[/s][/red]
		[blue]Next[/blue]
		objTextFile2.Write(vbCrLf)
	[red][s]End if[/s][/red]
	[red][s]linenumber = linenumber + 1[/s][/red]
Loop

becomes

do until objTextFile.AtEndOfStream
	strNextLine = objTextFile.Readline
	arrServiceList = Split(strNextLine , Chr(34) & "," & Chr(34))
	for i = 0 to Ubound(arrServiceList)
		if (i = 71) then 'This is actually the 72nd column because i started at 0
			strTemp = Replace(arrServiceList(i), "|", "-")
			objTextFile2.Write(strTemp & Chr(34) & "," & Chr(34))
		end if
	next
	objTextFile2.Write(vbCrLf)
loop

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[misscrf]

Thank you for the responses. PHV, that makes sense that the &Chr(34)&","&Chr(34) is concatenating the extra ",". Thank you for pointing that out.

Geates, I have good news and bad news.

The good is that the |'s now replace with a - in that column.

The bad news is that all the columns before that one are wiped out of the file. :-(

Any reason that is happening now?

Thanks! I love progress!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Geates]

yep. The ELSE that I said was pointless is not pointless.
Neither is the if i<= ubound(arrServiceList) (sort of)


Only if there is [red]another "column" is it necessary to add ",".[/red]

	for i = 0 to Ubound(arrServiceList)
		strTemp = arrServiceList(i)
		if (i = 71) then 'This is actually the 72nd column because i started at 0
			strTemp = Replace(strTemp, "|", "-")
		else
			[red]if (i < ubound(arrServiceList)) then strTemp = strTemp & Chr(34) & "," & Chr(34)[/red]
		end if
		objTextFile2.Write(strTemp)
	next

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[misscrf]

Thank you for the reply. I ended up going through this with a colleague who knows his vb and could work through what the code means, so I could learn it.

I know have the script working and now it does a few things (more than what I started with trying to get working). My end goal is to get all multivalue fields to have a pipe for the multivalue delimiter. All of these fields have a semi-colon, except for one. To handle that, this script does a series of find and replaces, in order -
1) Pipes (|) to dash (-) in a column that holds information for "Tags". This column actually is multivalue, but the values themselves have pipes. the multivalue delmiter is a comma and a space.
2) comma space (, ) to pipe (|) in "Tags" column Only. Now that the pipes in the value names are not a pipe, we can make the multivalue delimiter for this column to be pipes, which is the requirement.
3) comma space ( to pipe (|) in all multivalue columns Only. This is for all other multivalue fields other than the "tag" field

Const ForAppending = 8
Const ForReading = 1

strFileName = InputBox("Enter IDX Filename: ")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile (strFileName, ForReading)
intPos = InStr(1,strFileName,".",1)
strFileName = Left(strFileName,(intPos-1))
Set objTextFile2 = objFSO.OpenTextFile (strFileName & "-MultiClean.idx", ForAppending, True)


linenumber = 1

Do Until objTextFile.AtEndOfStream
    strNextLine = objTextFile.Readline
    'define each column on the line as being delimited by ","
    arrServiceList = Split(strNextLine , Chr(34) & "," & Chr(34))
    If linenumber > 0 Then
       For i = 0 to Ubound(arrServiceList)
          If i <= Ubound(arrServiceList) Then	   
          	'70 must be the column number -1 for the Tags column		 		
	   		 		If (i = 70) Then 
    						strTemp = Replace(arrServiceList(i), "|", "-")
 	     					strTemp = Replace(strTemp, ", ", "|")
	     					objTextFile2.Write(strTemp&Chr(34)&","&Chr(34)) 
	     			' each number i= must be the number of a column -1, which is a multivalue field
	     			ElseIf (i = 12) OR (i = 13) OR (i = 14) OR (i = 15) OR (i = 19) OR (i = 53)  OR (i = 56) OR (i = 72)Then
	     					strTemp = Replace(arrServiceList(i), ";", "|")
 	     					objTextFile2.Write(strTemp&Chr(34)&","&Chr(34))
	     			Else
	     					'If last column, don't add a "," as a column delimiter
	     					If (i = Ubound(arrServiceList)) Then
	     						objTextFile2.Write(arrServiceList(i))	     				  						
	     					Else
	     						'Write other columns as are, with delimiter of ","
	     						objTextFile2.Write(arrServiceList(i)&Chr(34)&","&Chr(34))
	     					End If
         		End If     		 
         End If
       Next 
       objTextFile2.Write(vbCrLf)
    End if
linenumber = linenumber + 1
Loop

objTextFile.Close
objTextFile2.Close

MsgBox "Convert multivalue fields script is finished running."

Voila!

I appreciate your help. Your replies pointed me to part of the script and helped me to break it down and attempt to understand it better.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot