I need to import some simple data but the source Excel file has records with more than one value in a field. I need to parse them into separate records.
Here's what I have. I'm importing data with Projects plus the Contacts that go with each Project. This is a many-to-many relationship of course, so there should be a join table. Yet I don't have the luxury of getting a join table. What I have is a csv file of the Project records and in that file there is a column for the related Contacts. In many of the fields there are more than one Contact FK numbers, separated by carriage returns. Using Excel to open the file, the relevant columns in the records look like this:
I need to determine out how to parse these records so that each Project-Contact pair is in its own line that can be used to make a join table. How do I do it, either in Excel or in FMP?
This seems like a basic question but I can't find a resource for the answer.
Thanks in advance.