How to Import Records with More Than One Value

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.

This is my method. I'm extremely cautious to import these and NOT resort in any way. I might even include a column (if possible) that provides a sequential number in Excel, should I need that and/or add this column to the temp table and fill it before doing anything.

Then I loop and store the column value (non empty) into a variable. If empty on the next loop, use the previous value in the fields, and only change the variable upon new (non-blank) value.

mikebeargie, I used your method and added Beverly's tips. It took some fiddling to get it to work. It kept exiting after the first record (exiting after the nested loop). I used the data viewer to see what was happening. The variable $i gave me the status of the nested loop, and I added a variable in the main loop called $LoopCounter.

Two substantive changes:

- The Go to Layout step near the bottom wouldn't work if it said: Go to Layout: [ original layout ; Animation: None ]. Instead it need to actually name the layout that was specified in line 5.

- I noticed that the script needed to initialize the variable $i = zero to make sure the nested loop count started at zero. I positioned this after line 5.

For your second point that's not true. Blank plus one = one. So you don't need to create a variable of $i for zero outside of the inner loop at all, just blank it out afterwards so the next time through the inner loop it starts over at 1 again.