Import data from a text file

  • Thread starter Thread starter Mat
  • Start date Start date
M

Mat

Hi
I have a log file that records an action in following
format:
50144021 12-17-2004 21:00:44 Mail Sent Subject: Test file
TO: (e-mail address removed)

I want my database to look into this file and return the
date/time of the last send in the log to match up with a
record in one of my tables that has the following
fields: "email","last sent", "subject". (The match will
be done on the email address field)

I can therefore look at each record and identify when
each email address was last sent the file.

Any help would be much appreciated...
Thanks
Mat
 
Hi
I have a log file that records an action in following
format:
50144021 12-17-2004 21:00:44 Mail Sent Subject: Test file
TO: (e-mail address removed)

I want my database to look into this file and return the
date/time of the last send in the log to match up with a
record in one of my tables that has the following
fields: "email","last sent", "subject". (The match will
be done on the email address field)

I can therefore look at each record and identify when
each email address was last sent the file.
Click to expand...

do you mean something like this?
Attention: purly airCode, I didn't checked any syntax, not is there
any error checking

dim Ary() as string
dim stTmp as string
dim rs as DAO.Recordset
dim I as long

set rs=currentdb.openrecordset ("MyTable",,dbAppendonly)

open "PathName" for Input as #1
While Not EOF(1)
Line Input #1 stTmp
ary=split(stTmp," ")
rs.addnew
rs!email=ary(ubound(ary))
rs!lastSend=ary(1)
For I=6 to Ubound(ary)-2)
rs!subject=rs!subject &ary(i)
next I
rs.update
wend

close #1
rs.close
set rs=nothing


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Thanks,
However :
1. The data imported should not append an extra record to
the table - it needs to match up the email entry in the
log file with the email field in the table and then
insert the date value from the log file into
the 'LastSent' field in the table. Does that explain it
better ?

Any ideas much appreciated ?
Thanks
Mat
-----Original Message-----


do you mean something like this?
Attention: purly airCode, I didn't checked any syntax, not is there
any error checking

dim Ary() as string
dim stTmp as string
dim rs as DAO.Recordset
dim I as long

set rs=currentdb.openrecordset ("MyTable",,dbAppendonly)

open "PathName" for Input as #1
While Not EOF(1)
Line Input #1 stTmp
ary=split(stTmp," ")
rs.addnew
rs!email=ary(ubound(ary))
rs!lastSend=ary(1)
For I=6 to Ubound(ary)-2)
rs!subject=rs!subject &ary(i)
next I
rs.update
wend

close #1
rs.close
set rs=nothing


If you expect an answer to a personal mail, add the
Click to expand...
word "manfred" to the first 10 lines in the message
 
Thanks,
However :
1. The data imported should not append an extra record to
the table - it needs to match up the email entry in the
log file with the email field in the table and then
insert the date value from the log file into
the 'LastSent' field in the table. Does that explain it
better ?
Click to expand...

BTW.Did it work?

thus this please you?

dim Ary() as string
dim stTmp as string
dim rs as DAO.Recordset
dim I as long

set rs=currentdb.openrecordset ("MyTable",,dbOpenDynaset)

open "PathName" for Input as #1
While Not EOF(1)
Line Input #1 stTmp
ary=split(stTmp," ")
rs.findfirst "email='" &ary(ubound(ary))
if rs.noMatch then
rs.addnew
rs!email=ary(ubound(ary))
else
rs.edit
endif
rs!lastSend=ary(1)
For I=6 to Ubound(ary)-2)
rs!subject=rs!subject &ary(i)
next I
rs.update
wend

close #1
rs.close
set rs=nothing

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Hi,
Thanks for this - I now try and run the code and gets to
the line and stops - Operation is not supported for this
type of object. (Error 3251)
Any ideas - it looks like it is nearly there.
Thanks
Mat.

rs.findfirst "email='" &ary(ubound(ary))
-----Original Message-----


BTW.Did it work?

thus this please you?

dim Ary() as string
dim stTmp as string
dim rs as DAO.Recordset
dim I as long

