Extraxting Data from a text file

[WallFilm]

Hi I am very basic user of FMP and I am now trying to "break" one field with text into different field for each information extracted from that field. I hope to use this process to create a record for each student.

This is a sample page my students send me and I imported in a FMP database in a field called letter. In the DB I also have date and other fields.

Name: John

Surname: XXX

Birth: 27/03/1984

Class:

Nationality: Greek

City: Salonicco

Gender: M

Option A : WRITIER

Option B: Whatever

Option C:

Suggestion A: Joyce

Suggestion B:

Previous Classes:

Student Id: AZ56345

Telephone: 1234567890

As the questions where optional I cannot use this definition for the Field "Option C" to do something like

Middle(letter;Position(¶&"Option C:",1,1) +11; ((Position("Suggestion A:",1,1)-(Position(¶&"Option C:",1,1) )+11)

As if the script doesnt find "Option C:" or "Suggestion A:" the script does not work

If every question has a reply the script works fine, but the optional replies are a problem for me.

Is there a way to overcome this problem? the first 3 questions and the last 2 are always there but what is in between varies.

I have several hundred letters and it will be great if I could automate this process through FMP.

Thanks a lot.

Walter

Edited May 14, 2010 by Guest

[fseipel]

The Position function will return a zero if the search string is not found.

So when parsing the text, include an IF.. THEN construct:

If Position(¶&"Option C:",1,1)>0 THEN

Set field = Middle(letter;Position(¶&"Option C:",1,1) +11; ((Position("Suggestion A:",1,1)-(Position(¶&"Option C:",1,1) )+11)

End If

Also, you may want to test for ¶ rather than "Suggestions A:", to determine where one field ends; that way if the next line "Suggestion A:" is omitted, the script won't break.

You may also want to create a subscript that accepts field name as parameter, and have the main script call it for each field to be extracted.

[WallFilm]

Thank you very much ....

Could you please give me an example of the subscript as I am not sure how to use this option.

Regards

Walter

[comment]

I don't see how a subscript would be useful here. Just add some fields, duplicate the Set Field [] step and change the search string.

ParseImported.zip

Edited May 16, 2010 by Guest
Fixed a small error in the script

[LaRetta]

This might be a bit easier; one set field in generic script does them all.

UPDATE: I could have looped forward as well but I'm a kinda backwards thinker. :blush2:

Letter.zip

[comment]

And if?

Letter.zip

[LaRetta]

Suggestion A: Joyce: Ulysses

You aren't supposed to break it.

You could test first as:

If [ ValueCount ( Students::lettertext ) ≠ PatternCount ( Students::lettertext ; ":" )

Show Custom Dialog [ OK ; "You should have used Michael's process, silly." ]

Halt Script

End If

... then my script.

But point made ... it might be easier to break, depending upon how configured. I had already tested and knew that carriage return in the middle of the lettertext data would also break it. BTW, yours would break on Option A if you put the standard pattern of searchString = "Option A: " because there is a space between the A and colon.

I still think it would be good solution in many instances, rather than 1) duplicating the set field line, 2) re-pointing to a new field and then 3) changing the search criteria within the calculation; all of which could open up possibility of error.

Regardless, it was a fun exercise and not as boring as yours.

UPDATE: Oh! I wondered why you inserted Ulysses. Ulysses is a book by Joyce!

Edited May 16, 2010 by Guest

[comment]

True, mine was boring. But it was addressed to a "Entry Level" member whose calculation (no offense) has only three parameters in Position(). And I also presume he's not going to open a parsing factory, so...

But if we want fun, let's go with it. Note that like your file, this assumes field names and prefixes are the same.

ParseToFields.zip

[bruceR]

Nice example. I find it much easier to read each line and split it into values, set script Parse Two.

ParseToFields.fp7.zip

[comment]

You have the same problem as LaRetta had earlier.

[bruceR]

Actually I had already created a version that handled that problem but in the interest of simplicity for discussion, stripped that feature out. But it's certainly easy enough to fix. Update in a moment.

[LaRetta]

Problem I had earlier ... (please see script Run2).

You can now add extra carriage returns and even have two colons on same line. I just didn't like the hard-coding of the field names even if it was just into a prefix variable. And I love this stuff!

Each line needed to be isolated before looking for the start and end instead of using variables to track the colons and carriage returns. I did change count to iteration and I used Get ( LayoutNames ) instead.

I changed from using Middle() to Right() in collecting the field value. It worked the same but looked more consistent since I had used Left() in deriving the field name.

LetterRev.zip

Edited May 17, 2010 by Guest
Added update

[comment]

I just didn't like the hard-coding of the field names

I actually started with a very similar thing, except in the opposite direction: I derived the prefixes from the field names.

[RodSierra]

Thanks for the updated parsing ideas, all mine work so I've never bothered to update them, but they are based on mostly on older version text manipulation functions. Can now greatly simplify my parsing, just never bothered to mess with what worked, but now its worth updating....just need to find the time!

[WallFilm]

Thanks for your suggestions.

For my moderate abilities it seems as if all I need to do is import each letter in the letter field then click and this will fill up every field when there is an appropriate reply.

As for Joyce and Ulysses: I don't know who added Ulysses to the Writer: Joyce reply .... For my example I took a real letter where my student simply add Joyce to his "Writer: " comment. he referred to Joyce as parts of a controversial lecture we had about his novella "the dead" from the Dubliners.

But this is not important here ........ so thanks again form everyone's kind help.

Walter

Edited May 17, 2010 by Guest