Trying to extract info from an email.

[lazynewt]

Hi ive got a php script sending me information in the body of an email. The info is kind of in csv format

name,address,time,date

I am trying to figure out a way to import this into an existing database. Can anyone help me on this?

I can save multiple emails to a text file using outlook but this gives me csv info aswell as headers.....

 

[ErikSnoek]

This should work . It skips the headers by checking if the line has 4 by comma delimited fields.

Private Sub Import_Click()
    Dim objFilesystem As Object, objFile As Object
    Dim strLine() As String
    Dim rstTable As DAO.Recordset
 
    Set rstTable = CurrentDb.OpenRecordset("SELECT * FROM yourTable")
    Set objFilesystem = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFilesystem.OpenTextFile("c:\yourfile.txt", 1, 0)
 
    If Not IsNull(objFile) Then
        Do While Not objFile.AtEndOfStream
            strLine() = Split(objFile.ReadLine, ",")
            If UBound(strLine) = 3 Then 'read only when 4 by comma delimited fields are found (0 to 3)
                rstTable.AddNew
                rstTable!Name = strLine(0)
                rstTable!Address = strLine(1)
                rstTable!Time = strLine(2)
                rstTable!Date = strLine(3)
                rstTable.Update
            End If
        Loop
    End If
    rstTable.Close
    objFile.Close
End Sub

If you need any explanations, just ask.

 

[lazynewt]

Hi ErikSnoek thanks for the help . Whilst waiting for a reply here i decided to try handle things from the server side. I have modified the php script to output the form to a csv file. I am then hoping to send this to a folder on the network and use the code you have supplied.

The reason for this is that when ms access saves to a txt file it adds carriage returns to it which usually will make access throw a wobbly. if i could remove those it may help.

problems at the moment are.

a. do i output each "form fill out" to a seperate csv for importing.
Or
b. do i save multiple form data to one single csv and update this into the db hourly....
or
c. do i keep it sending to us in email format as csv then save all them as .txt files and strip the headers and carriage returns..

The problem with having multiple user details all going into one csv is figuring out how to get each new part of data to go on its own line and not just tag onto the end forming one huge line of info.
Option c would be great im just not sure how id remove the carriage returns...

i know ive probably confused you but if uv got any ideas id appreciate it. I can post the php code im using if you are knowledgeable in php.

thanks for the help.

 

[ErikSnoek]

The code I posted skips all lines that do not have 4 fields that are delimited with a comma. This means that both the headers and the empty lines are skipped. Shouldn't it work in your particular situation, if you use option C?

E.g.:
SomeEmail.txt

Sender: blabla                     <- Skipped
Receiver: blablabla                <- Skipped
                                   <- Skipped
:)                                 <- Skipped
                                   <- Skipped
name,adress,time,date              <- Processed
                                   <- Skipped
