Importing data from a web page source code

[cbsteven]

My database is designed to keep a local copy of orders that are placed via our ecommerce web site. A third party controls our site so we don't have much say about the data format, so basically what I do is this. I type the order number into a field in a new record, then start a script, which then launches an applescript. The applescript will use IE5 to load the webpage of that specific order, grab the source code, and save it to a file. Then it will have BBEdit open the file and parse it down into manageable chunks. The HTMl is not very pretty so it requires a lot of complicated regular expressions to break down.

Then the script will throw the chunks that BBEdit parsed out into the database.

Now this works, but it is not pretty to look at IE5 pulling up a window then BBEdit launching and opening several of its own windows. Even though the script closes all the windows behind it, it is not a very slick implementation, but i can't figure out a better way, since there don't seem to be any FMP plugins that do regular expressions.

This needs to work on both OSX and OS9, using FMP6. Any suggestions?

[BobWeaver]

Filemaker may not do regular expressions, but its text functions can be very powerful if used properly. I've created Filemaker scripts to parse some phenomenally ugly text into useful data. At least that takes BBedit out of the loop, but you still need to download the webpage somehow.

[kennedy]

I'm with you on the lack of RegExps in FileMaker. Huge weakness in my mind... so many things are dreadfully difficult in FMP, when they are dirt-easy with the simplest of tools (that handle RegExps).

Anyway, if you can avoid the need for this on OS9... you could easily script it using Unix utilities (like 'awk/gawk' or 'perl')... and that would get rid of the ugliness. There may be a version of those for OS9... not sure.

FWIW.

[cbsteven]

Thanks for your reply. I'm going to give it a shot and see if I can do this using FMP text functions, although I think it will be a huge headache to maintain if the backend provider every changes the format. I did a test and I'd like to run it by you guys and you tell me if there is a more efficient way.

The function at the bottom will convert the field Raw Data, which contains the HTML snippet of:

<tr>

<td vAlign="top" nowrap><b>Company:</b></td>

<td vAlign="top"></td>

</tr>

<tr>

<td vAlign="top" nowrap><b>Name:</b></td>

<td vAlign="top"> John Smith </td>

</tr>

<tr>

<td vAlign="top" nowrap><b>Address:</b></td>

<td vAlign="top">

1000 Alameda

<br>de las Pulgas

and will convert it to the text " John Smith".

Right(

Middle(Raw Data,

Position(Raw Data, "<b>Name:</b>", 1, 1),

Position(Raw Data, "</td>",

Position(Raw Data, "<b>Name:</b>", 1, 1), 2) - Position(Raw Data, "<b>Name:</b>", 1, 1))

, Length(

Middle(Raw Data,

Position(Raw Data, "<b>Name:</b>", 1, 1),

Position(Raw Data, "</td>",

Position(Raw Data, "<b>Name:</b>", 1, 1), 2) - Position(Raw Data, "<b>Name:</b>", 1, 1)))

- Position(

Middle(Raw Data,

Position(Raw Data, "<b>Name:</b>", 1, 1),

Position(Raw Data, "</td>",

Position(Raw Data, "<b>Name:</b>", 1, 1), 2) - Position(Raw Data, "<b>Name:</b>", 1, 1))

, ">", Length(

Middle(Raw Data,

Position(Raw Data, "<b>Name:</b>", 1, 1),

Position(Raw Data, "</td>",

Position(Raw Data, "<b>Name:</b>", 1, 1), 2) - Position(Raw Data, "<b>Name:</b>", 1, 1))

), -1))

[BobWeaver]

Without analyzing your function in detail, it looks like you are on the right track. One tip that you may find useful: Make use of the substitute function to help clean things up a bit. For example, have a look at what you get when you run your sample of HTML through this formula:

Substitute(Substitute(Substitute(Substitute(Substitute(

Substitute(Substitute(Substitute(Substitute(Substitute(RawText,

"