set rs=currentdb.openrecordset ("MyTable",,dbOpenDynaset)

open "PathName" for Input as #1
While Not EOF(1)
Line Input #1 stTmp
ary=split(stTmp," ")
rs.findfirst "email='" &ary(ubound(ary))
if rs.noMatch then
rs.addnew
rs!email=ary(ubound(ary))
else
rs.edit
endif
rs!lastSend=ary(1)
For I=6 to Ubound(ary)-2)
rs!subject=rs!subject &ary(i)
next I
rs.update
wend

close #1
rs.close
set rs=nothing


If you expect an answer to a personal mail, add the
Click to expand...
word "manfred" to the first 10 lines in the message
 
Hi,
Thanks for this - I now try and run the code and gets to
the line and stops - Operation is not supported for this
type of object. (Error 3251)
Any ideas - it looks like it is nearly there.
Thanks
Mat.

rs.findfirst "email='" &ary(ubound(ary))
Click to expand...

Oopps I missed the closing quote

rs.findfirst "email='" &ary(ubound(ary)) &"'"


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Thanks, have updated the closing quote &"'"
However I still get same error msg and it stops at the
same line.
If I highlight the '&ary' part on this line, it shows it
is finding the correct email address in the log file.
(highlights the email address listed in the log file on
mouse over)

Will this code, once we have this bit corrected, look at
all email address entries sequentially in the log file
and match them up with their corresponding email address
in the table, and then insert the 'lastsent' portion from
the same line in the log file into the lastsent field in
the table ? (Sorry if this is a bit longwinded!)
Your time and effort is MUCH appreciated.
Thanks
Mat
Oopps I missed the closing quote

rs.findfirst "email='" &ary(ubound(ary)) &"'"


If you expect an answer to a personal mail, add the
Click to expand...
word "manfred" to the first 10 lines in the message
 
Thanks, have updated the closing quote &"'"
However I still get same error msg and it stops at the
same line.
If I highlight the '&ary' part on this line, it shows it
is finding the correct email address in the log file.
(highlights the email address listed in the log file on
mouse over)

Will this code, once we have this bit corrected, look at
all email address entries sequentially in the log file
and match them up with their corresponding email address
in the table, and then insert the 'lastsent' portion from
the same line in the log file into the lastsent field in
the table ? (Sorry if this is a bit longwinded!)
Your time and effort is MUCH appreciated.
Thanks
Mat

word "manfred" to the first 10 lines in the message
Click to expand...

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
On Sun, 19 Dec 2004 12:43:07 -0800, "Mat"

So lets start from the Beginning:
My sample needs:
a reference to MicroSoft DAO 3.x in Tools----References

it creates now a new table ( if this table is not existing)
it takes the Fields: theEmail, LastSend,theSubject

three thinks struck my mind:
the file in PathName has to exist
the dates have to be in Order to get the last entry
the logfile gets read everytime from the beginning

paste this into a new Module

Option Compare Database
Option Explicit

Sub WriteTheLogIntoMyTable()
Dim Ary() As String
Dim stTmp As String
Dim rs As DAO.Recordset
Dim I As Long
Dim db As DAO.Database
Dim PathName As String
Dim TableName As String
Dim stTemp As String

Set db = CurrentDb
PathName = "C:\emailTest.txt"
'this is the location and name of your logfile
TableName = "MyTable"
'this is the Table I need
CheckTable db, TableName
'if this table is not existing, it gets created
stTemp = "SELECT theEmail, LastSend,theSubject " _
& " FROM " & TableName
'this is the SQL string to choose the fields from the table
Set rs = db.OpenRecordset(stTemp, , dbOpenDynaset)
'this is openening a recordset for finding
Open PathName For Input As #1
'not I open the logFile
While Not EOF(1)
' read the file till EndOfFile
Line Input #1, stTmp
'read a single line (dump the end of line marker)
Ary = Split(stTmp, " ")
'split the readLine on blank
rs.findfirst "theEmail='" & Ary(UBound(Ary)) & "'"
'find the first match of the email
' if you have a few 1000 emails this will be the bootleneck
If rs.noMatch Then
'if it is not in the table, then add it
rs.AddNew
rs!theEmail = Ary(UBound(Ary))
'it is the end of the read line
Else
'it was found
rs.edit
End If
rs!lastSend = Ary(1)
' change the date
stTemp = ""
For I = 6 To UBound(Ary) - 2
'add the subject, which can have a lot of blanks
'therefor add everything between the date and the TO
stTemp = stTemp & Ary(I)
Next I
rs!theSubject = stTemp
rs.Update
'write the field to disk
Wend

Close #1
'close the file, if you miss this you can't open it again
rs.Close
'close the recordset
Set rs = Nothing
Set db = Nothing
'set all object you used to nothing
End Sub


Sub CheckTable(db As DAO.Database, TableName As String)
Dim SQL As String
Dim tbl As DAO.TableDef
On Error Resume Next
Set tbl = db.TableDefs("MyTable")
If Err.Number <> 0 Then
SQL = "CREATE TABLE " & TableName _
& " (theEmail CHAR(255) NOT NULL" _
& ", LastSend DATE" _
& ", theSubject CHAR(255))"
CurrentDb.Execute SQL
End If
db.TableDefs.Refresh
Set tbl = Nothing
End Sub

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Hi
I have added the reference as mentioned.
The log file does exist, but the module stops on line:
Open PathName For Input As #1

I have also tried entering the log file instead of using
the PathName variable, but that does not have any affect.

Any ideas ?
Thanks
Mat.
-----Original Message-----
On Sun, 19 Dec 2004 12:43:07 -0800, "Mat"

So lets start from the Beginning:
My sample needs:
a reference to MicroSoft DAO 3.x in Tools----References

it creates now a new table ( if this table is not existing)
it takes the Fields: theEmail, LastSend,theSubject

three thinks struck my mind:
the file in PathName has to exist
the dates have to be in Order to get the last entry
the logfile gets read everytime from the beginning

paste this into a new Module

Option Compare Database
Option Explicit

Sub WriteTheLogIntoMyTable()
Dim Ary() As String
Dim stTmp As String
Dim rs As DAO.Recordset
Dim I As Long
Dim db As DAO.Database
Dim PathName As String
Dim TableName As String
Dim stTemp As String

Set db = CurrentDb
PathName = "C:\emailTest.txt"
'this is the location and name of your logfile
TableName = "MyTable"
'this is the Table I need
CheckTable db, TableName
'if this table is not existing, it gets created
stTemp = "SELECT theEmail, LastSend,theSubject " _
& " FROM " & TableName
'this is the SQL string to choose the fields from the table
Set rs = db.OpenRecordset(stTemp, , dbOpenDynaset)
'this is openening a recordset for finding
Open PathName For Input As #1
'not I open the logFile
While Not EOF(1)
' read the file till EndOfFile
Line Input #1, stTmp
'read a single line (dump the end of line marker)
Ary = Split(stTmp, " ")
'split the readLine on blank
rs.findfirst "theEmail='" & Ary(UBound(Ary)) & "'"
'find the first match of the email
' if you have a few 1000 emails this will be the bootleneck
If rs.noMatch Then
'if it is not in the table, then add it
rs.AddNew
rs!theEmail = Ary(UBound(Ary))
'it is the end of the read line
Else
'it was found
rs.edit
End If
rs!lastSend = Ary(1)
' change the date
stTemp = ""
For I = 6 To UBound(Ary) - 2
'add the subject, which can have a lot of blanks
'therefor add everything between the date and the TO
stTemp = stTemp & Ary(I)
Next I
rs!theSubject = stTemp
rs.Update
'write the field to disk
Wend

Close #1
'close the file, if you miss this you can't open it again
rs.Close
'close the recordset
Set rs = Nothing
Set db = Nothing
'set all object you used to nothing
End Sub


