parsing from a txt file into a table

i have received a bit of help in other categories trying to get to the route
of the problem.. a solution was developed but i don't really know how to
program.

i can import my TXT files fine into the table and i receive 2 fields.. field
1 for left of the colon and field 2 for right of the colon

everything looks great....
is there a simple script to run that will convert the data from the 2 fields
in to records? so that i can have them on my table?

like the field columns would look like..
Company|Status|BTN|Contact|Term|Start|End|MARC

and the data from the right of the colon would parse into those fields?..

using
Contract Information:

as the delimiter to begin a new record?

here is what my data looks like.....



Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

may be other ways to skin a cat... if I was doing this, I'd import the file
into Excel then reformat it as a database then pull it into your access
fields. Look on the Excel Disc Groups for 'formatting columns into rows for
a database'? or somehting like that... may take a while to figure it out but
I learned that doing some pre-import formatting in Excel was easiest for me
to import odd data constructs... maybe someone else here can help you format
that columnar data into access tables... I'd like to know how also.

"brokerluke" wrote:

i have received a bit of help in other categories trying to get to the route
of the problem.. a solution was developed but i don't really know how to
program.

i can import my TXT files fine into the table and i receive 2 fields.. field
1 for left of the colon and field 2 for right of the colon

everything looks great....
is there a simple script to run that will convert the data from the 2 fields
in to records? so that i can have them on my table?

like the field columns would look like..
Company|Status|BTN|Contact|Term|Start|End|MARC

and the data from the right of the colon would parse into those fields?..

using
Contract Information:

as the delimiter to begin a new record?

here is what my data looks like.....



Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

You importing that data into a table, but each field + one line of data is
going into a different record, and how can you stitch this all back together
that way?

The only realistic approach to this is you going to have to sit down and
write some code to do this, there's no simple way out of this.

Given that you now shown what your sample data looks like, you can see that
it's not one row data that makes up one record, but "many" rows of text data
needs to makes up one record. because of this type of data structure, you're
not going to be able to use the built in import routines to parse out this
data.

I would suggest that you ask the people to send you this data in some format
like excel and therefore it would be able to be imported into MS access, if
you don't have control over this data, then the next step is you're going to
have to sit down and simply write code to parse out this data. in other
words this solution you seek is going to require you to have some
programming skills.

the basic approach to open a text file in code is as follows:

Sub ReadTextFile

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String
Dim lngLines As Long
Dim lngBlank As Long

strFile = "c:\my data\MyData.txt"

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf
If Trim(strLineBuf) = "" Then
lngBlank = lngBlank + 1
Else
lngLines = lngLines + 1
End If
Loop
Close intF

End If

MsgBox "Number non blank lines = " & lngLines & vbCrLf & _
"Blank lines = " & lngBlank & vbCrLf & _
"Total = " & lngBlank + lngLines

End Function

In addition to doing the above, you'll have to write code to parse out each
line of code into one record. This is not trivial task if you don't have
coding experiance.

the following code would give some of the basic idea of the challenge you
have he

Public Sub ReadTextFile()

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String
Dim lngLines As Long
Dim lngBlank As Long
Dim rst As DAO.Recordset

strFile = "c:\my data\MyData.txt"
Set rst = CurrentDb.OpenRecordset("tblName")
intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf
Do Until strLineBuf = "Customer Information:" And EOF(intF) =
False
Line Input #intF, strLineBuf
Loop
' start a new record...
Line Input #intF, strLineBuf
rst.AddNew
strBuf = Split(strOneLine, ":")(1)
rst!CustomerName = strBuf

Line Input #intF, strLineBuf
rst!ContractFileNum = strBuf

' .etc .etc .etc

rst.Update
Loop

Close intF
rst.Close
End If

End Function
End Sub

The Above is by no means a solution, but is only a general shell or skeleton
of an outline of the type of approach and code that you'll need to develop
to solve this problem. (so, the above is "air" code that I written off the
top of my head when writing this post..but, it is the general idea and are
approach to the type of code you'll need here).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada