VBA text manipulation 2

[HelloWorldGuy]

I have a question in reference to thread705-1379089 I'm trying to format one of the ideas there to what I need.
All I want to do is import the column data from a file once a week. the file has 6 lines of headder that will always be deleted then a repeating three lines that look like:
07/03/12 11:47:15 PAGE 1
UNIT ID ITEM NUMBER LOCATION ONHAND
RESTRUCTURED
every page.

My plan is to have one button that creates the filtered file and then one imports it to the table.I already have the import code. Just getting this new file structured. Thanks ahead of time.

Const gstrQUERYNAME As String = "QUERY NAME*" '1st line of head
Const gstrPagLibrary As String = "LIBRARY NAME*" '2nd line of head
Const gstrFILE As String = "FILE *" '3rd line of head
Const gstrILCMU As String = "ILCMUF04 *" '4th line of head
Const gstrDATE As String = "DATE *" 5th line of head
Const gstrDATENUM As String = "07/03/12 *" '1st line of repeating page head
Const gstrUNIT As String = "UNIT ID *" '2nd line of repeating page head
Const gstrRESTRUCTURED As String = " RESTRUCTURED *" '3rd line of repeating page head

Private Sub cmdExecuteCommand_Click()
On Error GoTo Normalize_File_Error
Dim blnCapture As Boolean
Dim lngFileIn As Long, lngFileOut As Long
Dim strData As String
Dim strBuffer As String

lngFileIn = FreeFile
Open "txtFilePath" For Input As #lngFileIn 'other methods allow user to select input file path
lngFileOut = FreeFile
Open "C:\Filtered.txt" For Output As #lngFileOut
Do
Line Input #lngFileIn, strBuffer
If Trim(strBuffer) = "" Then
'Ignore blank line
ElseIf strBuffer Like gstrQUERYNAME Then
blnCapture = False
ElseIf strBuffer Like gstrPagLibrary Then
blnCapture = False
ElseIf strBuffer Like gstrFILE Then
blnCapture = False
ElseIf strBuffer Like gstrILCMU Then
blnCapture = False
ElseIf strBuffer Like gstrDATE Then
blnCapture = False
ElseIf strBuffer Like gstrPagLibrary Then
blnCapture = False
ElseIf strBuffer Like gstrUNIT Then
blnCapture = False
ElseIf strBuffer Like gstrRESTRUCTURED Then
blnCapture = False
ElseIf blnCapture = True Then
'This is where the data is written to the output file
Print #lngFileOut, strData
End If
Loop Until EOF(lngFileIn)

Normalize_File_Exit:
Reset
Exit Sub

Normalize_File_Error:
'Stop
End Sub

 

[HelloWorldGuy]

Wait, it's all a file format issue! You were right on about the RTF issue! Thank you so much!

 

[Golom]

Just a couple of things (since I had a few minutes). You can pick up the date from the line that starts 'DATE' so you don't have to hard code it in a constant. You can also strip the '07/03/12 11:47:15 PAGE 1' bit off the last line since there doesn't seem to be a line break before it. Try this code.

onst gstrQUERYNAME             As String = "QUERY NAME*"     '1st line of head
Const gstrPagLibrary            As String = "LIBRARY NAME*"  '2nd line of head
Const gstrFILE                  As String = "FILE*"          '3rd line of head
Const gstrILCMU                 As String = "ILCMUF04*"      '4th line of head
Const gstrDATE                  As String = "DATE*"          '5th line of head
Const gstrTIME                  As String = "TIME*"          '6th line of head"
Dim gstrDATENUM                 As String                    '1st line of repeating page head
Dim gstrTIMENUM                 As String                    '1st line of repeating page head
Const gstrUNIT                  As String = "UNIT ID*"       '2nd line of repeating page head
Const gstrRESTRUCTURED          As String = "RESTRUCTURED*"  '3rd line of repeating page head

Dim lngFileIn                   As Long                      'Input File Handle
Dim lngFileOut                  As Long                      'Output File Handle
Dim strBuffer                   As String                    'Read String Buffer
Dim InputFile                   As String                    'Input File Name
Dim OutPutFile                  As String                    'Output File Name

On Error GoTo Normalize_File_Error

