[X]My Assistant

[cnorkowski]

I am trying to read or parse out specific data from a text file. I need it to search for a specific word and copy n paste the data after that into a new text file so I can import in my Table. the key word to search for is "A. NUMBERS" then copy everything after that.

Attached File(s)

 Text_File.txt ( 4.59K )Number of downloads: 16

 

[doctor9]

cnorkowski,

Here's a fairly simple function that takes the path/name of the text file and returns whatever follows your trigger string:

CODE

Public Function GetNumberData(strFilename As String) As String
    Dim TextLine As String
    
    Open strFilename For Input As #1 ' Open file.
'   Loop until we reach the end of the input file.
    Do While Not EOF(1)
'       Read an entire line into a string variable from the input file.
        Line Input #1, TextLine
'       If this line of text contains the trigger...
        If InStr(1, TextLine, "A. NUMBERS") > 0 Then
        
'           Return everything after the trigger.
            GetNumberData = Mid(TextLine, InStr(1, TextLine, "A. NUMBERS ") + 11)
            Exit Do
        End If
    Loop
    Close #1
End Function

Syntax:
? GetNumberData("H:\Text File.txt")
A. B. C. 7L 113,253.92 -1,023.08 112,230.84 7L 14,826,965.4 102,069.29 14,929,034.75 7L 166,936.37 -2,417.78 164,518.59 7L 754,302.07 10,847.90 765,149.97 7L 307,689.73 3,992.87 311,682.60 7L 2,633,626.38 -524,340.20 2,109,286.16 7L 22,724.00 1,065.41 23,789.41 TOTAL 18,825,497.9 -409,805.60 18,415,692.32 9S 30,119,314.0 -61,894.80 30,057,419.20 9S 77,440.00 -2,525.00 74,915.00 9S 379,049.00 35,606.00 414,655.00 9S 206,435.00 0.00 206,435.00 9S 21,867.00 0.00 21,867.00 TOTAL 30,804,105.0 -28,813.80 30,775,291.20 B. SAR A. B. C. 9S 1,030,994.00 -10,396.00 1,020,598.00 TOTAL 1,030,994.00 -10,396.00 1,020,598.00
Hope this helps,
Dennis

[cnorkowski]

