Extracting Relevant Data from a Block of Text Containing some Junk

[ravnwing]

Hello.
For my job I get in txt files that look fairly well like gibberish if you don't know what you are looking for (and honestly a bit like gibberish even if you do.) My problem is that in the text files there are long repetitions of junk with relevant data in them. For example:

z260006U62425L                            U62425L                               
z22000000000060000N# 000000000137000000000000137000000000006PzS000006000        
z24JpPpN 6 JvWvL(S) QUpRTZ pNpL#5,z97Np xRpSS 5#Ly PLpTvy zpSv,z97Np STp7       
z24NLvSS STvvL S#L7y 5#Ly PLpTvy STRpP                                          
z24xy 0006 6 7601666060 0000006N#  00000000702 7901009033 00000000000 RP        
z24xy 0006 1 7601666010 0000006N#  00000006055 7901009033 00000000000 zN        
z24xy 0006 2 7601666020 0000006N#  00000000394 7901009033 00000000000 zN        
z24xy 0006 5 7601666050 0000006N#  00000000014 7901009033 00000000000 RP 

And then patterns like this repeats 80 times or so, depending on the order it comes from. But in each of those blocks there is relevant information that I would like to keep
What I would like to do is set it up so that a program or macro sifts through the blocks of text and puts the relevant information into the appropriate fields in a table in Microsoft Access. I know what the information is, and where it is, line and character. I just need a way to tell the program to ignore this set number of characters, copy these ones, then jump to the next line and continue. Does anyone have any idea how this can be done?

Please get back to me asap. Thank you so much.

[Seth Schrock]

It looks like you might be able to import the data from your text file and use the space as the field delimiter for those last lines, but as you said it all looks like gibberish, so I can't be sure. What exactly are you wanting it to look like once it has been processed?

[ravnwing]

okay, so this is an image of the block of text with the fields I want to save for the access table highlighted. You are right about the spaces for the most part, but the second line is where that doesn't really work. Any ideas?

[zmbd]

1) You're going to have to explain just exactly what/where the data is coming from.

2) in this mess:

z260006U62425L U62425L 
z22000000000060000N# 000000000137000000000000137000000000006PzS00000600 0 
z24JpPpN 6 JvWvL(S) QUpRTZ pNpL#5,z97Np xRpSS 5#Ly PLpTvy zpSv,z97Np STp7 
z24NLvSS STvvL S#L7y 5#Ly PLpTvy STRpP
<<snip>>

Just what exactly it is that you're after

3) Most of the Experts here work in secure environments. Thus, the third party storage sites are blocked. Furthermore, using third part site will invalidate the thread should it ever go down, or you decide you need the space in the account (^_^) please use the [advanced] posting option

>HOWEVER<
Please keep in mind, images/files are rarely needed if the question is well explained to begin with....

[Seth Schrock]

It sounds like you are going to need to read the file line by line into Access (See VBA Standard Text File I/O Statements) and then you will just have to know which line you are on (using a loop most likely) and then you can use a combination of the Left(), Mid(), and Right() functions to get what you need.

[NeoPa]

Without a clearly-stated set of rules that can be used to determine what is junk and what is relevant data this question cannot be answered sensibly. There are various techniques that can be used in different circumstances. Without knowing the circumstances then anything we suggest is as likely to be misleading as it is to be helpful.

Remember, whatever is designed into the program won't have someone there making an intelligent choice based on what you understand. Your understanding will have to be incorporated into the code. To help with that we have to have that understanding specified at the very least.

[ravnwing]

I'm sorry, I didnt know how to do the advanced options. This is the first time I am using this site, as well as just beginning to teach myself access and vba.

Most lines in this has some relevant information. The first line, for instance, I need characters 7-14. On the second I need 4-13. I think what Seth had to say in terms of using the left, right, and mid functions was a good one to pick out the relevant data. My question then becomes how do I take the data that I have picked out and put into variables in vba and move them into named fields in an access table?

going with what I said before, the first set of numbers (ln1, chr 7-14) is an id number, and the second set of numbers (ln2, chr 4-13) is a quantity. Provided I can use a macro to start a new record, how would I then use visual basic to move those values, ID and quantity, into their appropriate fields?

[Seth Schrock]

In your OP, you mention that the pattern repeats about 80 times. Is the pattern the whole thing or just lines 5 through 8? If the whole thing, is it always an 8 line pattern?

[ravnwing]

