By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,403 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,403 IT Pros & Developers. It's quick & easy.

Input multiple line text file into one access record

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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.Workspaces(0).Databases(0)
Set rsDest = db.OpenRecordset("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(intInputFile)
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$(strLineIn, Len(strLineIn) -
intFindDelim))

'--write the field to the record
rsDest.Fields(intCounter) = 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

Nov 13 '05 #4

P: n/a
Thanks - it worked great

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.