Replacing return characters in a line of text

[cluM09]

Hello,

I have a flat file with tab-delimited format. I extracted this file from an application.

For some reason, the file contains some return characters at either the end of the line or within the line.

I want to replace the return characters with a semicolon (, a period (.) character or ther characters.

I have tried the following code, but it did not work. I think the regular expression method may work, but I don't know how to use the regular expression with such an instance.

I like to know how I can replace the return character with another character in the line using VBscript.

strLine = "The that contains the return characters"
strText = Replace(strLine,Chr(13),";")

Any help will be greatly appreciated.

CluM09

 

[mrmovie]

try other things like

vbCr
vbCrLf

etc
etc

i sometimes find copying text into word sometimes helps clarify what you are looking at

 

[Remou]

It is very likely that the line also includes Chr(10):

[tt]strline ="abc" & chr(10) + Chr(13) & "def"
msgbox replace(strline, chr(10) + Chr(13), ";")[/tt]

Or

[tt]strline ="abc" & chr(13) + Chr(10) & "def"
msgbox replace(strline, chr(13) + Chr(10), ";")[/tt]

 

[cluM09]

Remou,

Thank you for the response. I tried your suggestion, but I still cannot replace the character. Below is the text I am referring to.

Line No IP Address
1 "10.87.194.77
10.87.194.75"
2 "10.87.194.76
10.87.194.74"
3 10.87.196.42
4 "10.87.196.41
10.87.196.36
10.87.196.37"

The first field is a line number and the second field (except line 3) is where the return character can be found. Because of the return character(s), the line got pushed to another line. However, the field data is contained within the double quotes.

 

[Remou]

In that case, I suspect that what you have is tab characters (09).

Why not check the characters?

for i=16 to 20 'len(trim(strline))
   strmsg = strmsg & "," & asc(mid(strline,i,1))
next

msgbox mid(strmsg,2)

 

[cluM09]

Remou,

I tried your suggestion, but I got a runtime error: Invalid procedure call or argument: 'asc' instead.

I did this using a function below.

strFile = Text1.txt where Text1.txt conatins:
Line No IP Address
1 "10.87.194.77
10.87.194.75"
2 "10.87.194.76
10.87.194.74"
3 10.87.196.42
4 "10.87.196.41
10.87.196.36
10.87.196.37"

getFileData(strFile)

Private Function geFileData(ByVal strInputFile)
Dim objFSO, inFile, strLine, strmsg
Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set inFile = objFSO.OpenTextFile(strInputFile, ForReading, False)
inFile.SkipLine
If IsObject(inFile) Then
Do Until inFile.AtEndOfStream
strLine = Trim(inFile.ReadLine)
For i = 16 To 20 'len(trim(strline))
strmsg = strmsg & "," & Asc(Mid(strLine,i,1))
Next
Wscript.Echo Mid(strmsg,2)
Loop
End If
Set objFSO = Nothing: Set inFile = Nothing
End Function

CluM09

 

[Remou]

Sorry, it was intended as a rough sketch. Change this:

[tt]For i = 16 To 20 'len(trim(strline))[/tt]

To

[tt]For i = 16 To len(strline)[/tt]

I do not think that reading every line is a good idea. The suggestion was intended as a means of examining the character codes at the end of one of two lines, so they could be used in the Replace function.

 

[PHV]

A starting point:

...
inFile.SkipLine
strOut = ""
bInQuote = False
Do Until inFile.AtEndOfStream
  strLine = Trim(inFile.ReadLine)
  If bInQuote Then
    strOut = strOut & ";" & strLine
  Else
    strOut = strOut & vbCrLf & strLine
  End If
  If InStr(strLine, """") Then
    bInQuote = Not bInQuote
  End If
Loop
MsgBox strOut
...

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

 

[cluM09]

Remou,

It only shows multiple blank line and nothing else. It is really a caret return character since it actually pushed down the cursor.

One thing I have not tried is the regular expression method, but I don't know how to instruct the pattern for it.

Thanks anyway.

CluM09

 

[cluM09]

PHV,

Your code works, but it generate multiple lines that I don't need. The output is shown below:

1 "10.87.194.77

1 "10.87.194.77;10.87.194.75"

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76;10.87.194.74"

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76;10.87.194.74"
3 10.87.196.42

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76;10.87.194.74"
3 10.87.196.42
4 "10.87.196.41

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76;10.87.194.74"
3 10.87.196.42
4 "10.87.196.41;10.87.196.36

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76;10.87.194.74"
3 10.87.196.42
4 "10.87.196.41;10.87.196.36;10.87.196.37"

I only want the lines with the semicolon between the string when there is a caret return character. If there is no caret return character, I want it to show the original line as shown below.

1 "10.87.194.77;10.87.194.75"
2 "10.87.194.76;10.87.194.74"
3 10.87.196.42
4 "10.87.196.41;10.87.196.36;10.87.196.37"

Thanks.

CluM09

 

[PHV]

Seems you didn't copy my suggested code properly ...
Put the MsgBox outside the loop.

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