Sub CheckTable(db As DAO.Database, TableName As String)
Dim SQL As String
Dim tbl As DAO.TableDef
On Error Resume Next
Set tbl = db.TableDefs("MyTable")
If Err.Number <> 0 Then
SQL = "CREATE TABLE " & TableName _
& " (theEmail CHAR(255) NOT NULL" _
& ", LastSend DATE" _
& ", theSubject CHAR(255))"
CurrentDb.Execute SQL
End If
db.TableDefs.Refresh
Set tbl = Nothing
End Sub

If you expect an answer to a personal mail, add the
Click to expand...
word "manfred" to the first 10 lines in the message
 
Hi
I have added the reference as mentioned.
The log file does exist, but the module stops on line:
Open PathName For Input As #1
Click to expand...

PathName = "C:\emailTest.txt"

this means there is a file [emailtest.txt] in C:
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Hi

Thanks. Guess what it now seems to work !
I can fine tune it to exact requirements, but it does
update the last sent as I wanted.
I can only say MANY thanks for all your time and effort -
really much appreciated.
Mat


-----Original Message-----
Hi
I have added the reference as mentioned.
The log file does exist, but the module stops on line:
Open PathName For Input As #1
Click to expand...

PathName = "C:\emailTest.txt"

this means there is a file [emailtest.txt] in C:
If you expect an answer to a personal mail, add the
Click to expand...
word "manfred" to the first 10 lines in the message
 
Hi

Thanks. Guess what it now seems to work !
I can fine tune it to exact requirements, but it does
update the last sent as I wanted.
I can only say MANY thanks for all your time and effort -
really much appreciated.
Mat
Click to expand...
You are welcome, but next time please add that you are a beginner, I
would have added more comments and didn't assumed that you know (what
" I " call basic stuff) a lot about VBA.

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Andi,

Just a couple of questions:

1. If I have a duplicate email address - some users get
more than one file - can a match be done on more than the
original email address - eg can a comparison be done on
say, Email AND Subject and then return the LASTSENT date
for each into the relevant record in the table?
EG:
(e-mail address removed) gets Daily Fle A AND ALSO
(e-mail address removed) gets Daily Fle B

2 records exist in the table with subject and email that
will also match 2 entries in the log file.

Can the code update each record according to EMAIL and
SUBJECT - at the moment it only looks for EMAIL match?

2. How can you stop the concatenation of the subject
entry - it currently merges all words into one in the
table.
Thanks
Mat.
-----Original Message-----

You are welcome, but next time please add that you are a beginner, I
would have added more comments and didn't assumed that you know (what
" I " call basic stuff) a lot about VBA.

If you expect an answer to a personal mail, add the
Click to expand...
word "manfred" to the first 10 lines in the message
 
Andi,

Just a couple of questions:

1. If I have a duplicate email address - some users get
more than one file - can a match be done on more than the
original email address - eg can a comparison be done on
say, Email AND Subject and then return the LASTSENT date
for each into the relevant record in the table?
EG:
(e-mail address removed) gets Daily Fle A AND ALSO
(e-mail address removed) gets Daily Fle B
Click to expand...


do I get it right: your subject is a "fixed" Variable and you want for
every "email and subject" a record
2. How can you stop the concatenation of the subject
entry - it currently merges all words into one in the
table.
Click to expand...
I added a blank


While Not EOF(1)
Line Input #1, stTmp
Ary = Split(stTmp, " ")
stTemp = ""
For I = 6 To UBound(Ary) - 2
stTemp = stTemp & " " & Ary(I)
Next I
stTemp = Mid(stTemp, 2)
rs.findfirst "theEmail='" & Ary(UBound(Ary)) & "'" _
& " AND theSubject='" & stTemp & "'"
If rs.noMatch Then
rs.AddNew
rs!theEmail = Ary(UBound(Ary))
rs!theSubject = stTemp
Else
rs.edit
End If
rs!lastSend = Ary(1) & " " & Ary(2)
' here I added the time now, is your dateformat everytime :mm-dd-yyyy
rs.Update
Wend

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Back
Top