The pattern is the whole thing, not just the repeated lines. And unfortunately it is not always an 8 line pattern, occasionally there is no line 4. But the relevant data that starts in line 5 always has "z24xy", so once I hit line line 4 I can check if the line begins with z24xy, and if it does not then just move to the next line and begin collecting the data from there. Lines 3, and in this case 4, have no relevant information.

[zmbd]

As Seth said, follow the link to the IO information
You'll need to read in each line
From your very small dataset
I'd use the split() function to parse the string into an array based on spaces.
Then check each cell for the required data or starting string.
Depending how many records you'r dealing with would depend on if I would use an sql-execute method or open a recordset to push the data to table.

[ravnwing]

The problem is that I have no idea what I'm doing. I have spent, literally, the last 6 hours, and at least that much time yesterday trying to teach myself how to do what I need to do and I'm still totally lost.

The I/O information is not really built well to be a beginners understanding of VBA, I think its more meant to teach people who already know how to use VBA well how to do something new. The FreeFile information has no context to me at all, the open command makes sense to me, but then the input line code has a command and two parameters, but it needs three: a file string being accessed, a line being retrieved and a place to put the line. And then when I am parsing out the information that I need from the line that I have retrieved, I would have to understand how the mid knows what line to pull strings from. The fields that I need are these:

Field 1: ln1, mid(8, 7)
Field 2: ln2, mid(4, 11)
Field 3: ln2, mid(22, 13)
Field 4: ln2, mid(35, 15)
Field 5: ln5, mid(14, 8)
Field 6: ln5, mid(36, 11)
Field 7: ln6, mid(36, 11)
Field 8: ln7, mid(36, 11)
Field 9: ln8, mid(36, 11)

And what is most important is that I need to understand, once I have all of these into separate variables or more likely an array of variables, how do I use VBA WITHIN ACCESS to put all of these fields into a record and then start a new record so that I can begin again. I have some experience with other languages, so once I understand the logic behind doing that much I can write the code to put it into a loop to put in all the data until the file is done. I just dont understand how to work the code in this setting; using vba within access.

I hope that makes at least some more sense.

[zmbd]

The I/O information is not really built well to be a beginners understanding of VBA, I think its more meant to teach people who already know how to use VBA well how to do something new.

Agreed, it's not intended to be a tutorial in the basics of VBA script writing. Instead, it covers an often overlooked set of tools native to the VBA/BASIC language.

So let's throw a little context for you:

I'll assume that you know some basics (punny (^o^) ) about the editor and so forth; however, I know by default certain options are not set in Access so if you will read thru [*]> Before Posting (VBA or SQL) Code
>> Now I'm not after the forum rules here, what I'm after are the Options and the basic trouble shooting steps for later<<
Keep this link handy for when we do the debug\compile stuff.

So you have a text file that we need to open, read, and parse
Using the IO
We need to find first free pointer that we can assign our file to so we use the function FreeFile():
intPointer = FreeFile

More than likely we could simply use #1 however best practice is to use freefile to return the next available pointer.

So we have this number "intPointer" and we're going to assign the path of the text file that we want to parse to this value, and in this case it doesn't sound like we need to alter the file so I'll just open it to for reading (Output):
Open "C:\data\in.txt" For Output As intPointer
See that AS and intPointer, so for now on when using the IO functions we're coing to use "intPointer," just like we use "Me." for forms, to refer to the file located at "C:\data\in.txt"

So great, we have the file open (we hope and should test for but not right now - indeed, we should have checked to see if the file was available before trying to open it).

Now the file is open as i lets upull the data, taking from yhour posted data OP.
Line Input #intPointer, strReadLineIn
therefor:
strReadLineIn= "z260006U62425L U62425L "
Now here's where things get funn... what to do with this...
I use the Split() or midstring to parse the infromation and then you will need to save it to a table.

Most likely you'll do this in a loop until you reach the endoffill (EOF)

You now need to close the file, a Close intPointer

Write your intial code to at least open and read the file.
debug.print strReadLineIN will print the line to the immediate window (press <ctrl><g> to open) so you can see how your file is being opened and parsed. Anyway, debug and run your code... let us know how things progress and we'll take the next step.

If you are truely stuck, even after he basic debugging, then post your work as described in the link above (and here)> Before Posting (VBA or SQL) Code and we'll tweek from there.

[ravnwing]

I'm not sure what isnt working, but it certainly doesnt like something. When I run it this way, it doesnt like the double "line input" that was recommended. The debugger tells me that it was expecting a #, so I guess it thought that the second line input was supposed to be the intpointer, but then when I take it away it gives me runtime error 54, the help of which is perfectly unhelpful.

