6 Replies Latest reply on Aug 30, 2016 2:28 AM by Jax

    Importing lines of a plain text file into FileMaker fields

    Jax

      Hi guys,

       

      I'm trying to get multiple choice quiz questions into a FileMaker database. The information is in a plain text file. Question and options are simply separated by returns:

       

      Where in the cell is the genome located?
      Mostly in the cytosol.
      Mostly in the mitochondria.
      [X] Mostly in the nucleus.
      Stored in the membrane.
      Stored in RNA.

       

      Next question...

       

      Each question has 5 options, one of which is correct [X]. There's an empty line between questions.

       

      What's a good approach to get this into a FileMaker database with separate fields for the questions, the options, and the correct solution?

       

      All the best

        • 1. Re: Importing lines of a plain text file into FileMaker fields
          beverly

          Jax, are there always two returns between the sections (one question and 5 answers)?

           

          you can loop the 'lines' in the text. Import into a single text field one record or import into many records (one field). If the pattern is always the same, the first line is the question, the next 5 lines are answers and the line with "X" is the correct answer. This is followed by a blank 'line' before the next question.

           

          think through how you are manually reading the test and would manually copy the lines & push into the correct "bucket". Then the scripted loop would follow the same logic.

           

          I would have separate question and answer tables, so as you push a question, get back a unique ID (auto-enter serial or UUID) as the primary key that would be used with each of the answers as foreign key.

           

          beverly

          2 of 2 people found this helpful
          • 2. Re: Importing lines of a plain text file into FileMaker fields
            Jax

            Hi Beverly,

             

            Thanks for taking the time to answer. Yes, the structure is always the same: question, 5 options, one marked at the beginning of the line, an empty line before the next record.

             

            I'm envisioning a table for questions with a question ID plus an associated table for the 5 options as you also said.

            Screen Shot 2016-08-29 at 13.44.49.png

            This is the little that I was able to piece together. Now, I just have to learn more about FM scripting. What's a good place to start?

             

            Set Variable [$counter; Value:0]

             

            Loop

                 read a line of the text

                 if counter is 0 then use the text to define question text

                 if counter is 1, see whether line starts with [X]

                      if so set correct option to counter and use text to define option 1

                 if counter is 2, see whether line starts with [X]

                      if so set correct option to counter and use text to define option 2

                 ...

                 if counter is 5, reset counter to 0

             

                         proceed to next line

                 exit loop if end of text field is reached

              Set Field [$counter; $counter + 1]

            End Loop

            • 3. Re: Importing lines of a plain text file into FileMaker fields
              Jax

              Over on Filemaker.com comment suggested this script.

               

              Set Variable [ $txt; Value:AnyTable::gTextfield ]
              #
              # OUTER LOOP
              Go to Layout [ “Questions” (Questions) ]
              Set Variable [ $i; Value:1 ]
              Loop
                Exit Loop If [ $i > ValueCount ( $txt ) ]
                New Record/Request
                Set Field [ Questions::Question; GetValue ( $txt ; $i ) ]
                Set Variable [ $qID; Value:Questions::QuestionID ]
                #
                # INNER LOOP
                Go to Layout [ “Options” (Options) ]
                Loop
                Set Variable [ $j; Value:$j + 1 ]
                Exit Loop If [ $j > 5 ]
                #
                Set Variable [ $option; Value:GetValue ( $txt ; $i + $j ) ]
                Set Variable [ $correct; Value:Left ( $option ; 4 ) = "[X] " ]
                #
                New Record/Request
                Set Field [ Options::QuestionID; $qID ]
                Set Field [ Options::Option; If ( $correct ; Right ( $option ; Length ( $option ) - 4 ) ; $option ) ]
                Set Field [ Options::IsCorrect; $correct ]
                End Loop
                #
                Go to Layout [ “Questions” (Questions) ]
                Set Variable [ $i; Value:$i + 7 ]
                Set Variable [ $j; Value:"" ]
              End Loop

              • 4. Re: Importing lines of a plain text file into FileMaker fields
                beverly

                sure! the Else IF would be in there rather than multiple "IF". you are approximately there. And there would be a loop-within-a-loop. It may depend if this is a one-field-one-record or a one-field-many-records import.

                 

                If you mean fmforums:

                 

                then you should have stated that this is a Cross-post (question asked on multiple forums at the same time), as netiquette would prefer you do so.

                 

                beverly

                1 of 1 people found this helpful
                • 5. Re: Importing lines of a plain text file into FileMaker fields
                  philmodjunk

                  Did Comment suggest that you copy and paste the contents of your entire text file into that global field?

                   

                  That's how I read the script. Did it work for you?

                   

                  Comment is very knowledgeable so I'd consider his advice very likely to work for you.

                  1 of 1 people found this helpful
                  • 6. Re: Importing lines of a plain text file into FileMaker fields
                    Jax

                    Hi guys, thanks a lot for the comments. It did indeed solve my problem and I documented the script there in detail for other noobs like me to be able to reproduce it. Yes, Phil, as you said, a missing step I was able to figure out was to paste the text directly into the global text field for parsing. I'll keep the cross posting netiquette in mind. Thanks for the tip, Beverly. All the best.