3 Replies Latest reply on Sep 29, 2014 10:40 AM by SunnyChu

    Extracting word from a doc file, and turn it into multiple records

    SunnyChu

      Title

      Extracting word from a doc file, and turn it into multiple records

      Post

      Hi guys I am currently stuck on a script that I would like to create for my task organizing database. and here is what I want to acheive:

       

      Input: A word document containing multiple objects, with a comma seperating them.

       

      Example input:

      Burger, fries, apple, orange juice, coke

       

      Output: I would like to create a script that can detect when a comma appears and create a new record with the word after that comma.

       

      Example output:

      Record 1: Burger

      Record 2: Fries

      Record 3: Apple

      Record 4: Orange juice

      Record 5: Coke

       

      The problem is that I don't know what keyword should I search in the net to find the correct tutorial to solve this problem. I tried "extracting" "parsing" and something to do with leftWord and rightWord and still no result. Any help will be greatly appreciated.

       

      Thank you in advance for helping.

      Sunny

        • 1. Re: Extracting word from a doc file, and turn it into multiple records
          philmodjunk

          Unless there's a plug in that can eliminate this step, you'll need to save your Doc file as a text file in order to get something that FileMaker can import.

          Import records can then import this text and each return separated row of text from the text (tab) file will become a record in the specified target table. The comma separated text can then be parsed into individual records containing the listed text:

          Set Variable [$ItemList ; value: Substitute ( YourTable::InputField ; , "," ; ¶ ) ]
          Go to Layout ["Items" (ItemTable) ]
          Loop
             Set Variable [$K ; value: $K + 1 ]
             Exit Loop If [$K > ValueCount ( $ItemList ) ]
             New Record/Request
             Set Field [ItemTable::Item ; Trim ( GetValue ( $ItemList ; $K ) ) ]
          End Loop

          • 2. Re: Extracting word from a doc file, and turn it into multiple records
            briancrockett

            I'm going to assume you have some method for reading data from a Word doc or you would have been asking how to do that.

            I generally use Substitute() to change all the "," to "¶". (The ¶ character is a carriage return.) This will turn your comma separated list into a value list and there are lot's of built in functions to deal with value lists.

            Your next step would be create a loop using a counter.

            Set Var $List = Substitute($List;",";"¶")
            Set Var $Max = ValueCount ( $List )
            Set Var $Count = 1
            Loop
                New Record
                Set Field Item GetValue($List;$Count)
                Set Var $Count = $Count + 1
                Exit Loop if $Count > $Max
            End Loop

            • 3. Re: Extracting word from a doc file, and turn it into multiple records
              SunnyChu

              Hi Phil & Brian,

              Many thanks for the help! It worked perfectly and i learnt alot from you guy's script. Would have took me days to figure that out.

              Thanks again!