Awesome Thanks for the reply Dennis.. I tried to include the code with what I had an I still can't figure it out..
Private Sub GetNumberData()
Dim strFilter As String
Dim strInputFileName As String
Dim db As Database
Dim strSQL As String
Dim rs As Recordset
Dim DeleteCt As Integer
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim ft, fs, t, s, temp, sample
Set fs = CreateObject("Scripting.FileSystemObject")
Set ft = CreateObject("Scripting.FilesystemObject")
Dim TextLine As String
ft.createtextfile "Text File.txt" 'create a temporary file
strFilter = ahtAddFilterItem(strFilter, "TXT Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
FileName = strInputFileName

Set s = fs.getfile(strInputFileName)
Set t = fs.getfile("Text File.txt")

Open strInputFileName For Input As #1 ' Open file.
' Loop until we reach the end of the input file.
Do While Not EOF(1)
' Read an entire line into a string variable from the input file.
Line Input #1, TextLine
' If this line of text contains the trigger...
If InStr(1, TextLine, "A. NUMBERS") > 0 Then

' Return everything after the trigger.
GetNumberData = Mid(TextLine, InStr(1, TextLine, "A. NUMBERS") + 11)
Exit Do
End If
Loop
Close #1
' For x = 1 To 71 'read first 11 lines
' sample.readline
' Next x

Set temp = t.OpenAsTextStream(ForWriting, TristateUseDefault)
Set sample = s.OpenAsTextStream(ForReading, TristateUseDefault)

Do While Not sample.atendofstream 'read remainder of file until end
temp.writeline (sample.readline) 'write a line at a time
Loop

temp.Close
sample.Close
strFilter = ahtAddFilterItem(strFilter, "TXT Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
FileName = strInputFileName

DoCmd.TransferText acImportFixed, "MALS13OFC50 BOR", "OFC50TMP", strInputFileName, False
strInputFileName = Left$([strInputFileName], InStr(1, [strInputFileName], ".txt") - 1)
'******
Set db = CurrentDb
strSQL = "UPDATE [OFC50TMP] SET [OFC50TMP].NameOfFile = '" & strInputFileName & "' WHERE [OFC50TMP].NameOfFile is null;"
db.Execute strSQL, dbFailOnError

Set db = Nothing
End Sub

[doctor9]

cnorkowski,
You seem to have tried to incorporate my function into a larger subroutine. If you don't want to call the function to get the text from the text file, that's fine, but you need to do something with this bit in particular:

CODE

GetNumberData = Mid(TextLine, InStr(1, TextLine, "A. NUMBERS") + 11)

This line of code is meant to return the text that follows "A. NUMBERS" to the bit that calls the function in the first place. It will only work in the context of a function; a subroutine doesn't return a value.
Without some context, it's hard to decipher what you're trying to actually do with all of the data that follows "A. NUMBERS" from the text file. What are you trying to do with it? Write it to another text file? Store it in a table? Do you need to parse out the pieces of data from all of that text?
Hope this helps,
Dennis

[cnorkowski]

Thanks Dennis.. I would like to copy everyting after "A. NUMBERS" into a new text so I can store that piece into a table. Let me know if that makes sense. Thanks again

[doctor9]

cnorkowski,
Okay, assuming that Text File.txt is already existing and already has data in it when you run this code, it should work...

CODE

Private Sub MoveDataToNewFile()
    Dim strRawFilename As String, strNewFile As String
    
    strRawFilename = "H:\Text File.txt"
    strNewFile = "H:\OnlyData.txt"
    
    Open strNewFile For Output As #2
    
    Print #2, GetNumberData(strRawFilename)
    Close #2
End Sub
Public Function GetNumberData(strFilename As String) As String
    Dim TextLine As String
    
    Open strFilename For Input As #1 ' Open file.
'   Loop until we reach the end of the input file.
    Do While Not EOF(1)
'       Read an entire line into a string variable from the input file.
        Line Input #1, TextLine
'       If this line of text contains the trigger...
        If InStr(1, TextLine, "A. NUMBERS") > 0 Then
        
'           Return everything after the trigger.
            GetNumberData = Mid(TextLine, InStr(1, TextLine, "A. NUMBERS ") + 11)
            Exit Do
        End If
    Loop
    Close #1
End Function

Now, I've hard-coded the filenames. You can alter this to let the user browse/create filenames instead, but the basic concept is the same. When the code is done running, the original text file is still the same, and it creates a new file with only the data following "A. NUMBERS".
Hope this helps,
Dennis

[cnorkowski]

Awsome thanks Dennis!! The only thing is the output only has "S" nothing else..

Attached File(s)

 OnlyData.txt ( 3bytes )Number of downloads: 5

 

[doctor9]

Ah, I see a couple of mistakes I made... First, I assumed that all of your text was on a single line (I opened your attachment within my browser, rather than downloading it, which stripped out the carriage returns). Second, I assumed there was only one space between "A." and "NUMBERS" because this forum removes duplicate spaces from posts (unless the text is inside of Code tags).
My bad.
Okay, so since we need to copy multiple lines of text after we find the trigger string, let's do this with just one subroutine - no function.

CODE

Private Sub MoveDataToNewFile()
    Dim TextLine As String, boolFoundFlag As Boolean
    Dim strRawFilename As String, strNewFile As String
    
'   Initialize
    strRawFilename = "H:\Text_File.txt"
    strNewFile = "H:\OnlyData.txt"
    boolFoundFlag = False   'Set this flag to TRUE when we find the trigger string.
    
    Open strRawFilename For Input As #1
    Open strNewFile For Output As #2
    
'   Loop until we reach the end of the input file.
    Do While Not EOF(1)
'       Read an entire line into a string variable from the input file.
        Line Input #1, TextLine
    
'       If we've already found the trigger string, write this line of
'       text to the new textfile.
        If boolFoundFlag = True Then
            Print #2, TextLine
        End If
            
'       If this line of text contains the trigger, set the flag to TRUE.
        If InStr(1, TextLine, "A.  NUMBERS") > 0 Then boolFoundFlag = True
    Loop
    Close #1
    Close #2
End Sub

So, basically this code loops through each line of text in the original file, just like before. If the trigger text ("A. NUMBERS") has been found, we write the current line of text we just read into the new file. Once we reach the end of the original file, we close both the input file and the output file.
If any of this is confusing, let me know and I'll do my best to explain the how/why I did things.
Hope this helps,
Dennis

[cnorkowski]

This is perfect!! Thanks so much Dennis!!