Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tabulating data from emails (Office 2002 Suite)

    Hi,
    I recently ran some polls for a company and recieved the data as 855 emails. I was in a rush to set up the voting system so i didnt have time to work out the best way to format the data, so i now have 855 emails with the "Body" as follows:

    "pmanning@optusnet.com.au"

    "Emily Manning"

    "55511804"

    "1169-364 "

    I would like to make each email a record, and put each of these values into a field. But the formatting makes it hard.
    Any tips?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    If you are using Outlook, you can import or link an entire Outlook folder. You can then use a query or some code to extract the desired information from the Contents field of the imported/linked table. You would use the InStr function repeatedly to look for quotes. Note: a quote is used to delimit a string in expressions and in code; to use a quote as a character, use Chr(34), or double the quote within a string.

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    Hi,
    I have previously only used querys to sort records rather than extract vales from fields, are there any pointers you can give me to get me started
    Much appreciated
    Adrian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    In the example you gave in the first post in this thread, there were four pieces of information in the body of the e-mail:
    <hr>"pmanning@optusnet.com.au"

    "Emily Manning"

    "55511804"

    "1169-364 "<hr>
    Could you please tell me:

    1. Will there always be four pieces of information, or can there be more or less in one e-mail?
    2. Is the order of the information always the same?
    3. Are the quotes included in the e-mail?

    Thanks.

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    1. Yes there will only always be four, though if they havent filled out a field just the brackets will be there, like: ""
    2. Yes the order and position is always the same.
    3. Yes the quotes are inluded in the email.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    Here you go. Don't get scared, it ain't pretty, but I think it works.

    I will post the SQL for a query, and a screenshot of part of the query grid. for illustration purposes, the table is named tblMails and the contents are in the field named, uhh, Contents. First, I create eight calculated fields P1 to P8 that return the position within the field of the quotes. The expressions are:

    P1: InStr([Contents],Chr(34))
    P2: InStr([P1]+1,[Contents],Chr(34))
    ...
    P8: InStr([P7]+1,[Contents],Chr(34))

    Then come the four fields you need; they extract the parts between the quotes, using the values of P1 to P8. I have named them F1 to F4 here, but you should use meaningful names such as E_mail, FullName etc. The expressions are:

    F1: Trim(Mid([Contents],[P1]+1,[P2]-[P1]-1))
    ...
    F4: Trim(Mid([Contents],[P7]+1,[P8]-[P7]-1))

    Here is the complete SQL:

    SELECT tblMails.Contents, InStr([Contents],Chr(34)) AS P1, InStr([P1]+1,[Contents],Chr(34)) AS P2, InStr([P2]+1,[Contents],Chr(34)) AS P3, InStr([P3]+1,[Contents],Chr(34)) AS P4, InStr([P4]+1,[Contents],Chr(34)) AS P5, InStr([P5]+1,[Contents],Chr(34)) AS P6, InStr([P6]+1,[Contents],Chr(34)) AS P7, InStr([P7]+1,[Contents],Chr(34)) AS P8, Trim(Mid([Contents],[P1]+1,[P2]-[P1]-1)) AS F1, Trim(Mid([Contents],[P3]+1,[P4]-[P3]-1)) AS F2, Trim(Mid([Contents],[P5]+1,[P6]-[P5]-1)) AS F3, Trim(Mid([Contents],[P7]+1,[P8]-[P7]-1)) AS F4
    FROM tblMails;
    Attached Images Attached Images
    • File Type: png x.png (4.2 KB, 0 views)

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    Hey thanks a lot! It worked a dream.
    And i actually understood what was going on!

    Given the there is not going to be any new data... should i work from the query as it is or should i some how convert it to a table?
    I tried to do a "find duplicates" wizard. but it came up with the attached error message.

    Thanks for your EXCELLENT help!
    (ive started some introduction-to-SQL tutorials to better understand this stuff in general)
    Adrian

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    If there are not going to be new data, I would definitely create a new table. You can create a new query based on the one you have now, include the fields you need, and turn it into a make-table query.

  9. #9
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tabulating data from emails (Office 2002 Suite)

    Sweet, thanks heaps!
    Adrian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •