importing text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to import text from emails which are set up
the same into access I want each email to be a single
record but the import sees each line as a different
record I would like each line to be a separate field is
this possible.


Please Help!!!

colin
 
Hi Colin,

You won't be able to do this with Access's standard import routines. By
the sound of it the messages are saved in one or more text files. To
import them automatically you'll need to write VBA code that opens the
the file(s) and reads them line by line, sorts out which line goes in
which field, and appends them to your table.

If you post back here with more information about how the emails are
stored and what you want to do with them we should be able to get you
started.
 
A little more information needed:

1) Is there just one email per file, or are there more than one?

2) If more than one, how do you tell where one ends and the next one
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:, First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one line?


Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.
-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard import routines. By
the sound of it the messages are saved in one or more text files. To
import them automatically you'll need to write VBA code that opens the
the file(s) and reads them line by line, sorts out which line goes in
which field, and appends them to your table.

If you post back here with more information about how the emails are
stored and what you want to do with them we should be able to get you
started.

I am trying to import text from emails which are set up
the same into access I want each email to be a single
record but the import sees each line as a different
record I would like each line to be a separate field is
this possible.


Please Help!!!

colin
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there more than one?

2) If more than one, how do you tell where one ends and the next
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:, First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one line?
Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.
-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard import routines. By
the sound of it the messages are saved in one or more text files. To
import them automatically you'll need to write VBA
Click to expand...
code
that opens the
the file(s) and reads them line by line, sorts out
Click to expand...
which
line goes in
which field, and appends them to your table.

If you post back here with more information about how the emails are
stored and what you want to do with them we should be able to get you
started.
Click to expand...
wrote:

I am trying to import text from emails which are set up
the same into access I want each email to be a single
record but the import sees each line as a different
record I would like each line to be a separate field is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order
-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there more than one?

2) If more than one, how do you tell where one ends and the next
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:, First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one line?
Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or more
text files. To
import them automatically you'll need to write VBA code
that opens the
the file(s) and reads them line by line, sorts out which
line goes in
which field, and appends them to your table.

If you post back here with more information about how
the emails are
stored and what you want to do with them we should be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are set
up
the same into access I want each email to be a single
record but the import sees each line as a different
record I would like each line to be a separate field is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
.
Click to expand...
 
Hi Colin,

The code will need to be something like this (which is a mixture of air
code and pseudocode), to add the data to a table which already contains
the necessary fields:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim lngPos as Long
Dim strLine as String
Dim strFName as String
Dim strFValue as String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("tblMyTable")

lngFN = FreeFile()
Open "D:\folder\file.txt" As #lngFN
rsr.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsr.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsr.Fields(strFName).Value = CCur(strFValue)

Case 'and so on

End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsr.Fields("Item").Value & vbCRLF & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN






Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order
-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there more than one?

2) If more than one, how do you tell where one ends and the next
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:, First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one line?


On Tue, 4 Nov 2003 14:21:45 -0800, "colin"

Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or more
text files. To
import them automatically you'll need to write VBA code
that opens the
the file(s) and reads them line by line, sorts out which
line goes in
which field, and appends them to your table.

If you post back here with more information about how
the emails are
stored and what you want to do with them we should be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are set
up
the same into access I want each email to be a single
record but the import sees each line as a different
record I would like each line to be a separate field is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Hello John

Thank you for your help.
I have created a table with fields required ,what do i
do next to make the code work?

Regards

Colin
-----Original Message-----
Hi Colin,

The code will need to be something like this (which is a mixture of air
code and pseudocode), to add the data to a table which already contains
the necessary fields:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim lngPos as Long
Dim strLine as String
Dim strFName as String
Dim strFValue as String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("tblMyTable")

lngFN = FreeFile()
Open "D:\folder\file.txt" As #lngFN
rsr.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsr.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsr.Fields(strFName).Value = CCur(strFValue)

Case 'and so on

End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsr.Fields("Item").Value & vbCRLF & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN






Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order
-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there more
than one?

2) If more than one, how do you tell where one ends and
the next
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:,
First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one
line?


On Tue, 4 Nov 2003 14:21:45 -0800, "colin"

Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or more
text files. To
import them automatically you'll need to write VBA
code
that opens the
the file(s) and reads them line by line, sorts out
which
line goes in
which field, and appends them to your table.

If you post back here with more information about how
the emails are
stored and what you want to do with them we should be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are set
up
the same into access I want each email to be a
single
record but the import sees each line as a different
record I would like each line to be a separate field
is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Colin,

