5 Replies Latest reply on Dec 16, 2011 9:29 AM by philmodjunk

    Extracting fields from a text block



      Extracting fields from a text block


      I have the need to upload a structured text block of date to a simple database and then extract the data into individuals fields that could easily be exported into an excel type file. For example I would have a series of text blocks that look like this:


      name: Sally Smith

      address: 123 main street

      city: somwhere

      state: MD


      - reading

      - writing

      - swimming


      And I need to be able to extract that information into individuals fields such as a name field, address field and so on.

      I am just not sure what the equation for the name field would look like to tell it to pull the name Sally Smith when it sees the tag "name:" in the text block.



        • 1. Re: Extracting fields from a text block

          Just to confirm, does your example show one block of text with multiple lines or 8 blocks of text?

          Does it show 1 record or 8?

          How will this data be "uploaded" to your FileMaker database?

          Since you have multiple "Interests" listed, how should they be structured for export?

          There are scripts and calculations that can parse out the text into individual fields, but we need to know the details before we can make a detailed suggestion.

          • 2. Re: Extracting fields from a text block


            I simplified this example to get my question across. This would all be 1 record, so one complete text block. and under the interests section that would be parsed off into a table where I would relate for instance the persons name three times and list their 3 interest as three separate records. 

            So, my structure would be to have a contact table. Which has the text block as one field and then all of the parsed contact information (name, address, city, state, etc.)

            And then there would be a 2nd related table that would have the persons name (for sake of example) and would create each of the interests as a new record.

            The information would be uploaded as individual text blocks, creating a new record each time.



            • 3. Re: Extracting fields from a text block

              The good news is that you have just one "field" per line and what appears to be a very predictable format. This greatly simplifies things. If I am assuming a consistancy that will not be the case with the actual data, you'll need to elaborate a bit so I can modify this approach.

              Trim ( Right ( GetValue ( Contacts::TextBlockField ; 1 ) ; Length ( contacts::TextBlockField ) - 6 ) )

              will return the Name data. An auto-enter calculation on the Name field or a script using set field with this expression can extract it to copy to another field.

              Different versions of that will work for Name through state.

              the 1 in GetValue returns the first row of text. The 6 represents the 6 characters of Name:<space>. Trim removes any leading or trailing spaces.

              I would not relate the two tables with the name field. Add an auto-entered serial number on the contacts table and use it to generate the list of interests in the related interests table. (Name are not always unique and people change their names. Names also are easily misspelled and require correction after initial data entry.)

              This script can parse out the list of interests into separate related records:

              Set Variable [$ContactID ; value: Contacts::ContactID ]
              Set Variable [$InterestsList ; Value: RightValues ( ValueCount ( contacts::TextBlockField ) - 5 ]
              Go To Layout [INterests (Interests) ]
                 Set Variable [$K ; Value: $K + 1]
                 Exit Loop If [$K > ValueCount ( $InterestsList ) ]
                 New Record/request
                 Set Field [Interests::ContactID ; $ContactID ]
                 Set Field [Interests::Interest ; GetValue ( $InterestsList ; $K ) ]
              End Loop
              Go To Layout [original layout]

              • 4. Re: Extracting fields from a text block


                One thing I didn't mention, is that the list of rows/fields could be variable. So I can't assume that the first row will be name and the second address, how can I get around that. 

                I just used name as my relating field for sake of example, I am using a unique auto-entered number. 

                Thank you for your help with the loop script.



                • 5. Re: Extracting fields from a text block

                  Then you have to find position of the label text inorder to extract the trimmed text to the right of it.

                  Let ( [ T = Contacts::TextBlockField ;
                            start = Position (  T ;"Name:" ; 1 ; 1 ) + 6 ;
                            End1 = Position ( T ; ¶ ; start ; 1 ) ;
                            End = if ( End1 ; End1 ; Length ( T ) + 1 ) //allows for possibility that "Name:" is on last line of text block
                          Trim ( Middle ( T  ; start ; End - start ) )
                        ) // Let