AnsweredAssumed Answered

How to Import Records with More Than One Value

Question asked by BMyers on Jan 26, 2018
Latest reply on Jan 29, 2018 by mikebeargie

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:

 

Project               Contacts

20101                    50226

20102                    50811

20103                    52226

                              50653

                              51238

20104                    51511

                              50967

 

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.

Outcomes