Extract and paste different data from a single field

[carruthid]

Hi all,

I am new to the forum and appreciate any help.

I am working with a data base import. After import, I need to extract different data within a single field. Here is the example:

1995 Ford Escort Wagon, new transmission, tags, $900 obo. Groveland,CA. Call 209-922-0000

The field is called "description" I have been able to cut the first line prior to the comma, but can't seem to get any of the rest with any accuracy. The order changes with each record except for first line.

I would like to be able to extract the following:

First Line, Dollars, City, State and paste into separate fields.

I apologize in advance if this is a rudimentary question.

[comment]

It's easy to parse out the field to separate tokens based on the placement of commas or such. You could also look for the $ character and get the subsequent digits. But I don't see how you're going to make a computer realize that "Groveland" is a city while "new transmission" is not.

[carruthid]

Thank you for the reply,

I have some control over the format of the data used so I could say within the originating system that "Title" falls within the first comma, "Desc" would be located between the first and second comma, "Price" follows a dollar sign, "City" is second word from the last, and "State" would be the last word.

Could you help me with an example of how this might look as a calculation to take the first two comma separated values, the dollar sign and following values and the last two words in the description field?

For example if I changed the output of the originating system to:

1995 Ford Escort Wagon, new transmission, tags, $900 obo. Call 209-922-0000, Groveland, CA

The output needed would be:

Field One: 1995 Ford Escort Wagon

Field Two: new transmission

Field Three: $900

Field Four: Groveland

Field Five: CA

[comment]

If you have control of the original data, why not split it into individual fields right at the source?

[carruthid]

I wish that were so. I do not have that much control. I can only control the order in which it is entered.

[comment]

Well, it looks like an exercise in hiding the needle in a haystack, then trying to find it...

Anyway, try these:

Field One =

GetValue ( Substitute ( Textfield ; ", " ; ¶ ) ; 1 )

Field Two =

GetValue ( Substitute ( Textfield ; ", " ; ¶ ) ; 2 )

Field Three =

Let ( [

pos = Position ( Textfield ; "$" ; 1 ; 1 ) ;

words = Right ( Textfield ; Length ( Textfield ) - pos )

] ;

LeftWords ( words ; 1 )

)

Field Four =

MiddleWords ( Textfield ; WordCount ( Textfield ) - 1 ; 1 )

Field Five =

RightWords ( Textfield ; 1 )

[carruthid]

Yes I understand. Seems a little clunky to me too but it works.

Just a couple follow up questions...Is there a way to include the dollar sign as part of Field Three?

Is it possible that if it doesn't see a dollar sign it would return nothing?

Thanks for your help.

[comment]

Is there a way to include the dollar sign

It would be best, IMHO, to set the result type of the field to Number and format it as currency.

Is it possible that if it doesn't see a dollar sign it would return nothing?

Change:

LeftWords ( words ; 1 )

to;

Case ( pos ; LeftWords ( words ; 1 ) )

[carruthid]

Sorry, I know how to do the dollar sign...Duh!

Thanks

[carruthid]

Everything working great. Thank you helping this filemaker newbie.

[carruthid]

Is it possible to add a variable to look at if the first delimiter is a comma, a colon, or a period?

Let ( [ti = AEFinder::Entire Import; Comma = "," ; pos = Position ( ti; Comma; 1; 1)]; Left ( ti; pos-1))

[comment]

I am not sure i understand your question.

[bcooney]

Automatic message

This topic has been moved from "Database Schema & Business Logic → Custom Functions" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".

[carruthid]

I receive data from outside sources that are similar in content but each supplier uses different styles. Unfortunately they won't change.

I am attempting to strip the first text prior to a comma, period or colon.

So I need a script to look at each variable (comma, period or colon) and strip the first line of text prior to the delimiter and place in another field.

Would I be writing something like this? How would I write the OR statement.

Go to Record/Request/Page [ First ]

