Parse Email Data into multiple Fields?

[Otis]

Hi all, I hope this is the correct area for this.

I have a database where potential clients can email me for a quote. The emails that are generated from the quote are just raw text and look like this:

---

Name:..... Joe Smith

Title:..... Managing Director

Company:..... Acme Group, LLC

Address:..... 1234 Main Street

City:..... Los Angeles

State:..... CA

Zip-Code:..... 90049

Country:..... United States

Email:..... [email protected]

Phone Number:..... 123-456-7890

Additional Comments:

Please call me immediately.

---

I usually MANUALLY copy/paste the info from the email into the various fields of my FileMaker database.

For example, some of my corresponding FileMaker fields are "First Name", "Last Name", "Title", "Company" and so on.

I don't want to host this database online, and thus, this process has worked for me to a certain extent.

However, I was wondering if I could just create a new field in FileMaker where I can copy and paste the ENTIRE email block above once, and then have a FileMaker calculation field or script (or both) to automatically copy/paste (or parse) this info into the appropriate fields.

The email data will always follow the same format. The only oddity to the above email data I receive from my online form is that not everyone fills out every field. Thus, if they don't fill out a field, the entire line of text will not be included. (For example, it won't be blank, there will just not be a line item listing for "Company" at all if they didn't fill it out, etc.)

I'm using FileMaker Pro Advanced 11 for Mac and Mac OS 10.6.6 Snow Leopard if that helps.

I'm a bit stuck how to get started with this and any help is appreciated.

[bcooney]

There's no "magic" to it. With any consistency, it's just a parsing exercise. Some of the text custom functions on Brian Dunning's site might make quicker work of it for you.

[comment]

Try something like =

Let ( [

prefix = "Company: " ;

pos = Position ( ¶ & RawText ; ¶ & prefix ; 1 ; 1 ) ;

start = pos + Length ( prefix ) ; 

end = Position ( RawText & ¶ ; ¶ ; start ; 1 ) 

] ;

Case ( pos ; Middle ( RawText ; start ; end - start ) )

)

To extract other data, change the prefix parameter.

This assumes a colon and a space separating the prefix and the data - I'm not sure what the dots in your post mean.

[Otis]

Try something like =

Let ( [

prefix = "Company: " ;

pos = Position ( ¶ & RawText ; ¶ & prefix ; 1 ; 1 ) ;

start = pos + Length ( prefix ) ; 

end = Position ( RawText & ¶ ; ¶ ; start ; 1 ) 

] ;

Case ( pos ; Middle ( RawText ; start ; end - start ) )

)

To extract other data, change the prefix parameter.

This assumes a colon and a space separating the prefix and the data - I'm not sure what the dots in your post mean.

Thank you both for your posts.

The dots in the post are exactly as they appear in the email I receive from my quote email.

There is a colon, then 4 dots, and then a space before the actual data is displayed. This is where I'm having trouble.

[comment]

There is a colon, then 4 dots, and then a space before the actual data

Well, then make the prefix = "Company:..... " etc. BTW, there are 5 dots in your original post, not 4.

[Otis]

Well, then make the prefix = "Company:..... " etc. BTW, there are 5 dots in your original post, not 4.

Thanks for the heads up. I will definitely do that.

[Otis]

comment, just wanted to tank you again for the push in the right direction.

I used your example to create separate calculation field for each input, then a script to essentially copy/paste the raw text from the calc fields into my main fields.

Worked perfectly. Thanks again.

[comment]

separate calculation field for each input, then a script to essentially copy/paste the raw text from the calc fields into my main fields.

That's not necessary. You can either use the calculation fields themselves, or - if you want the data to be editable - have the script populate the fields directly, using the same calculations.

[Otis]

Thanks for the info, but I already did it and it's working fine.

Also, I had an issue with the text formatting when I tried to have the calc fields populate the fields. It would retain the formatting from the original email which I didn't want. The copy/paste is likely archaic to some degree as you pointed out, but it properly pasted the text correctly without formatting issues. Yes, I know I can option-paste in the original email text to lose formatting from the start, but it's not ideal for me. This way worked great.

Thanks again.

[comment]

You should not use copy/paste in scripts. Instead, try:

Set Field [ YourTable::Name ; Let ( [ prefix = "Name:.... " ; ... ]

Set Field [ YourTable::Company ; Let ( [ prefix = "Company:.... " ; ... ]

and so on.

[Otis]

As I mentioned, I did exactly that at first and kept the original text formatting which is something I didn't want.

The way I got it to work was with copy/paste and making sure the check box for "Paste Without Style" was checked.

[bcooney]

To remove formatting, wrap your result in TextFormatRemove ( ).*

*Not sure if Set Field retains formatting?

Edited July 19, 2011 by bcooney
Set Field and Formatting Question

[Otis]

To remove formatting, wrap your result in TextFormatRemove ( ).*

*Not sure if Set Field retains formatting?

Thanks for this tip.

This worked perfectly with the Set Field command and I've revised my script to use this instead of copy/paste to get it working properly.

(The standard Set Field script step retained formatting.)

Thanks to all for the help with this. :)