[red]' Define Source and Destination Files here[/red]
InputFile = ".\LineInput.txt"
OutPutFile = ".\LineOutput.txt"

'Open the files
lngFileIn = FreeFile
Open InputFile For Input As #lngFileIn

lngFileOut = FreeFile
Open OutPutFile For Output As #lngFileOut

Do
    Line Input #lngFileIn, strBuffer

    ' Pick up the date from the line that starts with DATE.
    If gstrDATENUM = "" Then
        If strBuffer Like gstrDATE Then
            gstrDATENUM = VBA.Right$(VBA.Trim$(strBuffer), 8)
        End If
    End If

    ' Pick up the time from the line that starts with TIME.
    If gstrTIMENUM = "" Then
        If strBuffer Like gstrTIME Then
            gstrTIMENUM = VBA.Right$(VBA.Trim$(strBuffer), 8)
        End If
    End If

    ' Test for lines that we don't want to include
    If VBA.Trim$(strBuffer) = "" _
       Or strBuffer Like gstrQUERYNAME _
       Or strBuffer Like gstrPagLibrary _
       Or strBuffer Like gstrFILE _
       Or strBuffer Like gstrILCMU _
       Or strBuffer Like gstrDATE _
       Or strBuffer Like gstrTIME _
       Or strBuffer Like gstrUNIT _
       Or strBuffer Like gstrDATENUM & "*" _
       Or strBuffer Like gstrRESTRUCTURED Then
        ' Do Nothing
        
    Else

        ' If the Date Time Header is appended to the line then strip it.
        If InStr(1, strBuffer, gstrDATENUM & " " & gstrTIMENUM) > 1 Then
            strBuffer = Left$(strBuffer, InStr(1, strBuffer, gstrDATENUM) - 1)
        End If
        
        'Write the data to the output file
        Print #lngFileOut, strBuffer

    End If
Loop Until EOF(lngFileIn)

Normal_Exit:
Close #lngFileIn
Close #lngFileOut

Exit Sub

Normalize_File_Error:
MsgBox Err.Number & " - " & Err.Description
GoTo Normal_Exit:

My test system has some unusual things going on so I have to use VBA.Right$ and VBA.Trim$ instead of just Right$ and Trim$. You probably don't need to do that.

 

[Golom]

Sorry. That first line should start with "Const" ... not "onst

 

[strongm]

Or we could use a regular expression solution...

[blue]    [green]' requires that project has references to
    ' Microsoft Scripting Runtime
    ' Microsoft VBScript Regular Expressions 5.5[/green]
    
    Dim inputfile As String
    Dim outputfile As String
    Dim fso As FileSystemObject
    
    inputfile = ".\LineInput.txt"
    outputfile = ".\LineOutput.txt"
    Set fso = New FileSystemObject
    
    With New RegExp
        .Global = True
        .MultiLine = True
        .Pattern = "[\s|\S]*?RESTRUCTURED\r\n([\s|\S]*?)\d{2}/\d{2}/\d{2}.*"
        fso.CreateTextFile(outputfile, True).Write .Replace(fso.OpenTextFile(inputfile, ForReading).ReadAll, "$1") '.Replace(Text1, "$1")
    End With[/blue]

 

[HelloWorldGuy]

So this thread has proven to be more than helpful. You guys are great! I don't in any way need this for functionality but I'm just currious, what would be a good way to eliminate in line strings? Like, the "L" that repeats throught the record. Also, Golom that's an awesome answer to the date issue. I changed the hard-coded date to "##/##/##" and it seems to be functional, but I could be wrong when I stress test this on some other data. Do you think that will be a sufficient solution?

Also, I just wanted to say it's really nice to have joined this community! Thanks again!

-Guy

 

[Golom]

Yes. ##/##/## should work ... assuming that a string matching that pattern would never occur in your data.

I didn't know that the "L" was spurious and should be eliminated. You can

Print #lngFileOut, Replace(strBuffer, "L ", "")

and that will get rid of it.

 

[HelloWorldGuy]

Amazing! Thank you!

 

[Golom]

Maybe we should make that

Print #lngFileOut, Replace(strBuffer, " L ", " ")

Just in case some legitimate data ends with an "L