Jump to content
Sign in to follow this  
-dp-

Parsing delimited data in a single field on import

Recommended Posts

I need to import data from a Google Forms survey. One of the fields is a text field containing multiple semi-colon-delimited values (e.g., "basketball;football;track & field;volleyball"). I need to break these out into separate records in a related table. The only idea I've come up with is to capture the field contents into a variable, count the values (using a method I have not yet figured out), create a related record with the leftmost value (the method of identifying which I have not yet figured out, given that some values contain multiple words and spaces), delete the leftmost value , increment a counter, then loop until the counter reaches a number equal to the number of values originally in the field. I can see lots of ways for this to break. Before I start slogging through it, I wanted to see if anyone has a better idea. Thanks.  

FMPA 18, macOS 10.15.3

Share this post


Link to post
Share on other sites

What you describe is more or less the way to go. Althought it can be a bit simpler if you start by substituting the semicolons with carriage returns. Then you can do something like:

Set Variable [ $parentID ; YourTable::ID ]
Set Variable [ $values ; Substitute ( YourTable::Yourfield ; ";" ; ¶ ) ]
Set Variable [ $n ; ValueCount ( $values ) ]
Go to Layout [ ChildTable ]
Loop 
  Set Variable [ $i ; $i + 1 ]
  Exit Loop If [ $i > $n ]
  New Record
  Set Field [ ChildTable::ParentID ; $parentID ]
  Set Field [ ChildTable::Value ; GetValue ( $values ; $i ) ]
End Loop
Go to Layout [ original layout ]

Of course, this assumes that the values themselves do not contain any carriage returns - otherwise you need to substitute them with a placeholder first.

 

Edited by comment

Share this post


Link to post
Share on other sites

It's good to know that I was on the right track, but this is much cleaner than where I was going. I was not aware of the ValueCount function. Thank you for educating me.

Share this post


Link to post
Share on other sites

BTW, although proper relational structure is certainly encouraged on these pages, you may also consider just replacing the semicolons with carriage returns and formatting the field as a checkbox set (as I presume it appeared on the original form).

 

Edited by comment

Share this post


Link to post
Share on other sites

Tempting, but every time I've done something like that in the past, it has eventually come back to bite me. I'll need to produce some charts, so I think that spinning off a separate table is the way to go. But thanks for the idea.

Share this post


Link to post
Share on other sites

I just adapted your script into my solution, and it worked perfectly. Thanks very much.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Kyrie 
×
×
  • Create New...

Important Information

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