How to replace if a character is repeated more than a threshold value with a null or "" in vbs?

Question

Const ForReading = 1
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
strSource = "C:\Users\Desktop\test.csv"
Set File = fso.OpenTextFile(strSource, ForReading)
strText = File.ReadAll
File.Close
strNewText = Replace(strText,",","|")
Set File = fso.OpenTextFile(strSource, ForWriting, True)
File.WriteLine strNewText
File.Close

This is the code Im using and the result I get is: xxxx|yyyy|zzzzz||||||||||||||||||||| The pipe character '|' must be replaced with 'null' or '' if its repeated more than five times and I have tried using trim, replace and mid functions but couldn't get the solution. Thanks

Just to understand your maybe underlying requirement: do you expect a fixed number of pipes in all lines? Or will the replacement you are looking for potentially yield lines with more and with less pipes? — trincot, Jan 18 '17 at 16:35
every line will have pipes after each string but it shouldnt extend for more than five times — Karthik Mike, Jan 19 '17 at 06:21

score 0 · Accepted Answer · answered Jan 18 '17 at 16:36

0

You're trying to limit the "|" to 5 consecutive entries? There may be a prettier way, but this would work

Do While InStr(1, strNewText, "||||||") > 0
    strNewText = Replace(strNewText, "||||||", "|||||")
Loop

answered Jan 18 '17 at 16:36

Zerk

1,595
9
16

Thanks for the reply but im trying to remove the pipes and replace with null if it exceeds five times – Karthik Mike Jan 19 '17 at 06:29
Do While InStr(1, strNewText, "|||||") > 0 strNewText = Replace(strNewText, "|||||","") Loop – Karthik Mike Jan 19 '17 at 06:36
This will replace 6 pipes by 5 even when they occur in the middle of a line, not at the end. – trincot Jan 19 '17 at 08:18

score -1 · Answer 2 · edited May 23 '17 at 11:52

You could use the following regular expression:

\|{5,}

as follows:

Const ForReading = 1
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
path = "C:\Users\Desktop\test.csv"
Set file = fso.OpenTextFile(path, ForReading)
strText = file.ReadAll
file.Close

Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\|{5,}"
strNewText = re.Replace(strText, "")

Set file = fso.OpenTextFile(strSource, ForWriting, True)
file.WriteLine strNewText
file.Close

NB. Consider using Option Explicit at the beginning of your script; it will save you from misspelled and confused variables.

How to replace if a character is repeated more than a threshold value with a null or "" in vbs?

2 Answers2