Parse --moved from Introductions

[mike60smart]

Hi Everyone

This is my first question here and I look forward to working with the community.

I have a linked Folder to Outlook which displays EMails received.

The Contents field is a Memo Data Type

Is it possible to Parse the contents of an EMail into a record in a table named "tblDataOutputGroup"

In the linked table Each Paragraph has the required Field Names followed by a Colon ie

From:
Subject: etc...

Any help appreciated.

 

[isladogs]

Re: Parse

Hi Mike and welcome to AWF

I know you are very active on the Utter Access forum with over 12000 posts to your name so its good of you to drop in .

By pure chance I had just read your thread on this topic at UA and rather surprised that you didn't provide the cross-posting link especially as some progress has been made in that thread ...http://www.utteraccess.com/forum/index.php?showtopic=2044899&hl=

I'm sure you are aware of the etiquette involved.

Anyway, I very much hope you will get the answer to this question here or at UA and that you will become a regular contributor here as well

 

[mike60smart]

Re: Parse

Hi Colin

Many thanks for the welcome.

I tried to add a couple of attachments and a Link to UA but was informed that I had to have made 10 posts for this facility to be available to me.

 

[isladogs]

Re: Parse

Hi again

You can upload files now if you zip them.

If its ok with you I'll ask a site moderator to move this thread to Modules and VBA.

 

[mike60smart]

Re: Parse

Hi Colin

Yes please. My mistake

Many thanks

 

[mike60smart]

Hi Colin

I have managed to get a step further with what I require.

I used the following in a query to replace the paragraph marks at the end of each paragraph with the pipe character "|"

It now produces the following string:-

From: Debbie Whellans | Subject: Open Course Booking - Good Practice in Infection Control - 11/10/2017 - on account | | Message Body: | Contact Name: Debbie Whellans | Phone: 01995813313 | | Organisation: Cd Nursery | | Organisation Address: | 1A Main Street | | Town/City: Bolton | Postcode: B32 9AY | | Billing Address: | | Town/City: | Postcode: | | Course Title: Good Practice in Infection Control | | Course Date: 11th October 2017 | | Number of Participants: 1 | | Participant Names: | Ashley Brown | | PO Number: | | Additional Comments: | dietary requirements etc.

Is there a way to parse the text to the right of the colon into a field using the string to the Left of the Colon??

Please note I had to remove the EMail data as I am not allowed to post Links until I have completed 10 posts

The table I am trying to Parse the data to is as follows:-

View attachment Grouop Table.zip

The linked Folder displays the email as follows:-

View attachment Linked data.zip

 

[isladogs]

Just had a look at the screenshots and have an idea I'd like to test.

Could you zip and upload a stripped down copy of your db with those 2 tables and a couple of records. Modify anything confidential.

I think you'll need to change the MessageBody field to Memo/LongText.
Also where is the Importance field stored in your import table

Just out of interest, why do you want to parse it?
And finally which version of Access & Excel do you have?

 

[mike60smart]

Hi Colin

I can't send a copy of the Db.

The linked Data Zip file that I uploaded shows the email record in a Linked folder from Outlook.

I am using Access 2010 and Excel 2010

 

[isladogs]

Can you copy the record into another table or into Excel
OR convert the table to a local table.
I just want something I can use to test my idea without creating my own data

Version 2010 is perfect

 

[mike60smart]

Hi Colin

My original post in UA is shown in the attachment

View attachment Link.zip

 

[mike60smart]

Hi Colin

OK Will do

 

[mike60smart]

Hi Colin

Here with the 2 tables

View attachment Test.zip

 

[isladogs]

OK got it thanks.
Starting to look at it now.
You won't need to zip after 10 posts

Please can you let me know here if any progress on this happens at UA whilst i'm working on this here

 

[isladogs]

Mike

Please clarify:
Is the version with pipe marks (|) the original & the version with line returns the replacement (or vice versa)
Presumably I can use whichever is easiest to parse i.e. probably the | marks

I've made significant headway with this but am having trouble with the double || marks between some items (and the equivalent blank lines in the other version)

Am I correct in assuming these are fields that will sometimes be populated but aren't in your example?
Do you have an example where ALL fields are populated?

 

[moke123]

ridders,

heres one idea i had and posted at UA. Maybe it'll give you an idea.

 

[isladogs]

Hi Moke

I hadn't realised you were still working on this.
Should have left you to it.

I've been doing a similar idea but using Excel Power Query to parse the data then reimporting to Access

Not knowing the answers to the questions I asked in post 14 slowed me down a lot

Some 2 hours later & after several false starts, I'd almost finished when I saw this post!

Mine will work but your approach is definitely better for 2 reasons:
1. All done in Access
2. Much less code to achieve the end result

I think Mike should be very happy with your solution

 

[mike60smart]

Hi Colin & moke

Moke's example works just perfect.

Many many thanks for the help with this.

 

[mike60smart]

Hi Moke

Spoke too soon.

In your example Db the values after Postcode are being Parsed into the wrong fields.

See the attached screenshot:-



Thoughts??

 

[mike60smart]

Hi Moke

I also tried adding a Billing Address to see if that made any difference - no joy:-

 

[Gasman]

Mike,
PMFJI, but I was ineterested in this concept.

You need to match the rep string with the data.?

A few fields are missing.

    RepString = "Subject:,Message Body:,Contact Name:,Email:,Phone:,Organization:,Organization Address:,Town/City:,Postcode:,Billing Address:,[COLOR=Red]Town/City:,Postcode[/COLOR]:,Course Title:,Course Date:," & _
                "Number of Participants:,Participant Names:,PO Number:,Additional Comments:,--"

Then you would need to alter the field assignments..

HTH (and I'm not leading you astray)