The "air code" I posted will need some modification to make it work.
Start by inserting a module into your database; then type this:
Sub ImportOneEmail(FileSpec As String)
into the module and hit Enter. Access will automatically add
End Sub
below the line you typed.

Now paste the code between the Sub... and End Sub lines. Replace
"tblMyTable" the actual name of your table, and
"D:\folder\file.txt"
with
FileSpec
(NB: no quotes round FileSpec).

Next you'll need to complete the code in the Select Case ... End Select
structure so that it processes all the fields you have defined in the
table.

To test it, hit Ctrl-G to open the VBA Immediate pane and type
ImportOneEmail "D:\folder\file.txt"
(replacing the D:\folder... with the actual drive,folder and name of one
of your emails, and hit enter. If you're very, very lucky the code will
run and create a record in your table with the contents of the email.
Much more likely, it will fail with a compile error, and you'll begin to
learn the joys of correcting and debugging code!



Hello John

Thank you for your help.
I have created a table with fields required ,what do i
do next to make the code work?

Regards

Colin
-----Original Message-----
Hi Colin,

The code will need to be something like this (which is a mixture of air
code and pseudocode), to add the data to a table which already contains
the necessary fields:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim lngPos as Long
Dim strLine as String
Dim strFName as String
Dim strFValue as String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("tblMyTable")

lngFN = FreeFile()
Open "D:\folder\file.txt" As #lngFN
rsr.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsr.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsr.Fields(strFName).Value = CCur(strFValue)

Case 'and so on

End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsr.Fields("Item").Value & vbCRLF & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN






Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order

-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there more
than one?

2) If more than one, how do you tell where one ends and
the next
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:,
First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one
line?


On Tue, 4 Nov 2003 14:21:45 -0800, "colin"

Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or more
text files. To
import them automatically you'll need to write VBA
code
that opens the
the file(s) and reads them line by line, sorts out
which
line goes in
which field, and appends them to your table.

If you post back here with more information about how
the emails are
stored and what you want to do with them we should be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are
set
up
the same into access I want each email to be a
single
record but the import sees each line as a different
record I would like each line to be a separate field
is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
hi john

i have tried to set out as you have advised i do not
understand "complete the code in the Select Case ... End
Select
structure so that it processes all the fields you have
defined in the table. "

this is what i have done so far:
Sub ImportOneEmail(FileSpec As String)
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim lngPos As Long
Dim strLine As String
Dim strFName As String
Dim strFValue As String

Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("tblEMAIL")

lngFN = FreeFile()
Open c:\web.txt For Random As #lngFN
rsR.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsR.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsR.Fields(strFName).Value = CCur(strFValue)

Case and so no
End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsR.Fields("Item").Value & vbCrLf & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN

End Sub
when i run i get an error as follows

Compile error
Ambiguous name detected:ImportOneEmail

thank you

Colin
-----Original Message-----
Colin,

The "air code" I posted will need some modification to make it work.
Start by inserting a module into your database; then type this:
Sub ImportOneEmail(FileSpec As String)
into the module and hit Enter. Access will automatically add
End Sub
below the line you typed.

Now paste the code between the Sub... and End Sub lines. Replace
"tblMyTable" the actual name of your table, and
"D:\folder\file.txt"
with
FileSpec
(NB: no quotes round FileSpec).

Next you'll need to complete the code in the Select Case ... End Select
structure so that it processes all the fields you have defined in the
table.

To test it, hit Ctrl-G to open the VBA Immediate pane and type
ImportOneEmail "D:\folder\file.txt"
(replacing the D:\folder... with the actual drive,folder and name of one
of your emails, and hit enter. If you're very, very lucky the code will
run and create a record in your table with the contents of the email.
Much more likely, it will fail with a compile error, and you'll begin to
learn the joys of correcting and debugging code!



Hello John

Thank you for your help.
I have created a table with fields required ,what do i
do next to make the code work?

Regards

Colin
-----Original Message-----
Hi Colin,

The code will need to be something like this (which is
Click to expand...
a
mixture of air
code and pseudocode), to add the data to a table which already contains
the necessary fields:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim lngPos as Long
Dim strLine as String
Dim strFName as String
Dim strFValue as String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("tblMyTable")

lngFN = FreeFile()
Open "D:\folder\file.txt" As #lngFN
rsr.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsr.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsr.Fields(strFName).Value = CCur(strFValue)

Case 'and so on

End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsr.Fields("Item").Value & vbCRLF & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN






On Wed, 5 Nov 2003 03:00:47 -0800, "colin"

Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order

-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there more
than one?

2) If more than one, how do you tell where one ends and
the next
begins? (is there some sort of "end of record" signal?)

3) Do all the emails contain the same fields (Title:,
First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than one
line?


On Tue, 4 Nov 2003 14:21:45 -0800, "colin"

Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or more
text files. To
import them automatically you'll need to write VBA
code
that opens the
the file(s) and reads them line by line, sorts out
which
line goes in
which field, and appends them to your table.

If you post back here with more information about how
the emails are
stored and what you want to do with them we
Click to expand...
should
be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are
set
up
the same into access I want each email to be a
single
record but the import sees each line as a different
record I would like each line to be a separate field
is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Colin,

The "ambiguous name" error is probably happening because Access thinks
that there is some thing else called "ImportOneEmail". Possibly you have
pasted the code in twice in different places. It might be a good idea to
buy a book on learning Access VBA.

As for completing the code, you need to work on the bit between
Select Case
and
End Case
adding or modifying the individual Case statements so that between them
they handle every one of the fields in your table.
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue
Click to expand...
This one will work for all text fields where the field name is the same
in the emails and in the table. Just put the actual field names in the
list in place of Title, Address1, Address2.
Case "First Name"
'text field where names differ
rsR.Fields("FirstName").Value = strFValue
Click to expand...
If you have fields in your emails that need to go into fields in the
table that have different names, you need to use individual case
statements like this for each such field.
Case "Price", "Total"
'field type conversion
rsR.Fields(strFName).Value = CCur(strFValue)
Click to expand...
If fields in the email contain prices or quantitities, you may need to
use something like this to ensure that the text values from the emails
are converted into the right data types for the fields in your table.
Use CCur() for a Currency field, CLng() for an ordinary Number(Long)
field, and CDbl() for Number(Double).
End Select

hi john

i have tried to set out as you have advised i do not
understand "complete the code in the Select Case ... End
Select
structure so that it processes all the fields you have
defined in the table. "

this is what i have done so far:
Sub ImportOneEmail(FileSpec As String)
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim lngPos As Long
Dim strLine As String
Dim strFName As String
Dim strFValue As String

Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("tblEMAIL")

lngFN = FreeFile()
Open c:\web.txt For Random As #lngFN
rsR.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsR.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsR.Fields(strFName).Value = CCur(strFValue)

Case and so no
End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsR.Fields("Item").Value & vbCrLf & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN

End Sub
when i run i get an error as follows

Compile error
Ambiguous name detected:ImportOneEmail

thank you

Colin
-----Original Message-----
Colin,

The "air code" I posted will need some modification to make it work.
Start by inserting a module into your database; then type this:
Sub ImportOneEmail(FileSpec As String)
into the module and hit Enter. Access will automatically add
End Sub
below the line you typed.

Now paste the code between the Sub... and End Sub lines. Replace
"tblMyTable" the actual name of your table, and
"D:\folder\file.txt"
with
FileSpec
(NB: no quotes round FileSpec).

Next you'll need to complete the code in the Select Case ... End Select
structure so that it processes all the fields you have defined in the
table.

To test it, hit Ctrl-G to open the VBA Immediate pane and type
ImportOneEmail "D:\folder\file.txt"
(replacing the D:\folder... with the actual drive,folder and name of one
of your emails, and hit enter. If you're very, very lucky the code will
run and create a record in your table with the contents of the email.
Much more likely, it will fail with a compile error, and you'll begin to
learn the joys of correcting and debugging code!



Hello John

Thank you for your help.
I have created a table with fields required ,what do i
do next to make the code work?

Regards

Colin
-----Original Message-----
Hi Colin,

The code will need to be something like this (which is a
mixture of air
code and pseudocode), to add the data to a table which
already contains
the necessary fields:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim lngPos as Long
Dim strLine as String
Dim strFName as String
Dim strFValue as String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("tblMyTable")

lngFN = FreeFile()
Open "D:\folder\file.txt" As #lngFN
rsr.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field
name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsr.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsr.Fields(strFName).Value = CCur(strFValue)

Case 'and so on

End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsr.Fields("Item").Value & vbCRLF & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN






On Wed, 5 Nov 2003 03:00:47 -0800, "colin"

Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order

-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there
more
than one?

2) If more than one, how do you tell where one ends
and
the next
begins? (is there some sort of "end of record"
signal?)

3) Do all the emails contain the same fields (Title:,
First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than
one
line?


On Tue, 4 Nov 2003 14:21:45 -0800, "colin"

Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are
txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or
more
text files. To
import them automatically you'll need to write VBA
code
that opens the
the file(s) and reads them line by line, sorts out
which
line goes in
which field, and appends them to your table.

If you post back here with more information about
how
the emails are
stored and what you want to do with them we should
be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are
set
up
the same into access I want each email to be a
single
record but the import sees each line as a
different
record I would like each line to be a separate
field
is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
i am still having problems getting this working i am
getting error message "compile error:user defined type
not defined" And the first line is highlighted (dbD As
DAO.Database)

