[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vbs replace text2

Posted on 2006-03-20
6
Medium Priority
?
796 Views
Last Modified: 2011-10-03
Hi Experts,
Still in trouble here. See previous post 'vbs replace text'.
I am now having difficulty in getting rid of carriage returns/line feeds. I tried Chr(10) & Chr(13) and vbCRLF and nothing seems to work.
Glad to accept any help.
0
Comment
Question by:bravotango
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16234785
did you try to check if there are indeed carriage returns + line feed in the data?
with a hex editor (I assume a text file) this will be easily spottable...

you might try to replace first chr(10) & chr(13), and then chr(10) and chr(13) individually
0
 

Author Comment

by:bravotango
ID: 16234846
Hi angelIII , yes I did that too and also tried "\r\n" and individually. Maybe it is not possible to find these with vbs.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16234948
can you post the code that you are using, please
it should be possible.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:bravotango
ID: 16235137
OK here it is again. It works fine with most search patterns and with "\^" which was another solution from a nice expert.
I think I can do it another way and not use vbCRLF as a replacement but I would like to know if I can search this text file for line feeds.
Dim pattern
Dim replacement
' We must define ForWriting because the
' imode constants are unknown to VBScript
Const ForWriting = 2
Const inF = "NewSavings.qif"
Const outF = "Testfile1.txt"

' Here are the strings for replacement.
pattern = "LDEBIT"
replacement = vbCRLF

Dim Text
Dim fso                 ' Object variable
Dim oFileIn, oFileOut   ' Text stream
Dim path, fileIn, fileOut

path = GetPath()        ' Retrieve current path to script.
fileIn = path & inF     ' Create filenames.
fileOut = path & outF

Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(fileIn) Then  ' Input file exists?
    WScript.Echo "File '" & fileIn & "' not found"
    WScript.Quit 1
End If

' Input file present; open file and create output file.
Set oFileIn = fso.OpenTextFile(fileIn)   ' Open input file.
Set oFileOut = fso.OpenTextFile(fileOut, _
                    ForWriting, True)    ' Open output file.

Do While Not (oFileIn.atEndOfStream)
    Text = oFileIn.ReadLine              ' Read a line.
    Text = Filter(Text, pattern, replacement)
    oFileOut.WriteLine Text              ' Write text.
Loop

WScript.Echo "Text file: " & fileIn & vbCrLf & _
             "Written into: " & fileOut

Function GetPath
    ' Retrieve the script path.
    Dim path
    path = WScript.ScriptFullName  ' Script name
    GetPath = Left(path, InStrRev(path, "\"))
End Function

Function Filter(txt, expr1, expr2)
    ' Replace expr1 with expr2 in text.
    Dim oReg

    Set oReg = New RegExp        ' Create regular expression.
    oReg.Global = True           ' All matches
    oReg.IgnoreCase = True       ' Make case-insensitive.

    ' Replace all expr1 with expr2.
    oReg.Pattern = expr1          ' Set pattern.
    Filter = oReg.Replace(txt, expr2)
End Function
0
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 2000 total points
ID: 16235325
Your problem is in this line:

    oFileOut.WriteLine Text              ' Write text.

The WriteLine() method writes the value of "Text" to the file followed by vbCrLf.  That is what the "Line" part of "WriteLine" means.

If the files are not huge I would read the whole thing in at once using ReadAll(), do your replacement, and then write the whole thing back out.
0
 

Author Comment

by:bravotango
ID: 16243397
Hi Idle Mind,

That fixed it. I wasn't aware that I could do that. You helped me in more ways than you would think. Now my project is finished I can charge off to the gym feeling much better! Finished script is this:

Const ForWriting = 2
Const inF = "NewSavings.qif"
Const outF = "Testfile1.txt"

Dim part2
Dim fso                 ' Object variable
Dim oFileIn, oFileOut   ' Text stream
Dim path, fileIn, fileOut

path = GetPath()        ' Retrieve current path to script.
fileIn = path & inF     ' Create filenames.
fileOut = path & outF

Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(fileIn) Then  ' Input file exists?
    WScript.Echo "File '" & fileIn & "' not found"
    WScript.Quit 1
End If

' Input file present; open file and create output file.
Set oFileIn = fso.OpenTextFile(fileIn)   ' Open input file.
Set oFileOut = fso.OpenTextFile(fileOut, _
                    ForWriting, True)    ' Open output file.

part2 = oFileIn.ReadAll
            part2 = StrReplace(part2, "!Type:Bank", "")
            part2 = StrReplace(part2, "LDEBIT", "")
            part2 = StrReplace(part2, "LDEP", "")
            part2 = StrReplace(part2, "ABWDL", "")
            part2 = StrReplace(part2, vbCRLF & "D", vbCRLF)
            part2 = StrReplace(part2, vbCRLF & "T", vbCRLF)
            part2 = StrReplace(part2, vbCRLF & "P", vbCRLF)
            part2 = StrReplace(part2, vbCRLF & "\^", vbCRLF)
            part2 = StrReplace(part2, vbCRLF, vbTAB)
            part2 = StrReplace(part2, vbTAB & vbTAB & vbTAB, vbCRLF)
    oFileOut.Write part2

WScript.Echo "Text file: " & fileIn & vbCrLf & _
             "Written into: " & fileOut

Function GetPath
    ' Retrieve the script path.
    Dim path
    path = WScript.ScriptFullName  ' Script name
    GetPath = Left(path, InStrRev(path, "\"))
End Function

Function StrReplace(txt, expr1, expr2)
    ' Replace expr1 with expr2 in text.
    Dim oReg

    Set oReg = New RegExp        ' Create regular expression.
    oReg.Global = True           ' All matches
    oReg.IgnoreCase = True       ' Make case-insensitive.

    ' Replace all expr1 with expr2.
    oReg.Pattern = expr1          ' Set pattern.
    StrReplace = oReg.Replace(txt, expr2)
End Function
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question