Ascii text import

[waif]

I am looking to import a series of loosely formatted ascii text emails (mbox) into access 2000.

There are multiple emails in a single text file, and each email takes up a random number of lines (as would be expected).

All the messages all have the same number (and format) of header lines however, but have a variable number of lines in the body.

The starting text for each message is identical, so it could be used to kick off the start of a new db record.

I'd like to capture only a few of the header lines in the database (date, from, subject), discarding the others and would like to dump each of the body lines into a memo field, with one database record for each email in the file.

I expect this is possible, but don't have any background in vb (which I expect is what is required).

If someone could point me in the right direction, that would be wonderful.

 

[Liv Manto]

Something like this, the table is pre-made, with two fields.



Sub ReadText()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Dim ctrLine As Integer, CtrDiv As Integer, DivisionChapString As String, ctrrecords As Integer, LongestLengthLineofRecord As Integer, LongestText As String

Dim rst As DAO.Recordset
' to fill up the existing table with the text file.

Dim db As Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("NewtableDef") 'TextPerLine ' opened the table

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\E temp\6ac.txt")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
ctrLine = 0
CtrDiv = 0
ctrrecords = 0
LongestLengthLineofRecord = 0
LongestText = ""

Do While ts.atendofline <> True
s = Nz(ts.ReadLine, "")
'MsgBox s

rst("TextPerLine") = s
ctrrecords = ctrrecords + 1
rst("ID") = ctrrecords
rst.Update

Loop

ts.Close



---


Now since its ascii, you based your fields based on the number of your delimiter.

so say your text is

a;b;c;d;e;ff;ggg;hh

and you only want to take ggg, which is the 7th data

do until instr(text, ";") =0

you go if instr text = (text, ";") >0 then right(text, len(text) -instr(x, ";"))

(this means that you only take what is right of the first ";" )

counterdelimited = counterdelimited + 1
if counterdelimited = 7 then mydata = text
if instr(text, ";") =0 then exit do
loop