Jump to content

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

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 ]
  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
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.

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
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.

Link to post
Share on other sites

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.