I dont know if it matters, but as I said before, I'm running the VBA out of access. Any recommendations?

Private Sub text_reader()
 
    Dim strLineIn
 
    intPointer = FreeFile
 
    Open "H:\test.txt" For Output As intPointer
 
    Line Input Line Input #intPointer, strLineIn, strLineIn
 
    Close intPointer
 
    Debug.Print strLineIn
 
 
 
End Sub
 
 

[ravnwing]

and yes, I know that I didnt go on to parse the line, but I thought I would test that it could read the file first.

[zmbd]

Sorry, I had a typo in that post.

Try the following:

Private Sub text_reader()
    Dim intPointer As Integer
    Dim strLineIn As String
'
'Find the first free pointer
    intPointer = FreeFile
'
'open the text file for read only
 
    Open "H:\test.txt" For Input Access Read As intPointer
'
'While not the end of the file, read the line and
'and print in the debug window
    Do While Not EOF(intPointer)
        Line Input #intPointer, strLineIn
        Debug.Print strLineIn
    Loop
'
'close the file
    Close intPointer
End Sub

Note the difference in the variable casting... as a specific type of data

[ravnwing]

The compiler isnt accepting eof as an argument for the do while not statement in line 13. But even when I remove that part of the code, lines 13 and 16, its still giving me an error 54 on line 14.
"Statements used in manipulating file contents must be appropriate to the mode in which the file was opened."
then it goes on to list a number of reasons that typically cause that error code. Any idea why this is not working?
Does it have anything to do with the fact that I am not running this as a form application and am running it out of access? Or should that not matter?

[zmbd]

EOF(intPointer)

Allways have to tell the function which file you're looking at... funny, I cut and pasted that right out of one of my working files...

[ravnwing]

okay, there are no longer any errors, which is good, but its not appearing to do anything. If I were running a program in C++, which is what I am most used to, I would say that I forgot to include the cout statements, and that it simply wasn't printing the results. Am I right? or is there something wrong with the code itself?

[zmbd]

OK, Altered the code in Post#15

Fixed the typos, sorry about that... trying to write from memory without a complier (^_^)
However, today, I have the compiler handy.
I've ran this code and it works/
Altered line 10 open statement to ensure that your original data file is not altered (which btw, you should double check your test file contents )

Let me know.

[ravnwing]

oh! it works now! I see what you changed, why did that make it work now when it didn't before? Was the program overwriting the text file before it began?

[ravnwing]

Okay, this is wonderful. I have written the code to pull the relevant data from the different lines of the text, and so far it is working flawlessly.

Private Sub text_reader()
    Dim intPointer As Integer
    Dim strLineIn As String
    Dim impText As String
    Dim testText As String
    Dim impNum As Long
    Dim impCash As Currency
 
'
'Find the first free pointer
    intPointer = FreeFile
'
'open the text file for read only
 
    Open "H:\test.txt" For Input Access Read As intPointer
'
 
        ' Pull the first field
        Line Input #intPointer, strLineIn
        impText = Mid(strLineIn, 8, 7)
        Debug.Print impText
 
        'Pull the second field
        Line Input #intPointer, strLineIn
        impNum = Mid(strLineIn, 10, 5)
        Debug.Print impNum
 
        'Pull the third field
        impCash = Val(Mid(strLineIn, 30, 5)) / 100
        Debug.Print impCash
 
        'Pull the fourth field
        impCash = Val(Mid(strLineIn, 43, 7)) / 100
        Debug.Print impCash
 
        'Ignore a line with no relevant data
        Line Input #intPointer, strLineIn
 
        'Check to see if next line is relevant
        Line Input #intPointer, strLineIn
        testText = Left(strLineIn, 5)
 
        'If not then skip to next line
        If testText <> "z24xy" Then
            Line Input #intPointer, strLineIn
        End If
 
        'Pull and format the fifth field
        impText = Mid(strLineIn, 14, 4) & "." & Mid(strLineIn, 18, 2) & "." & Mid(strLineIn, 20, 2)
        Debug.Print impText
 
        'Pull the sixth field
        impCash = Val(Mid(strLineIn, 40, 7)) / 100
        Debug.Print impCash
 
        'Pull the seventh field
        Line Input #intPointer, strLineIn
        impCash = Val(Mid(strLineIn, 40, 7)) / 100
        Debug.Print impCash
 
        'Pull the eigth field
        Line Input #intPointer, strLineIn
        impCash = Val(Mid(strLineIn, 40, 7)) / 100
        Debug.Print impCash
 
        'Pull the ninth and final field
        Line Input #intPointer, strLineIn
        impCash = Val(Mid(strLineIn, 40, 7)) / 100
        Debug.Print impCash
 
 
