It’s pretty ugly data. I think you’re going to have to loop through your input table and make intelligent guesses about what gets imported and where it goes.
It seems that to be a valid message, the first line must be Message:#. Then you want to capture anything in the formats:
Date: blah
From: blah
Subject: blahblah where subject can have more than one line.
I would make my output table structure:
ID: Autonumber
MessageID: integer
Subject, from, Body: nice big text fields
MessageDate: whatever date/time format you want.
Then, in VB:
You’re going to open the input and output tables as a recordsets.
Loop through the RawData recordset
If left(RawData,8) = “Message:” then
‘start of a good message
‘skip all empty lines
‘store the empty string to a temp variable
store “” to lsBodyText
‘store the MessageID, skip to next line
liMEssageID = CINT(right(RawData,len(RawData) – 8)), skip to next line
‘store the date, skip
‘store the From, skip
‘Now the tricky part. Everything else until the next message (bad or good) is
‘body text
if left(RawData,8) <> “Message:” and left(RawData,5) <> “From:” then
‘body text, could be multiple lines
lsBodyText = trim(lsBodyText & “ “ & trim(RawData))
skip to the next line
ELSE
‘start of a new message
‘DO NOT skip to next line
‘Write the stored values to the output table
end if
Else
‘bad data, skip to the next line
End If
Tab this psuedocode appropriately and you should see the flow of the program.
This assumes that the message pieces always are in the same order. If not, then you’ve got to test each line to see what kind of data it is.
Good luck.
