UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Read Text Data Into Access Table    
 
   
cnorkowski
post Oct 22 2014, 12:31 PM
Post#1



Posts: 5
Joined: 22-October 14



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)
Attached File  Text_File.txt ( 4.59K )Number of downloads: 14
 
Go to the top of the page
 
doctor9
post Oct 22 2014, 12:42 PM
Post#2


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


cnorkowski,
welcome2UA.gif
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
Go to the top of the page
 
cnorkowski
post Oct 22 2014, 03:23 PM
Post#3



Posts: 5
Joined: 22-October 14



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
Go to the top of the page
 
doctor9
post Oct 22 2014, 03:38 PM
Post#4


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


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
Go to the top of the page
 
cnorkowski
post Oct 22 2014, 03:57 PM
Post#5



Posts: 5
Joined: 22-October 14



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
Go to the top of the page
 
doctor9
post Oct 22 2014, 04:28 PM
Post#6


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


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
Go to the top of the page
 
cnorkowski
post Oct 23 2014, 11:51 AM
Post#7



Posts: 5
Joined: 22-October 14



Awsome thanks Dennis!! The only thing is the output only has "S" nothing else..
Attached File(s)
Attached File  OnlyData.txt ( 3bytes )Number of downloads: 2
 
Go to the top of the page
 
doctor9
post Oct 23 2014, 12:39 PM
Post#8


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


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
Go to the top of the page
 
cnorkowski
post Oct 23 2014, 01:01 PM
Post#9



Posts: 5
Joined: 22-October 14



This is perfect!! Thanks so much Dennis!!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:25 PM