importing text

C

colin

I have a problem importing text from an email into access
i have i have via this news group which is below but i do
not know how to use the code below .


Can you please help!!!

regards

colin

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.
.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top