extracting data from unformatted .txt (1 Viewer)

[chewy]

this is a real noodle scratcher. I just got a project that need to extract just the address from this file

TAX ID # .........NAME/ADDRESS......... TYPE ACCOUNT # ...............DESCRIPTION.............. AMOUNT
043-82-5168 MATTHEW J SCOTT 1099C 1011018001 DESCRIPTION: 1099-C Information
306 EAGLE AVE APT A DEBT CANCELLED: 611.24
PHILADELHIA NY 13673 DATE CANCELLED: 09/23/02
NAME PAYEE: SCOT DEBT WAS DISCHARGED IN BANKRUPTCY: NO
REPORTING CODE: B

------------------------------------------------------------------------------------------------------------------------------------
TAX ID # .........NAME/ADDRESS......... TYPE ACCOUNT # ...............DESCRIPTION.............. AMOUNT
048-78-9819 JEFFREY W MOORE 1099C 1011161001 DESCRIPTION: 1099-C Information
PO BOX 11 DEBT CANCELLED: 8,375.72
FINE NY 13639 DATE CANCELLED: 03/22/02
NAME PAYEE: MOOR DEBT WAS DISCHARGED IN BANKRUPTCY: NO
REPORTING CODE: B

------------------------------------------------------------------------------------------------------------------------------------


hopefully that doesnt look like crap as in this box. Anyway I need it just extractthe name and address from htis mess. Is this possible or am I out of luck., I really need to figure a way to do this! IF anyone has any ideas I would greatly appreciate them!

 

[chewy]

here is the file

 

[WayneRyan]

chewy,

Just a rough draft, I'm outta here ...


Dim dbs As CurrentDb
Dim rst As RecordSet
Dim buf As String
Dim Name As String
Dim Address As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("YourTable")

Open "C:\5021.txt" For input As #1

While Not EOF(1)
Line Input #1, buf
If Instr(0, buf, "TAX ID #") > 0 Then
Line Input #1, buf
Name = Mid(buf, 13, 30) ' Could use trim ...
Line Input #1, buf
Address = Mid(buf, 13, 30) & vbCrLf
Line Input #1, buf
Address = Address & Mid(buf, 13, 30)
rst.Addnew
rst!Name = Name
rst!Address = Address
rst.Update
End If
Wend

Close #1
Set rst = Nothing
Set dbs = Nothing

hth,
Wayne

 

[chewy]

this is what I got just using excell to cut it out. Any more help on just the name and address?

 

[chewy]

im really stuck now. This is what I have is in the attachment. I need to take this text and have it set up in a mail merge. Has sanyone done this before who would be willing to give me some advice?

 

[chewy]

sorry here is the attachment

 

[neileg]

The problem is a lack of structure. In the absence of fixed string lengths, you need some kind of predictable indicator for the beginning and end of the fields. In your case the names may be two, three or more strings so counting the spaces doesn't do it. Similar considerations apply to the address.

It's obvious that this is an ASCII stream intended to produce a formatted report on a fixed width printer. It may be that you can chop the stream into fixed widths, and the structure of the data becomes more obvious.

Are you sure this is the only way you can get this data?

 

[chewy]

this is coming off of a mainframe, and im pretty much stuck but I am not sure how to implement the string manipulation

 

[neileg]

this is coming off of a mainframe, and im pretty much stuck but I am not sure how to implement the string manipulation

Yeah, that's the point.

The structure that the printed report would have is being lost somehow and this means that the text is not just unformatted but also unstructured.

We use a product called Monarch to capture mainframe reports. It works a bit like the text import in Excel, except it recognises headers and footers, too.

 

[chewy]

there must be a way to be able to use the line break as a delimiter and then extract three lines and then search for text again and then start the three line extraction until end of file. Any ideas?

 

[WayneRyan]

chewy,

What is the desired final result in VBA terms?

Dim strName As String
Dim strAddr1 As String
Dim strAddr2 As String

Or

One string with CR/LF delimiters?

Wayne

 

[chewy]

i actually did it in excell. But am still curious as to the result. Iwanted one string with delimiter

 

[WayneRyan]

chewy,

I didn't actually run this, but it's the general idea ...

' **************************************
Dim dbs As CurrentDb
Dim rst As RecordSet
Dim buf As String
Dim Name As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("YourTable")

Open "C:\Names.txt" For input As #1

Line Input #1, buf
'
' Skip to first line
'
While Not EOF(1) and buf <> ""
Line Input #1, buf
Wend
'
' Build Name
'
Name = RTrim(Mid(buf, 13, 30)) & vbCrLf
Line Input #1, buf
Name = Name & RTrim(Mid(buf, 13, 30)) & vbCrLf
Line Input #1, buf
Name = Name & RTrim(Mid(buf, 13, 30))
'
' Skip last line
'
Line Input #1, buf
'
' Add to table
'
rst.Addnew
rst!Name = Name
rst.Update
End If
Wend

Close #1
Set rst = Nothing
Set dbs = Nothing
' **************************************


hth,
Wayne