Jump to content

Importing lines of a plain text file into FileMaker fields


This topic is 2792 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi FMers,

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

Link to comment
Share on other sites

If you only need to do this once, then paste the text into a global text field and have a script parse it from there. Otherwise import the file into a temp table (where each line will be a record) and parse from there.

For best results, make each question a record in a Questions table, and each option a related record in an Options table.

Link to comment
Share on other sites

Try:

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 

Note that this assumes more than one option can be correct. Otherwise it would be better to store the correct option's ID in the parent question's record.

  • Like 2
Link to comment
Share on other sites

Ok, I've gone through this cool script line by line reading up on the commands used. I pasted the text into the global text field and ran the script but at first it only read the first question and options. I discovered that this was a newbie error causes by the weird way that the FileMaker Script editor does not allow direct text editing of scripts but forces users to put everything in click by click. This was the bad line I pasted in by mistake: Set Variable [ $i; Value:"Value:$i + 7"]. It should have read [ $i; Value:$i + 7].

I include the some details below for others noobs to be able to reproduce comment's great solution.

Screen Shot 2016-08-30 at 10.08.29.png

Screen Shot 2016-08-30 at 10.10.29.png

Screen Shot 2016-08-30 at 10.48.50.png

It turns out there's no space between the X and the option, so I adjusted the LEFT and RIGHT commands accordingly.

Screen Shot 2016-08-30 at 11.14.39.png

These are the commands I didn't know. Left takes 3 characters from the left of the $option text string. If that matches [X] $correct is set to 1. Right takes the full length of the string minus 3 characters from the right to not store the X in the option. The rest I couldn't come up with is clever looping using 2 counters $i and $j.

This is the test text I used is attached.

 

test questions.txt

 

Edited by jax
I found the bug and corrected it.
Link to comment
Share on other sites

2 hours ago, jax said:

I've gone through this cool script line by line reading up on the commands used.

Good for you - that's a great way to learn.

Two minor points:

  1. The Options table should also have an auto-entered serial number OptionsID field~ as should every table (with the possible exception of the Globals table and such);
  2. The IsCorrect field should properly be a Number field; among other things, this will allow you to format it is as Boolean.

--
P.S. The looping is actually quite simple. Your file has a block of 7 lines for each question: the actual question, 5 options and a blank line. The outer loop starts at 1 and increments  by 7 on each iteration, so it always corresponds to a line containing the question. Then you just add 1, 2, 3, 4 and 5 to that to retrieve the options belonging to that question.

 

Edited by comment
Link to comment
Share on other sites

I will change the 2 points indicated. In fact, I was looking for a boolean format when I set up the variable but only found number format which didn't seem right.

^_^ I guess if you spent enough time with FileMaker looping and other programming becomes 2nd nature. I had already been working on this for an hour when you posted and hadn't arrive yet at the right solution. If this where DNA I'd be faster ;-) Thanks for all the help.

Link to comment
Share on other sites

On 8/29/2016 at 5:11 AM, comment said:

Note that this assumes more than one option can be correct. Otherwise it would be better to store the correct option's ID in the parent question's record.

Hi Comment,

You may not be wishing to complicate the situation for Jax at this point since their self-rating is novice but I see three tables: Questions, Options and Responses, where the correct Option's response/s would be placed instead into the Responses table along with the ID of the person taking the test.  In this way, multiple 'same questions' asked of multiple people can be imported and parsed.  I just wanted to mention that for future.

Beautiful script. :)

Added for Jax:  The responses table would be known as a join table.

Edited by LaRetta
removed word 'instead'
Link to comment
Share on other sites

1 hour ago, LaRetta said:

I see three tables: Questions, Options and Responses

I see at least four:

quiz.png

This is assuming there is a single uniform questionnaire that all subjects must go through, once. Otherwise there would be more.

 

1 hour ago, LaRetta said:

where the correct Option's response/s would be placed instead into the Responses table

I didn't quite get this part.

Link to comment
Share on other sites

1 hour ago, comment said:

I didn't quite get this part.

"where the correct Option's response/s would be placed instead into the Responses table."

Yes, that was poorly worded; thanks for the correction.  I just meant that the Subject's choice from Options (the OptionID) would go into the Responses table.  The word 'instead' was a typo which I had removed.  :-)

And yes indeed, Jax will want a Subjects table (user or person taking the test).

Edited by LaRetta
Link to comment
Share on other sites

This topic is 2792 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.