Input multiple line text file into one access record

[Paul]

Hello Everybody,
Being new to VBA for Access, I have a question about inputing a text
file into access. The text file has 23 lines per file and needs to go
into 1 record in a table. The 1st four lines are garbage and don't need

to be inputted. THe rest of the lines only need what is after an equal
sign (=) inputted. Can somebody please help me?
THanks

[pietlinden]

Could you show enough of the file so we have some clue what you're
dealing with, and/or could you describe it?

If the fields in the text file are delimited then you should be OK.
Maybe you can import to a temporary table and delete the first four
records and then go from there, but this sounds like something you may
need to read in code and append to a table.

[Paul]

From: MM
Sent: Wednesday, August 17, 2005 2:56 PM
To: gr************* *@thank.org
Subject: Grant Application
Social Security # = 412345678
Branch of Service = Air Force
Last Name = Jones
Rank - First Name = Robert
Initial = J
Mailing Address = 1122 Boogie Woogie
City = new prague
State = GA
Zip = 12345

First 4 lines not needed and then everything after equal sign goes into
a field in a single record.

[pietlinden]

Here's the code... watch the wrap. I'm just about sure this can be
done more nicely with ADO and textstreams, but I haven't a clue how to
do this... so here it is using line reading stuff... <try not to laugh>

Private Sub Command0_Click( )
Dim db As DAO.Database
Dim rsDest As DAO.Recordset

Dim intInputFile As Integer
Dim strLineIn As String
Dim strFileToImport As String
Dim intCounter As Integer
'---stores the location of the equals sign
Dim strText As String
Dim intFindDelim As Integer
'--get the file from the user
'--NOTE: This line requires the OpenSaveFile API from here:
'http://www.mvps.org/access/api/api0001.htm

strFileToImport = TestIt
intInputFile = FreeFile
Open strFileToImport For Input As intInputFile

'--open the file for reading, skipping the first four lines
For intCounter = 1 To 4
'--read, but do not process lines 1-4
Line Input #intInputFile, strLineIn
Next intCounter
'--open the recordset so we can add records to it.
Set db = DBEngine.Worksp aces(0).Databas es(0)
Set rsDest = db.OpenRecordse t("tblSoldier ", dbOpenTable)
'-reset counter to zero (to correspond to zero-based fields
collection of the tabledef/recordset
rsDest.AddNew
intCounter = 0

'--read the rest of the file. Basic idea is that intFieldNo
(zero-based) = the sort of offset of the value
'--IOW, if you loop through a counter, the counter= 0 at 5 (line
1). then these will match the field offsets
'--in the table.

'-- Branch of Service = Air Force
'-- Last Name = Jones
'-- Rank - First Name = Robert

Do Until EOF(intInputFil e)
Line Input #intInputFile, strLineIn
'strLineIn needs to be parsed a little - strip off the junk
intFindDelim = InStr(1, strLineIn, "=", vbTextCompare)
If intFindDelim = 0 Then Exit Do
'--trim off the junk
strText = Trim$(Right$(st rLineIn, Len(strLineIn) -
intFindDelim))

'--write the field to the record
rsDest.Fields(i ntCounter) = strText
intCounter = intCounter + 1
Loop

'--we should have all the fields now, so save the record
rsDest.Update

rsDest.Close
Set rsDest = Nothing
Set db = Nothing

MsgBox "done"

End Sub

[Paul]

Thanks - it worked great