:(                                 <- Skipped
                                   <- Skipped
end of mail!                       <- Skipped

 

[lazynewt]

hey ErikSnoek Its the carriage returns that im concerned about. I Can see what you mean and i will try this tomorrow. My only concern was that it wouldnt be valid csv. Ill post back and let you know how i get on. This is what im up against though

this is the exact file

From:    dave
Sent:    19 May 2008 11:55
To:    info@myaddress.co.uk
Subject:    DEVeloping


Mr,steve,doe,26/02/83,fakedata
fakeemailaddy@gmail.com,imaginarydata5,imagdata6
,bob,keighley,01535,imaginary data,imaginarydata2
Mr,arthur,doe,26/02/83,imaginarydata3

From:    steve
Sent:    19 May 2008 11:56
To:    info@myaddress.co.uk
Subject:    DEVeloping2

Mr,steve,doe,26/02/83,
fakeemailaddy@gmail.com,imaginarydata5,imagdata6
,bob,keighley,01535,imaginary data,imaginarydata2
Mr,arthur,doe,26/02/83,imaginarydata3

as opposed to this

From:    dave
Sent:    19 May 2008 11:55
To:    info@myaddress.co.uk
Subject:    DEVeloping

Mr,bob,doe,26/02/83,fakeemailaddy@gmail.com,bob,keighley,01535 21534

From:    steve
 Sent:    19 May 2008 11:56
 To:    info@myaddress.co.uk
 Subject:    DEVeloping2

Mr,steve,doe,26/02/83,fakeemailaddy@gmail.com,bob,keighley,01535 21534

its not one continouse line and when you import into excel/access if its not a single line and contains carriage returns it throws a wobbly. Am i right in thinking your solution wont take the carriage returns and just extract the data with commas?

Thanks for the help i will try it tomorrow when i get in and let you know how i get on.

 

[ErikSnoek]

I wrote a new function that should work in your case. What you need to do is add "<data>" when the data starts and add "</data>" when the data end. Kinda XMLish .

Here's what happens:
1. All text gets read from the file
2. The code searches for <data> and then </data>
3. All text between <data> and </data> is saved in a string.
4. All carriage returns are removed, making one line of comma delimited data.
5. All fields are splitted and placed in an array.

Here's the function:

Private Sub Import_Click()
    Dim objFilesystem As Object, objFile As Object
    Dim strAllText As String, strFields() As String
    Dim lngPos As Long, lngEnd As Long, lngI As Long
    Dim rstTable As DAO.Recordset
    
    Set objFilesystem = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFilesystem.OpenTextFile("c:\yourfile.txt", 1, 0)
    
    If Not IsNull(objFile) Then
        strAllText = objFile.ReadAll
        lngPos = -1
        If Len(strAllText) > 0 Then
            Do While lngPos <> 0
                lngPos = InStr(lngPos + 2, strAllText, "<data>")
                If lngPos > 0 Then
                    Debug.Print "--Start data--"
                
                    lngEnd = InStr(lngPos, strAllText, "</data>")
                    strLine = Mid(strAllText, lngPos + Len("<data>"), lngEnd - (lngPos + Len("<data>")))
                    strLine = Replace(strLine, vbCrLf, "")
                    strFields = Split(strLine, ",")
                    For lngI = 0 To UBound(strFields)
                        Debug.Print "strFields(" & lngI & ") = " & strFields(lngI)
                    Next
                    
                    Debug.Print "--End data--"
                End If
            Loop
        End If
    End If
    objFile.Close
End Sub

Here's the file I used to test:

From:    dave
Sent:    19 May 2008 11:55
To:    [EMAIL="info@myaddress.co.uk"]info@myaddress.co.uk[/EMAIL]
Subject:    DEVeloping

<data>Mr,steve,doe,26/02/83,fakedata
[EMAIL="fakeemailaddy@gmail.com,imaginarydata5,imagdata6"]fakeemailaddy@gmail.com,imaginarydata5,imagdata6[/EMAIL]
,bob,keighley,01535,imaginary data,imaginarydata2
Mr,arthur,doe,26/02/83,imaginarydata3</data>
From:    steve
Sent:    19 May 2008 11:56
To:    [EMAIL="info@myaddress.co.uk"]info@myaddress.co.uk[/EMAIL]
Subject:    DEVeloping2
<data>Mr,steve,doe,26/02/83,
[EMAIL="fakeemailaddy@gmail.com,imaginarydata5,imagdata6"]fakeemailaddy@gmail.com,imaginarydata5,imagdata6[/EMAIL]
,bob,keighley,01535,imaginary data,imaginarydata2
Mr,arthur,doe,26/02/83,imaginarydata3</data>

And here's the output I get when running the code:

--Start data--
strFields(0) = Mr
strFields(1) = steve
strFields(2) = doe
strFields(3) = 26/02/83
strFields(4) = [EMAIL="fakedatafakeemailaddy@gmail.com"]fakedatafakeemailaddy@gmail.com[/EMAIL]
strFields(5) = imaginarydata5
strFields(6) = imagdata6
strFields(7) = bob
strFields(8) = keighley
strFields(9) = 01535
strFields(10) = imaginary data
strFields(11) = imaginarydata2Mr
strFields(12) = arthur
strFields(13) = doe
strFields(14) = 26/02/83
strFields(15) = imaginarydata3
--End data--
--Start data--
strFields(0) = Mr
strFields(1) = steve
strFields(2) = doe
strFields(3) = 26/02/83
strFields(4) = [EMAIL="fakeemailaddy@gmail.com"]fakeemailaddy@gmail.com[/EMAIL]
strFields(5) = imaginarydata5
strFields(6) = imagdata6
strFields(7) = bob
strFields(8) = keighley
strFields(9) = 01535
strFields(10) = imaginary data
strFields(11) = imaginarydata2Mr
strFields(12) = arthur
strFields(13) = doe
strFields(14) = 26/02/83
strFields(15) = imaginarydata3
--End data--

As you can see, the function currently outputs all fields in the array. You can use this information to edit the function to import the data to a table. See my previous function for an example on how to do that.

Hope this helps

 

[lazynewt]

ahh i c

Thanks ErikSnoek. works like a charm. I just added the [data] tags to the php code and all is well. Still alot to do manually but im sure i can figure out how to make everything done automatically. Youve been a real help especially with the explenation. i know this code will come in handy on more than this occasion.

thanks m8