From Mail to Filemaker?
December 17, 2006 9:09 AM Subscribe
How can I automagically parse the oddly-formatted text from the body of an email so that Filemaker can use it?
I am using Mal's E-commerce as my shopping cart software. It works very well, especially since it's free, but I'm bummed out that I have to copy and paste all the information field by field into my Filemaker database.
Here is a sample of the emails I get from the software:
Date : 14 Dec 2006 - 09:37
Shopper Id : 1234567
IP number : 123.234.56.78
For payment by: Paypal
Product : Quantity : Price
widget-01 : 1 : 1.00
widget-02 : 1 : 1.00
gewgaw-03 : 1 : 1.00
whatsit-01 : 2 : 1.00
Voucher : -0.00
Discount : -0.00
Subtotal : 5.00
Shipping : 3.00
Tax : 0.00
TOTAL : 8.00
Ship zone : 1
Invoice to:
Joe Schmoe
123 Main St.
Anytown
NC
12345
US
Tel :
Fax :
Email : jschmo@hotmail.com
What's the best way to massage the data so that Filemaker can correctly import the relevant information? The fact that it's all separated with colons and carriage returns seems to baffle my poor old Filemaker 5.
Since I'm copying and pasting right now, I have the address info as one block, but it's possible to have the email break the lines out with headers (Address: City: State: etc.)
I am using Mal's E-commerce as my shopping cart software. It works very well, especially since it's free, but I'm bummed out that I have to copy and paste all the information field by field into my Filemaker database.
Here is a sample of the emails I get from the software:
Date : 14 Dec 2006 - 09:37
Shopper Id : 1234567
IP number : 123.234.56.78
For payment by: Paypal
Product : Quantity : Price
widget-01 : 1 : 1.00
widget-02 : 1 : 1.00
gewgaw-03 : 1 : 1.00
whatsit-01 : 2 : 1.00
Voucher : -0.00
Discount : -0.00
Subtotal : 5.00
Shipping : 3.00
Tax : 0.00
TOTAL : 8.00
Ship zone : 1
Invoice to:
Joe Schmoe
123 Main St.
Anytown
NC
12345
US
Tel :
Fax :
Email : jschmo@hotmail.com
What's the best way to massage the data so that Filemaker can correctly import the relevant information? The fact that it's all separated with colons and carriage returns seems to baffle my poor old Filemaker 5.
Since I'm copying and pasting right now, I have the address info as one block, but it's possible to have the email break the lines out with headers (Address: City: State: etc.)
That is a very straight-forward text-format. A very basic parser could understand it, and generate appropriate database queries to add it to FileMaker.
The worst part as you've noticed is the address of the invoicee -- there is no headers for the city, etc. But it may not be bad. I bet it always put the country on line 5 of the address, the zip on 4, the state on 3, and the address/town on lines 1 and 2. A parser would just have to find the address section, then extract fields based on line number relative to that starting point.
I'm a specialist in compilers and parsers, but I know nothing about filemaker, so it would take me some time to create a solution. Good luck!
posted by clord at 9:39 AM on December 17, 2006
The worst part as you've noticed is the address of the invoicee -- there is no headers for the city, etc. But it may not be bad. I bet it always put the country on line 5 of the address, the zip on 4, the state on 3, and the address/town on lines 1 and 2. A parser would just have to find the address section, then extract fields based on line number relative to that starting point.
I'm a specialist in compilers and parsers, but I know nothing about filemaker, so it would take me some time to create a solution. Good luck!
posted by clord at 9:39 AM on December 17, 2006
Response by poster: I can definitely change it to add headers for the address sections, but other than that it's not customizable, as far as I can tell.
Filemaker can kinda sorta see it if I try to import the file, but it treats every carriage return as a new record. And the whole product:quantity:price bit is totally ungrokkable by Filemaker, as far as I can tell.
posted by bink at 9:46 AM on December 17, 2006
Filemaker can kinda sorta see it if I try to import the file, but it treats every carriage return as a new record. And the whole product:quantity:price bit is totally ungrokkable by Filemaker, as far as I can tell.
posted by bink at 9:46 AM on December 17, 2006
How do you currently represent the variable length Product/Quantity/Price information in your database? Is it a separate table of order items that each need to be linked back to an order record? If so, does FileMaker support importing related data from two CSV or tab-delimited text files into the two appropriate tables?
posted by RichardP at 9:52 AM on December 17, 2006
posted by RichardP at 9:52 AM on December 17, 2006
Hrm... "
Is it secure?
Yes. These days there is no technical reason why your credit card information shouldn't be secure but a lot still depends on the management systems the merchant uses. For example there is absolutely no point in using a SSL secure server to collect credit card details as I do at Mal's e-commerce if you then later on post it over the Internet using unsecured e-mail.
When one of your customers places an order you will be notified by e-mail. That e-mail will contain details of their order excluding the credit card number which you collect from the secure section in Admin.
I use exactly the same process for checks as the same security risks exist although for some strange reason not in most peoples minds."
posted by prodevel at 10:10 AM on December 17, 2006
Is it secure?
Yes. These days there is no technical reason why your credit card information shouldn't be secure but a lot still depends on the management systems the merchant uses. For example there is absolutely no point in using a SSL secure server to collect credit card details as I do at Mal's e-commerce if you then later on post it over the Internet using unsecured e-mail.
When one of your customers places an order you will be notified by e-mail. That e-mail will contain details of their order excluding the credit card number which you collect from the secure section in Admin.
I use exactly the same process for checks as the same security risks exist although for some strange reason not in most peoples minds."
posted by prodevel at 10:10 AM on December 17, 2006
Response by poster: The product/quantity/price info isn't in any database. I'm making a new one from scratch. Honestly, I don't really need that information as long as I know the total amount of the sale ("subtotal"), but I just figure one should collect as much information as one can. I'm perfectly happy to just skip that whole section, though, if it's more trouble than it's worth. Right now I'm thinking it might be.
Right now I manually copy and paste most of the information into an Excel spreadsheet. This works, more or less, but it's kind of a pain to keep up with and I tend not to do it until just before my sales taxes are due. I'd like to make the process a little bit easier so I actually do it.
As for security, all my credit card transactions are done through PayPal. No CC numbers come through emails. I don't take check info online either; if they want to pay by check they have to just snail mail it.
posted by bink at 10:44 AM on December 17, 2006
Right now I manually copy and paste most of the information into an Excel spreadsheet. This works, more or less, but it's kind of a pain to keep up with and I tend not to do it until just before my sales taxes are due. I'd like to make the process a little bit easier so I actually do it.
As for security, all my credit card transactions are done through PayPal. No CC numbers come through emails. I don't take check info online either; if they want to pay by check they have to just snail mail it.
posted by bink at 10:44 AM on December 17, 2006
Any halfway competent programmer could knock out a filter to do this for you in very little time, but it seems to me from a cursory look at the Mal's e-commerce website that the mOrders 3 free add-on probably does it already.
posted by flabdablet at 4:50 PM on December 17, 2006
posted by flabdablet at 4:50 PM on December 17, 2006
Response by poster: Ah. I didn't mention that I'm on a Mac, did I. Wa-wa- waaah.
posted by bink at 7:43 PM on December 17, 2006
posted by bink at 7:43 PM on December 17, 2006
Best answer: On a Mac? That means you've got all the unix text processing tools. Awk will eat this problem for breakfast.
What's the most convenient intermediate form for your data? Can Filemaker handle CSV files? Most things can, and it's a nice simple format.
Also, which mail client are you using, and do you know what format it keeps your mails in? If it's mbox format or something similar, and if you can tell me what's in your database tables, I could easily whip you up a little awk filter that would search your entire mailbox for e-commerce order confirmations and massage them into one or more appropriate CSV's.
posted by flabdablet at 9:05 PM on December 17, 2006
What's the most convenient intermediate form for your data? Can Filemaker handle CSV files? Most things can, and it's a nice simple format.
Also, which mail client are you using, and do you know what format it keeps your mails in? If it's mbox format or something similar, and if you can tell me what's in your database tables, I could easily whip you up a little awk filter that would search your entire mailbox for e-commerce order confirmations and massage them into one or more appropriate CSV's.
posted by flabdablet at 9:05 PM on December 17, 2006
Response by poster: I am using Apple's Mail, no idea about how it stores individual messages. I know it uses mboxes but I thought that was per mailbox. In any case, it's no trouble for me to copy the text into a file.
Filemaker will import comma-separated text files. I have noticed that it doesn't do anything with the colons, though; when I import a file (after copying the email to a text file, stripping out all the carriage returns and replacing them with tabs, deleting all the irrelevant entries, moving the order of the Filemaker fields to match them up with the text file, etc.) the data in the Date field comes through as Date:12/14/2006 and that whole order info section comes through as a bunch of separate lines.
The fields in the database right now are:
Date
Order Number
Subtotal
Postage
Tax
Name
Address
City
State
Zip
Country
Email
Order detail
plus a few other fields with values that I have to enter manually; they're not in the emails.
I definitely do have awk on this machine, but not a clue how to use it just yet.
posted by bink at 8:26 AM on December 18, 2006
Filemaker will import comma-separated text files. I have noticed that it doesn't do anything with the colons, though; when I import a file (after copying the email to a text file, stripping out all the carriage returns and replacing them with tabs, deleting all the irrelevant entries, moving the order of the Filemaker fields to match them up with the text file, etc.) the data in the Date field comes through as Date:12/14/2006 and that whole order info section comes through as a bunch of separate lines.
The fields in the database right now are:
Date
Order Number
Subtotal
Postage
Tax
Name
Address
City
State
Zip
Country
Order detail
plus a few other fields with values that I have to enter manually; they're not in the emails.
I definitely do have awk on this machine, but not a clue how to use it just yet.
posted by bink at 8:26 AM on December 18, 2006
from what I remember the most oft used part of awk is "piping" stuff to it.
e.g.: ls -ls | awk "{print $2, $4}"
or some such usage :)
in unix, if you want a script to handle your mail you can (in hpux/solaris, etc) put a ".forward" file in the user's home directory to "pipe" the email to a script (| /export/home/biz), although the input must be handled very carefully. I've used perl in the past w/this, while including the -T (TAINT-ed vars) include directive and special handling of the data so as not to encourage hacking. www.perlmonks.com has plenty of info on this.
Mail often has nice headers to signify bodies of messages etc., but it can still be tricky w/the number of mailers out there - at least it was 2/3 yrs. ago. I've parsed millions of mail tho and it's all fairly similar...
posted by prodevel at 3:48 AM on December 21, 2006
e.g.: ls -ls | awk "{print $2, $4}"
or some such usage :)
in unix, if you want a script to handle your mail you can (in hpux/solaris, etc) put a ".forward" file in the user's home directory to "pipe" the email to a script (| /export/home/biz), although the input must be handled very carefully. I've used perl in the past w/this, while including the -T (TAINT-ed vars) include directive and special handling of the data so as not to encourage hacking. www.perlmonks.com has plenty of info on this.
Mail often has nice headers to signify bodies of messages etc., but it can still be tricky w/the number of mailers out there - at least it was 2/3 yrs. ago. I've parsed millions of mail tho and it's all fairly similar...
posted by prodevel at 3:48 AM on December 21, 2006
Response by poster: Epilogue for anyone who reads this in the future:
Flabdablet worked some magic, created an awk script, and I am rather amazed, extremely grateful, a little bit better informed, and a lot more organized for the new year.
posted by bink at 2:10 PM on January 5, 2007
Flabdablet worked some magic, created an awk script, and I am rather amazed, extremely grateful, a little bit better informed, and a lot more organized for the new year.
posted by bink at 2:10 PM on January 5, 2007
This thread is closed to new comments.
You could write an intermediate parser of some kind, but it might be more productive to generate a better formed response that you can parse into filemaker.
Email me if you want to discuss it more (my username, at, vtwireless, dot, com)
posted by SirStan at 9:28 AM on December 17, 2006