How to Extract Text from this input...

[David Wills]

Hi All,

I was wondering if any of your Filemaker gurus would have the answer to this one.

I have some records where the input is something like this…

                            <chapter-marker start="31031/6000s" duration="1001/30000s" value=“TEXT” posterOffset="11/30s"/>

                            <chapter-marker start="31031/6000s" duration="1001/30000s" value=“TEXT WITH 4 WORDS” posterOffset="11/30s"/>

And I would like a calculated field that would extract just the TEXT within the quotes.  That Text could be 1, 2, 3, or more words.

Any ideas?

[Josh Ormond]

How consistent is the input? 

• is 'posterOffset' always after the 'value"?
• are there ever double quotes inside of the value itself?

There are several ways to do this, and I know others will pop in with some excellent suggestions. They may range from finding the beginning and end of the 'value' portion of the text, to looping through each character to be more dynamic in figuring out where the end of that string is. The answers to the above questions may help other decide which type of solution to provide.

[David Wills]

Hi Josh.

Yes...'posterOffset' is always after the 'value'

No...there are never double quotes inside of the value

Hope that helps

Right now I am using the following calculation...

MiddleWords ( Input ; 8 ; 10 )

This gives me...

TEXT THAT I WANT" posterOffset="11/30s

So I'm nearly there...I just need to trim off the posterOffset stuff.

[comment]

1 hour ago, David Wills said:

have some records where the input is something like this…

                            <chapter-marker start="31031/6000s" duration="1001/30000s" value=“TEXT” posterOffset="11/30s"/>

                            <chapter-marker start="31031/6000s" duration="1001/30000s" value=“TEXT WITH 4 WORDS” posterOffset="11/30s"/>

It sounds like your input is really an XML document. If so, I would suggest you import it using a custom XSLT stylesheet to transform it into Filemaker's XML schema so that each chapter-marker element becomes a record and the value attribute is a field (or one of the fields, if you want to import other values too). This is much more robust than parsing XML as text.

If importing is not an option, you could use a calculation =

Let ( [
start = Position ( input ; "value=\"" ; 1 ; 1 ) + 7  ;
end = Position ( input ; "\"" ; start ; 1 )
] ;
Middle ( input ; start ; end - start )
)

This is assuming the input contains a single chapter-marker element (your question is not entirely clear in that regard).

Note also that your question has smart quotes surrounding the text you want to extract. I presume that's a result of your editing and not how it really appears in the original.

 

[David Wills]

You are the MAN!  Thanks so much.  Works like a charm.

If you are interested it is an XML from Final Cut Pro (Apple's Video Editor).  I make video courses on Music Production Equipment and you make DVD chapters on the timeline but there's no native way to export them to make a table of contents for the courses I produce.

Your calculation has fixed all that.  XML file goes in...Text file with all the DVD chapters come out.

Thanks, so much!  David Wills
ProAudioEXP.com

[comment]

16 minutes ago, David Wills said:

If you are interested it is an XML from Final Cut Pro (Apple's Video Editor). 

Can you attach an example file (a complete file, preferably zipped)?

 

[David Wills]

Here you go

DVD.fcpxml.zip

[comment]

Have a look at the attached demo that shows the proper way to handle an XML input.

XML Import.zip

Edited January 26, 2021 by comment

[David Wills]

Very good  Thank you!