Text File (before cleanup script):

I need some assistance with automating a process (e.g., developing a script) which allows to "clean up" a text file.

Please see attached a *sample* text file.   Please note that the actual text file contains completely different data but follows the same structure.

Things that are noteworthy in the text file:
1. Each block consists of six (6) lines.
2. Within each block, the end-of-line (EOL) is marked by a slash ( "\").
3. In some instances, there are 2 blank lines between a block... in other case, there may be only a single blank line between blocks.

In the "before cleanup script", the sample text file contains four (4) blocks.   After the cleanup, the file *should* look like the 2nd part... that is, 4 lines without any "text wrapping".

My question:  How can the cleanup process be automated so that I end up with only four lines (as illustrated in the file)?

Thanks,
EEH
Cleanup-Script.txt
ExpExchHelpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Try this:

    Dim FFi As Integer
    Dim FFo As Integer
    Dim strLine As String
    Dim strWrite As String
    Dim bWritten As Boolean
    
    FFi = FreeFile
    Open "C:\temp\Q_28683165.txt" For Input As #FFi
    FFo = FreeFile
    Open "C:\temp\Q_28683165Out.txt" For Output As #FFo
    
    Do Until EOF(FFi)
        Line Input #FFi, strLine
        If strLine = "" Then
            If Not bWritten Then
                Print #FFo, strWrite
                bWritten = True
                strWrite = ""
            End If
        Else
            bWritten = False
            strLine = Replace(strLine, "\", "")
            strWrite = strWrite & strLine
        End If
    Loop
    
    Close

Open in new window

0
ExpExchHelpAuthor Commented:
Martin:

Thank you for the response... how do I execute it?   In Excel or Word?    

Please advise!   Thank you in advance!
EEH
0
ExpExchHelpAuthor Commented:
Martin:

Ok... I got this figured out (using Excel).    The only thing that doesn't seem to work is that the script creates three (3) blocks versus  four (4) blocks.   Is there a way this can be fixed?  

Thank you in advance,
EEH
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
In any office tool that supports VBA. I tested it in Excel.

Here's how you install an Excel macro. Following the instructions I added a header and footer to the code that I posted above, so you can copy the code from here.

1.    In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)
   
2.    Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu
   
3.    Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)
 
4.    Press Alt+F11 again to go back to Excel
   
5.    Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’.  Then anytime you want to run the macro press Ctrl+Shift+A
Sub Cleanup()

    Dim FFi As Integer
    Dim FFo As Integer
    Dim strLine As String
    Dim strWrite As String
    Dim bWritten As Boolean
    
    FFi = FreeFile
    Open "C:\temp\Q_28683165.txt" For Input As #FFi
    FFo = FreeFile
    Open "C:\temp\Q_28683165Out.txt" For Output As #FFo
    
    Do Until EOF(FFi)
        Line Input #FFi, strLine
        If strLine = "" Then
            If Not bWritten Then
                Print #FFo, strWrite
                bWritten = True
                strWrite = ""
            End If
        Else
            bWritten = False
            strLine = Replace(strLine, "\", "")
            strWrite = strWrite & strLine
        End If
    Loop
    Print #FFo, strWrite
    Close
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
The code above contains a correction at line 28 that fixes the problem you mentioned.
0
ExpExchHelpAuthor Commented:
PERFECT SOLUTION!!!!

Thank you, Martin!
EEH
0
Bill PrewCommented:
Here is a VBscript approach.  Save as a VBS file, and then run like this:

cscript EE28683165.vbs in.txt out.txt

or

cscript EE28683165.vbs in.txt out.txt

In the first case it will read from the first file, and write to the second.  In the second case it will read from the first file, and then overwrite it with the editted version.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
BlankLine = vbCrLf & vbCrLf
 
' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Get output file name from command line parm
If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
Else
   strOutFile = strInFile
End If

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

' Read file into an array
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

' Make desired changes
strData = Replace(strData, "\" & vbCrLf, "")
Do While Instr(strData, BlankLine) > 0
  strData = Replace(strData, BlankLine, vbCrLf)
Loop

' Write output file
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write strData
objFile.Close

Open in new window

~bp
0
ExpExchHelpAuthor Commented:
Perfect solution!
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.