also can you suggest a good book?

Thank you for your help

regards
colin
-----Original Message-----
Colin,

The "ambiguous name" error is probably happening because Access thinks
that there is some thing else called "ImportOneEmail". Possibly you have
pasted the code in twice in different places. It might be a good idea to
buy a book on learning Access VBA.

As for completing the code, you need to work on the bit between
Select Case
and
End Case
adding or modifying the individual Case statements so that between them
they handle every one of the fields in your table.
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue
Click to expand...
This one will work for all text fields where the field name is the same
in the emails and in the table. Just put the actual field names in the
list in place of Title, Address1, Address2.
Case "First Name"
'text field where names differ
rsR.Fields("FirstName").Value = strFValue
Click to expand...
If you have fields in your emails that need to go into fields in the
table that have different names, you need to use individual case
statements like this for each such field.
Case "Price", "Total"
'field type conversion
rsR.Fields(strFName).Value = CCur(strFValue)
Click to expand...
If fields in the email contain prices or quantitities, you may need to
use something like this to ensure that the text values from the emails
are converted into the right data types for the fields in your table.
Use CCur() for a Currency field, CLng() for an ordinary Number(Long)
field, and CDbl() for Number(Double).
End Select

hi john

i have tried to set out as you have advised i do not
understand "complete the code in the Select Case ... End
Select
structure so that it processes all the fields you have
defined in the table. "

this is what i have done so far:
Sub ImportOneEmail(FileSpec As String)
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim lngPos As Long
Dim strLine As String
Dim strFName As String
Dim strFValue As String

Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("tblEMAIL")

lngFN = FreeFile()
Open c:\web.txt For Random As #lngFN
rsR.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsR.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsR.Fields(strFName).Value = CCur(strFValue)

Case and so no
End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsR.Fields("Item").Value & vbCrLf & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN

End Sub
when i run i get an error as follows

Compile error
Ambiguous name detected:ImportOneEmail

thank you

Colin
-----Original Message-----
Colin,

The "air code" I posted will need some modification to make it work.
Start by inserting a module into your database; then type this:
Sub ImportOneEmail(FileSpec As String)
into the module and hit Enter. Access will
Click to expand...
automatically
add
End Sub
below the line you typed.

Now paste the code between the Sub... and End Sub
Click to expand...
lines.
Replace
"tblMyTable" the actual name of your table, and
"D:\folder\file.txt"
with
FileSpec
(NB: no quotes round FileSpec).

Next you'll need to complete the code in the Select Case ... End Select
structure so that it processes all the fields you have defined in the
table.

To test it, hit Ctrl-G to open the VBA Immediate pane and type
ImportOneEmail "D:\folder\file.txt"
(replacing the D:\folder... with the actual
Click to expand...
drive,folder
and name of one
of your emails, and hit enter. If you're very, very lucky the code will
run and create a record in your table with the
Click to expand...
contents
of the email.
Much more likely, it will fail with a compile error,
Click to expand...
and
you'll begin to
learn the joys of correcting and debugging code!
Click to expand...
wrote:

Hello John

Thank you for your help.
I have created a table with fields required ,what do i
do next to make the code work?

Regards

Colin
-----Original Message-----
Hi Colin,

The code will need to be something like this (which
Click to expand...
is
a
mixture of air
code and pseudocode), to add the data to a table which
already contains
the necessary fields:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim fldF as DAO.Field
Dim lngFN as Long
Dim lngPos as Long
Dim strLine as String
Dim strFName as String
Dim strFValue as String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("tblMyTable")

lngFN = FreeFile()
Open "D:\folder\file.txt" As #lngFN
rsr.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName
Case "Title", "Address1", "Address2" ..
'and other text fiels where the field
name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name"
'text field where names differ
rsr.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsr.Fields(strFName).Value = CCur(strFValue)

Case 'and so on

End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsr.Fields("Item").Value & vbCRLF & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN






On Wed, 5 Nov 2003 03:00:47 -0800, "colin"

Hello John
Also because the file is an email we also get the
following lines in the text file hich i do not need in
the data base

thanks again

regards

Colin

From: (e-mail address removed)
Sent: 04 November 2003 22:16
To: (e-mail address removed)
Subject: Website Order

-----Original Message-----
Hello again

There is only one email per file.
All contain the same fields in the same order
Text in field fits on one line (thier is a possibility
that the item field could be longer).

Regards

Colin
-----Original Message-----
A little more information needed:

1) Is there just one email per file, or are there
more
than one?

