Hi all.  I assume that I'll need to use the API for this but welcome any instructions for other methods as well.

I have a text file to import where one column contains multiple values that I would like translated to separate constituent attributes with the same attribute type.  Here is an example of the row from the text file:

7741633,Bob,Smith,bobsmith@someschool.edu,6030502,Clubs and OrganizationsID:10229,null,"[""Omicron Delta Kappa"",""Orientation Leaders""]",2020-03-18T17:59:52.000Z

I've highlighted the data that would become the attribute values. My goal is to create two attributes for this constituent both with the attribute type of "Clubs and Organizations".  One for "Omicron Delta Kappa" and one for "Orientation Leaders".

Any one have suggestions on how to accomplish this?

Thanks,

Catherine

You can use dictionaries

Leave the original field unmapped, with no dictionary attached. In virtual fields, use Copy Field and apply a dictionary to find the first value (Omicron Delta Kappa) and map to the first Clubs and Organizations attribute. Then create a second virtual field that also copies the orginal field but apply a dictionary that finds the second value (Orientation Leaders) and  map it to a second Clubs and Organizations attribute.

As for the actual dictionaries, are there always two values and the double quotation marks?

That looks promising.  Do I need a separate field and dictionary for each potential value???

I've got one row that has 10 values in that field.  I'm not sure what the limit would be though.

Having 2 values all the time would be easy...variety makes it a lot harder. With 2 values, you could have a dictionary to extract the first and another to extract the last. With variation in the number of fields, you'd have to look for specific text strings, and that would get messy because they could be in any order.

Can you adjust the output from the database of origin? If you could have it output in 10+ separate fields, that would be much easier. Another idea would to parse out that field into multiple fields in Excel before the import. Then you could just map each field directly.

I am unable to adjust the output of the other database unfortunately.

I was hoping to use a single tool for the import and not have to manipulate the file before running it through Import-O-Matic - for training and simplicity, etc.

Then API will be your best bet. You could do a series of if-then-else statements that looks for each possible club type and adds it when it is present in the incoming data. 

I was afraid of that. Any suggestions on how to do that? I've only done very simple stuff in the API so far.