Set Field [ Let ( [ti = AEFinder::Entire Import; Comma = "," ; pos = Position ( ti; Comma; 1; 1)]; Left ( ti; pos-1)) ]

Set Field [ Let ( [ti = AEFinder::Entire Import; Colon = ":" ; pos = Position ( ti; Colon; 1; 1)]; Left ( ti; pos-1)) ]

Set Field [ Let ( [ti = AEFinder::Entire Import; Period = "." ; pos = Position ( ti; Period; 1; 1)]; Left ( ti; pos-1)) ]

Here are the three common ways data arrives - note the delimiter after title line.

1 - 1995 Ford Escort Wagon: new transmission, tags, $900 obo. Call 209-922-0000, Groveland, CA

2 - 1995 Ford Escort Wagon, new transmission, tags, $900 obo. Call 209-922-0000, Groveland, CA

3 - 1995 Ford Escort Wagon. new transmission, tags, $900 obo. Call 209-922-0000, Groveland, CA

[comment]

What if the delimiter is supposed to be comma, but the title contains a period?

[carruthid]

Yes that would be a problem. However, I think it should look at the first delimiter only.

[comment]

Well, then:

GetValue ( Substitute ( Textfield ; [ "," ; ¶ ] ; [ ":" ; ¶ ] ; [ "." ; ¶ ] ) ; 1 )

should work fine, I think.

[carruthid]

Yeah, that works. Thanks! You're a lifesaver.

[carruthid]

I am working on another issue now but unsure how to make this happen.

We create a path for each imported record like: http://www.adman.com/picture/344264.jpg and the last part is a picture id based on a unique value for each record. In this case 344264.