2) If more than one, how do you tell where one ends
and
the next
begins? (is there some sort of "end of record"
signal?)

3) Do all the emails contain the same fields (Title:,
First Name: etc)
in the same order, or are some of them different?

4) Does the text in a field ever go over more than
one
line?


On Tue, 4 Nov 2003 14:21:45 -0800, "colin"

Hi John
Thank you for replying

This is a sample of my emails

Title: mr
First Name: an
Last Name: other
Address1: any street
Address2: any town
County: any county
Post Code:
Country: uk
Home Tel: 0121
Work Tel: 0121
Mobile: 077
Email: colin@

Card No: 1122222
Expiry Month: 01
Expiry Year: 03
Switch Issue: 1
Repair Ref:
Repair Amount:

Item: Braun Foil 235
Quantity: 1
Price: 8.50
Total: 8.5
Quantity Total: 1
Order Total: 8.5
No Mailing:

The emails are received by access so i presume are
txt
files
I would like to add to data base to produce address
labels also if possible an invoice.

-----Original Message-----
Hi Colin,

You won't be able to do this with Access's standard
import routines. By
the sound of it the messages are saved in one or
more
text files. To
import them automatically you'll need to write VBA
code
that opens the
the file(s) and reads them line by line, sorts out
which
line goes in
which field, and appends them to your table.

If you post back here with more information about
how
the emails are
stored and what you want to do with them we should
be
able to get you
started.

On Tue, 4 Nov 2003 13:02:50 -0800,
<[email protected]>
wrote:

I am trying to import text from emails which are
set
up
the same into access I want each email to be a
single
record but the import sees each line as a
different
record I would like each line to be a separate
field
is
this possible.


Please Help!!!

colin


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
The error you mention implies you do not have a reference set to DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.x
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
 
Hello again

I still cannot get this to work

I am getting compile error ,expected :end of Statement

what does this mean?

I have copied what i have done so far !
Am i missing something obvious?

regards

Colin


Sub ImportOneEmail(FileSpec As String)

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim lngPos As Long
Dim strLine As String
Dim strFName As String
Dim strFValue As String

Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("EMAIL")

lngFN = FreeFile()
Open FileSpec For Random As #lngFN
rsR.AddNew

Do While Not EOF(#lngFN)
Line Input #lngFN, strLine
lngPos = InStr(strLine, ": ")
If lngPos > 0 Then
'parse line into field name and value
strFName = Left(strLine, lngPos - 1)
strFValue = Mid(strLine, lngPos + 2)
Select Case strFName

Case "Title","First Name","Last
Name","Address1,"Address2","County","Post Code","Country",
'and other text fiels where the field name in
'the table is the same as in the textfile
rsR.Fields(strFName).Value = strFValue

Case "First Name",
'text field where names differ
rsR.Fields("FirstName").Value = strFValue

Case "Price", "Total"
'field type conversion
rsR.Fields(strFName).Value = CCur(strFValue)


End Select
Else 'no colon on line
If Len(strLine) > 0 And (strFName = "Item") Then
'this is a continuation of the Item field
rsR.Fields("Item").Value = _
rsR.Fields("Item").Value & vbCrLf & strLine
End If
Loop 'next line
rsR.Update
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
Close #lngFN

End Sub
 
Hello Doug

Thank you for your help but i am a complete beginner and
i do not understand

what are the 2 statements and how do a write the End if
and were do i put it

Thank you
Colin
 
I would assume that the missing End If should go immediately before the End
If that is there.

For each line you read from the file, you're checking to see whether or not
there's a colon in the line. If there is (the first If statement: If lngPos
0 Then), you're assigning what's after the colon to some field in your
Click to expand...
recordset. If there isn't (the Else), you have another If statement that
checks to make sure that you actually read something in, and that the last
line that did have a colon in it had "Item" in front of the colon. I believe
the missing End If should correspond to that If statement.

There are some potential errors in your code though. For example, you have
two different cases that include "First Name". The second one will never get
executed. As well, you don't save the value of the first line that does have
Item before the colon ("Item" isn't listed as one of the choices in the
Select Case statement)
 
Back
Top