Remove linebreak from text file

January 25, 2010 at 08:17:25
Specs: Windows XP
I have a database the exports query reulsts to a text file. Some data elements may contain line breaks. Currently I copy the text into MS Word and find and replace '^p' with ";" then find and replace ";62D" with "^p62D" (62D starts each record)

Is there a way to automate this or complete the whole thing in a batch


See More: Remove linebreak from text file

Report •


#1
January 25, 2010 at 09:18:22
I'd use VBScript to parse the text file, but if you wanted to put in a little more effort, you could probably write the export in a VBScript, and format the data from there.
Set fso = CreateObject("Scripting.FileSystemObject")
Set outF = fso.OpenTextFile("out.txt", 2, True)
fileText = fso.OpenTextFile("inFile.txt").ReadAll

fileText = Replace(fileText, vbNewLine, ";")
outF.Write Replace(fileText, ";62D", vbNewLine & "62D")


Report •

#2
January 26, 2010 at 03:57:24
Thanks for the response!

This is heading in the right direction. Now I get and Unterminated string constant error. I think this is because my text file can be very long. Maybe handling the file line by line would be better?


Report •

#3
January 26, 2010 at 04:06:02
You might get a batch if you post the file; or a meaningful chunk of it.


=====================================
Helping others achieve escape felicity

M2


Report •

Related Solutions

#4
January 26, 2010 at 05:11:55
Now I get and Unterminated string constant error
Check the in and out file names. You're probably missing a double quote somewhere.The error itself should tell you which line is missing the quotes.

EDIT: I believe VBS' max string size is about 2GB, so that shouldn't be an issue.


Report •

#5
January 26, 2010 at 06:41:03
After some tinkering and some though I got it to work:

Option Explicit

Dim objFSO, objFile, objOut, strTextFile, strData, myPrefix, myIn, myOut
CONST ForReading = 1
CONST ForAppending = 8

'name of the text file
myIn = Inputbox ("Export File Name:")

'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Create output file
Set objFile = objFSO.CreateTextFile("output.txt")
Set objFile = Nothing

'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(myIn, ForReading).ReadAll

myPrefix = inputbox("Program Code:")

strData = Replace(strData, vbCrLf,"; ")
strData = Replace(strData, "; " & myPrefix, vbNewLine & myPrefix)

'Open output file and append lines
Set objOut = objFSO.OpenTextFile("output.txt", ForAppending)

'Write new file
objOut.write (strData)
objOut.close

'Delete and Rename output
objFSO.DeleteFile myIn
objFSO.MoveFile "output.txt" , myIn

'Cleanup
Set objFSO = Nothing
Set objOut = Nothing

Wscript.Echo "Script Complete"


Report •

#6
January 26, 2010 at 06:43:34
I added some variables to make it more universal. but the key was switching to VBScripts, thanks for your help and interest Razor and Mechanix!

Report •

#7
January 26, 2010 at 07:00:40
Impressive. Take a 5 line script, and balloon it up to 40 lines. I kid, I kid. Except for this part:
'Create output file
Set objFile = objFSO.CreateTextFile("output.txt")
Set objFile = Nothing

Why do you open the output file, then close it?

Report •

#8
January 26, 2010 at 08:37:48
Thanks for the feedback. That was left over from another attempt. I have learned my coding style from just doing it, no training in theory or technique (if that’s not apparent from the topic of this discussion in the first place). How would you trim the fat on this script?

Report •

#9
January 26, 2010 at 09:18:43
Assuming this script is only going to run on WinXP, I'd probably change the script around like this:

Option Explicit
Dim objFSO, objOut, strData, myPrefix, myIn
Set objFSO = CreateObject("Scripting.FileSystemObject")

With CreateObject("UserAccounts.CommonDialog")
  .Filter = "All Files|*.*"
  .Flags = 4
  If Not .ShowOpen Then _
    WScript.Quit
  myIn = .FileName
End With
myPrefix = inputbox("Program Code:")

strData = Replace(objFSO.OpenTextFile(myIn, 1).ReadAll, vbCrLf,"; ")
strData = Replace(strData, "; " & myPrefix, vbNewLine & myPrefix)

'Output is destrctive, so make sure the rest of the script ran okay.
Set objOut = objFSO.OpenTextFile(myIn, 2)
objOut.write strData
Wscript.Echo "Script Complete"

If not, I'd have to get rid of the UserAccounts.CommonDialog, so that might complicate things, depending on how much I cared.


Report •

Ask Question