Is it possible to have a script look at a folder that has picture in it with the picture ids and if the picture exists then it pastes the url in to the photo field. (http://www.adman.com/picture/344264.jpg). If it doesn't exist, it would paste another url? (http://www.adman.com/picture/picture_not_available.jpg)

urls are phoney.

[comment]

have a script look at a folder that has picture in it

Is this a local folder?

[carruthid]

The photos are in a local folder currently but I could easily put them on the web site in a folder. The path does needs to be a web url. Currently I am importing photo information into another table and updating the records fine, but I can't seem to get it to path to point to a web url. It wants to put it in a file name path.

[comment]

What exactly is your purpose here? Filemaker is not very good at communicating with the OS file system - but perhaps something can be worked out. If not, you'll have to turn to OS-level scripting, or a plugin.

[carruthid]

We have an import of information that is fielded and manipulated with scripts to pull it all apart. One of the needs is that some of the records have pictures that go with the record and others do not. The end system only wants to see the url if there is a picture. If I use a common url with the picture id number at the end, it shows a broken file when no picture is available. So...I want to look in a file to see which pictures are there and then paste it into the record that matches.

The Photo files can be placed anywhere, but I but I need to paste a url name into a field with the id number.

[comment]

Well, if the end system uses HTML, the solution would probably be much simpler there.

To do this entirely within Filemaker - i.e. without a plugin or using OS-level scripting - see:

http://fmforums.com/forum/topic/66020-check-if-a-file-exists/page__p__313343#entry313343

[carruthid]

I have another question... What would the script look like if I only wanted to get the first 3 words in this sentence and paste into another field?

1995 Ford Escort Wagon. new transmission, tags, $900 obo. Call 209-922-0000, Groveland, CA

[comment]

get the first 3 words

Have a look at the LeftWords() function.

[carruthid]

Will do.

[carruthid]

Great, that worked. Thanks for the help.

[Lee Smith]

Take a look at this site http://www.youtube.c.../SkeletonKeySTL where you'll find a list of videos that are simple and easy-to-follow, showing in a concise way, some of the basic functions. The one for the Left Functions is

Located towards the bottom on the 3rd page (i.e. you need to click the button "Load 10 more video" a couple times.

HTH

Lee

[adriroxx]

Hi All,

I'm absolute beginner here at the forum and writing from Italy, so english could add some more panic to my questions, some times.

I followed the Carruthid question and Comment answers, I have something a little different:

I'm trying to verify a phisical address mailing list in which STREET NAME and STREET NUMBER are in the same field.

As usual, I need to split them in 2 separated fields before I can work on it.

I can not find a good solution to this because the kind of address we have in Italy and the syntax could be like this:

street nameofthestreet 48

avenue nameoftheavenue which is long 418

nameofthestreet 234/B

street nameofthestreet 37-39

As you see, we have no coma or fixed position or fixed number of words to work with, and numbers could have some "/letter" or "-letter" with them.

Can you suggest any calculation in order to extract numbers and split it down ??

Any suggestion will be really appreciated

[comment]

As you see, we have no coma or fixed position or fixed number of words to work with, and numbers could have some "/letter" or "-letter" with them.

Can you suggest any calculation in order to extract numbers and split it down ??

The problem is not the actual calculation, but finding something that is fixed - so that the calculation can use it as an "anchor".

Can it be assumed that the last space in the field is always the one immediately before the street "number"?

[adriroxx]

Hi Comment, thank for your interest in this issue.

Regarding: "Can it be assumed that the last space in the field is always the one immediately before the street "number"?

we do are in trouble because it CAN NOT be assumed, because sometimes we have combined or poorly typed numbers like:

nameofthestreet 234/8

nameofthestreet 234 B

street nameofthestreet 37 - 39

Consider that sometimes the street number is missing at all, and in that case we need to get aware of it, in order to "fire" the rekord all the way.

On our side, 99% of the times the STREET NUMBER will be ONE (but not the only one) of the last pieces in the field, because no one type the STREET NUMBER at the beginning of the field.

In my personal opinion, we should look for something able to recognize / detect the presence of a number in the field, considering also that:

- like in US, in italy we too have STREET NAME wich contain number themselves (like: avenue 25 april), so we should check if we have more than one number in the field, then skip the first one (or more than one) and pick up only the last one.

At that point, we can be confident that anything after it, whatever it is, it will be a part of the STREET NUMBER itself

In example: if we have:

avenue 25 april 234 B

our tools must recognize and skip the "25", go forward and recognize the "234" as the last number in the field, and pick up "234" and anything following it.

For your headache, when solved this example, we'll talk about processing: "avenue 25 april 37 - 39", but it will happend rarely...

Hope you can handle this... thanks

[adriroxx]

following my own 10 minutes ago post, i have an idea:

can we look for single word/strings in the field wich START with a number ? (like 8xxx)

If so, we could find all of them, then pick up only the last one and anything following it

this should work fine with all the following:

nameofthestreet 234/8

nameofthestreet 234 B

avenue 25 april 234 B

the only couple still out of our reach should be:

street nameofthestreet 37 - 39

avenue 25 april 37 - 39

what do you think about this approach? Am I missing something?

[comment]

It doesn't matter much what I think, because I don't see all the possible variations you might have. The point is that computers are very stupid and will do exactly what you tell them to do - whether it makes sense or not. So either you'll find an algorithm that fits all cases, or you'll have to use something that is less than perfect and go over it manually.

Re your latest suggestion: it seems to me that if you start from the last word, and go backwards looking for the first word that doesn't start with a digit, that would be the last word of STREET NAME, would it not? This, however, is a recursive process, so without the advanced version you'd have to script it.

[adriroxx]

...you'll have to use something that is less than perfect and go over it manually.

... looking for the first word that doesn't start with a digit

....This, however, is a recursive process, so without the advanced version you'd have to script it.

yes, I know, sooner or later I'll have to go over manually

but in the meanwhile, can you tell me (sorry, back to basic) what function should I use in order to:

- find a word that starts with a digit

- find a word that starts with a letter

- find a specific word in a string (let's say i want to find the word "street" in "Giorgio Armani street, 49")

By the way, I have version 9 Advanced, if useful

thanks again!