Extract Text, Sent to Excel File

[titans25]

I am a bondsman, and I have a text file that is sent to me, sometimes daily. It is in the format of a text file. The problem for me is that each record is 3 or 4 lines deep. I would like to export the data from this text file into an Excel worksheet.
The way the report comes to me currently isn't user friendly the way that it is.

I am expecially interested in the following items.
from the 1st record:

File Number: 06CR 052172
Defendant Name: Xxxxx,Xxxxxx,Xxxxxx
Bond: $2,000 Sec

There may be as many as 300 records to pull this data from.
It is always laid out in this manner. I can not change this.
Thanks for your help.
titans25
I have attached a small sample of the file as I receive it.

[Al42]

1) I'm assuming that the reason the first line doesn't line up with the other text is because you made it that way, not because the real text files have inconsistent data.

2) You have a choice - you can use Split() to split up the records, then the lines within each record, then the data in each line, or you can just brute-force read the file, line by line, keeping track of the kind of line you're in, and handle it the way you handle that kind of line (first line, second line, other line).

3) What have you written so far?

[Comintern]

I think I would go with the split method, especially if the layout of the text file is like the sample that you posted. The natural delimiter between records is a series of more than one vbCrLf pair. After you get the contents of the file into an array, it should be a fairly easy matter of parsing out the individual fields.

Private Sub TextImport()
 
    Dim sFile As String, iFile As Integer, sRecords() As String, sBuffer As String
    Dim lIndex As Long
    
    sFile = "C:\Test Data.txt"
    iFile = FreeFile
    
    Open sFile For Binary As #iFile
    sBuffer = String$(LOF(iFile), Chr$(0))
    Get #iFile, , sBuffer
    Close #iFile
    
    sRecords = Split(sBuffer, vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf)
    For lIndex = LBound(sRecords) To UBound(sRecords)
        Debug.Print sRecords(lIndex)
        'Parse record here.
    Next lIndex
 
End Sub

[titans25]

Thanks for the response ai42 and Comintern.
ai42, I havent written anytnhing as of yet, as I am very new to this but willing to learn. I do not know where to start as of yet.
Comintern, I saved the file test.txt to my c:\drive, and I ran the code and nothing happened. I pasted the code just as you have it written.
Thanks for any help that you can supply.
I have uploaded the actual file that I receive. Please test the code with this file and let me know if I have done something incorrect.
Thanks
titans25

[roland56]

Worked today on your problem.
My VB application extracts requested data and writes Excel file but needs finetuning according to specific needs.
Is this what you are looking for or do you need only 'bonded' items?
Let me know if you are interested in some billed programming assistance.

[titans25]

That looks great, just like I would like for it to. It would be nice to add their charge also. Don't have to have it, but it would be nice.
Thanks
titans25

[roland56]

Worked further on your problem.

•VB Program reads 'DISTRICT CRIMINAL CALENDAR' for Court specific Date (Ex: '090806-Data.TXT') and generates Excel file 'DCC-090806.XLS' in same Directory as the Data File.
•Cases are sorted per 'BOND' type (‘SEC’ – ‘UNS’ – ‘CUS’ – ‘CSH’ & ‘---‘)
•Cases are then sorted per BOND Amount (Descendent per 'BOND' type.
•Overview is made per 'BOND' type: 'Dollar amount' & 'Number of Cases'
•Total Bonds amount for specific court date ‘09/08/06’ is calculated.
•Page LAYOUT is SET to 11x17 Landscape
•File Name, Page Numbering & Date will be printed in the Footer.

Please check result PDF file and provide comments.
I would like to get some additional DATA files from you in order to run more tests with the program.
I will need an email address in order to supply you with the Excel File.
Let me know if you are interested in some billed programming assistance.

Bye for now.
Roland

[titans25]

Thanks for all of your help roland56.
I am sorry that it took so long to get back with you on this. I have had some unforseen issues.
Your file looks great.
I have uploaded some more sample text files for you to look at.
The email that I need you to send anything to me is:
[email protected]
Thanks so much.
Barry G.

[titans25]

Sorry roland56,
I forgot to upload the files.
Here they are.
Thanks
Barry G.