Find and paste from text file

**guerillaexcel** · 02-10-2011, 11:34 AM

Hi,
I previously posted this on the Excel Programming board but I haven't been able to get it completed over there, so a colleague recommended that I try and use Access to complete my query using the wonders of recordsets.

Here is my original thread

Basically, from the attached .txt file I am looking for 4 pieces of data to be pulled into a table.
After the 1st 4 pieces have been pulled in then I'd like to move to the next occurance of the data and then extract this and keep doing this until
we reach the end of the file.
The 4 pieces of information are:

Transaction Type : And the information after it on the same line up to 60 chars
The very next line with the name on it e.g. Mr Billy Murphy
The next again line, but just the first 60 characters
This last line with Batch on it along with the number.

Then move to the next set of data and extract that.

I'd like each piece of info to be the table with all the transaction types in one column, the names in the next column etc.
Is this doable? Can anyone help and if so do you need any more info from me?

Many thanks.

**alansidman** · 02-10-2011, 02:22 PM

You will need to clean up your text file in order for Access to do anything with it. It will need to resemble some sort of logical table. Access will not have any way of handling your request with the text file in the current format. To many headers (non-data information) in the file that needs to be removed and the data is not arranged in any table friendly format that will allow Access to work on it.

A suggestion may be to ask if the text file you are receiving can be re-formatted to a more user friendly (for analysis) format can be achieved. Is text the only available output of this report?

Alan

**guerillaexcel** · 02-11-2011, 06:43 AM

Hi Alan,

Thanks for your reply, I thought that as much but unfortunately this is the only format the report is available in. I have stripped away much of the company info but everything that I require is still on the report.
Is it not possible to import the report to Access, then using a script search for the first term ie "Transaction Type" and then return the 4 lines?

**alansidman** · 02-11-2011, 10:13 AM

Because Access is table driven, I don't see how you can do this. The columns in a table represent fields -- how would you distinguish between labels and data you import if they lie in the same "column?" Each row in a table represents a record. How would you account for the term transaction in the record? I'm thinking that you have a spreadsheet mindset here and not a relational database mindset.

This link might help you get a better understanding.

http://forums.aspfree.com/microsoft-...el-349267.html

Alan

**guerillaexcel** · 02-15-2011, 03:37 AM

solved it, not the cleanest method but it works for me. Basically imported my text file, then moved to each recordset, looked for a specific term and then returned the next 4 lines into a different text file, separated them by using a ";" and then can easily import to Access table or spreadsheet.
Cheers

Sub CCP3()

    ' define variables
    Dim rst As Recordset
    Dim cgroup As String
    Dim cname As String
    Dim cclient As String
    Dim acc As String
    Dim acc2 As String
    Dim bat As String
      
          
    Close #2 ' just in case its open
    
    ' delete table and re-import for the freshest version
     On Error Resume Next
    
    DoCmd.DeleteObject acTable, "cp"
    DoCmd.TransferText acImportDelim, "cp Import Specification", "cp", "C:\example.txt", False
    
    ' open file for output
    Open "C:\Output.txt" For Output As #2
   
    ' open recordset for input
    Set rst = CurrentDb.OpenRecordset("cp")
    rst.MoveFirst
    
    Do While Not rst.EOF
    
       ' If rst.Fields(0) Like "*Trans*" Then GoTo Here
        ' find the group name and group name
        If rst.Fields(0) Like "*B00A001*" Then
        rst.MoveNext
        recordheader rst
        rst.MoveNext
        recordheader rst
        rst.MoveNext
        recordheader rst
        rst.MoveNext
        recordheader rst
        rst.MoveNext
        recordheader rst
                   'cgroup = RST.Fields(0)
Here:
            cgroup = Trim(Left$(Right$(rst.Fields(0), Len(rst.Fields(0)) - 19), 30))
        
        rst.MoveNext
           recordheader rst
           cname = rst.Fields(0)
        rst.MoveNext
        recordheader rst
            'acc = RST.Fields(0)
            acc = Left(rst.Fields(0), 50)
            acc2 = Trim(Left$(Right$(rst.Fields(0), Len(rst.Fields(0)) - 50), 14))
        rst.MoveNext
        recordheader rst
            'bat = RST.Fields(0)
            bat = Right(rst.Fields(0), 5)
        ' find the client numbers

            Print #2, bat & ";" & cgroup & ";" & cname & ";" & acc & ";" & acc2 & ";"
 
           ' rst.MoveNext
           End If

        rst.MoveNext
    
    Loop

    Close #1   ' Close files
    Close #2
      
End Sub


Function recordheader(rst As Recordset)
    
    
If rst.Fields(0) Like "*AUDIT*" Then
 rst.MoveNext
        rst.MoveNext
        rst.MoveNext
        rst.MoveNext
        rst.MoveNext
        rst.MoveNext
        End If
        
End Function