'
'close the file
    Close intPointer
End Sub
 

Next I will have to write a loop that checks to see when the section ends (not the entire text file is formatted the same way, but that's something I can work out on my own.) The only thing I need to know now is how to move the fields I pulled out and rather than writing them to the Immediate screen, post them on my table.

So that means I need to understand how to refer to my table in access, create a new record, and then write from a variable to a field. Is there a page that has that information? or are they just simple commands that you can write out here?

Thanks so much for the help so far.

[Seth Schrock]

If you are simply writing new records, then it is really simple.

Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Table_Name", dbOpenDynaset)
 
With rst
   .AddNew
   !Field1 = impText
   !Field2 = impNum
   !Field3 = impCash
   '...
   .Update
   .Close
End With
 
Set db = Nothing
Set rst = Nothing

Just replace Table_Name with the name of your table and Field1, Field2 and Field3 with the names of your fields. Just keep going on with your fields, assigning the variable values to them and it will insert the values into your table with .Update command.

[zmbd]

The Record Set Method that Seth suggests would be the way I would go if there are several records to be created (and that appears to be true).

There is the execute method which will also work, and the only reason I mention it is that you might find information pertaining to it so a very simple reason for using one over the other: for a few new records, simple updates, or deletions the execute method is quite useful and avoids the nasty "you will do This for That many records" popup; however, it's not the most effecent with system resources should you have anything overly complex or need to add a lot of records.

[ravnwing]

Seth's method works fabulously for adding the data into the table. This is all working wonderfully. the only important thing that I have left to ask is this. a second part of this list is adding in more data to records that have already been created. I have figured out how to navigate from record to record fairly well, and set up a loop to get me to record that I want.

Private Sub finder()
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Dim test As String
Dim found As Boolean
found = False
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Table", dbOpenDynaset)
 
rst.MoveFirst
 
Do While found <> True
 
With rst
   If test = ![ID Number] Then
   found = True
   Debug.Print ![ID Number]
   Else
   .MoveNext
   End If
 
End With
 
 Loop
 
Set db = Nothing
Set rst = Nothing
 
End Sub
 

My problem is that once I get to the record I want, I don't know how to edit the record in a way that the program will accept. Once I have this done all that will be left to do will be to try and figure out a file selector to pick the input text and you gentlemen (and/or Ladies?) will have made my month. Can you help me?

[Seth Schrock]

To edit a record, you use the .Edit method instead of the .AddNew method. So line 8 of post 22 would be replaced with .Edit once you are on the record that you want to edit.

You mention that you are finding the record that you want changed by looping through the recordset. You might look into the .FindFirst method (Recordset.FindFirst Method).

[ravnwing]

The theory sounds great, but when I tried to use it, the code executed but it didnt seem to run the way I thought it should, which probably just means I understand it only out of context. I used the code as follows, but it only spat back to me the first ID number in the table.
any idea what I am doing wrong?

Private Sub finder()
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Dim test As String
Dim found As Boolean
Dim num As String
test = "U0181G5"
found = False
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Table", dbOpenDynaset)
 
rst.MoveFirst
 With rst
rst.FindFirst (test = ![ID Number])
num = ![ID Number]
Debug.Print num
End With
 
Set db = Nothing
Set rst = Nothing
 
End Sub

[Seth Schrock]

You need to switch the order of your criteria in the rst.FindFirst statement.

rst.FindFirst "[ID Number] = " & test

[ravnwing]

Thank you for all the help. I didnt get a chance to implement that when I was working on my project today, I will be getting back to it monday. I will check in on how it is working then.
Thank you again for all your help.

[ravnwing]

Hello again. I've tried this in a bunch of different variations, but I don't seem to understand how the findfirst function is supposed to work. It doesn't like having the test variable inputted as a parameter. This is how I have it arranged now

Option Compare Database
 
Private Sub finder()
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Dim test As String
Dim found As Boolean
Dim num As String
test = "U0181G5"
found = False
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Table", dbOpenDynaset)
 
rst.MoveFirst
    With rst
    .FindFirst ("[ID Number] = " & test)
    num = ![Quantity]
    Debug.Print num
End With
 
Set db = Nothing
Set rst = Nothing
 
End Sub

and then I get error 3070: that it doesnt understand U181G5 as a valid field or expression.
Any ideas?

[zmbd]

This thread is starting to branch too much.
Post 29:
Look at line 11, you have a string.
Look at line 19, you have to place a string in quotes.