Parsing .csv file to list fields

[georgewash]

After several hours reading my FMP 8 book and searching these forums, I am still stuck. I'm sure this has been covered and if so, please point me to the thread.

I am importing a single line of .csv and want to import it into my FMP database. I know how to import and select the fields to match. What I am having difficulty with is how to handle the field that looks like this:

Pick n Click:1:$29.99:$29.99|Pick n Click Extended:1:$25.00:$25.00|Choose Theme:Winter Fun::|Add Extra DVD(s):2:$10.00:$20.00|Project Name:You re in Utah::|Mail Files:1::

I tried to split it into separate columns in Excel by the | and then the :. The problem is that I have multiple products I am trying to include in my Invoice in FMP. If I place the products in a column, I can get the quantity, price, and total to import. What I can't figure out is how to list these in one record. Each line creates a new record.

Hopefully this is clear, if not, I can provide a file of how my layout is set up.

Thanks!

[comment]

In your example, what is the intended result? How many records, with what fields?

[georgewash]

What I want is the field to be imported into the following fields in one record:

Product Qty Price Total

Pick n Click 1 $29.99 $29.99

Extra DVD(s) 2 $10.00 $20.00

The following items in the original .csv file need to be in different fields shown else where in the table layout.

Theme: Winter Fun

Project Name: You're in Utah

Mail Files: 1

I can manually separate and assign these. My difficulty is in getting the Products into a list.

[comment]

What I want is the field to be imported into the following fields in one record:

Product Qty Price Total

Pick n Click 1 $29.99 $29.99

Extra DVD(s) 2 $10.00 $20.00

Shouldn't that be three records?

Product                 Qty   Price     Total



Pick n Click             1    $29.99    $29.99

Pick n Click Extended    1    $25.00    $25.00

Add Extra DVD(s)         2    $10.00    $20.00

It wouldn't be very difficult to parse the data into records and fields. But I am not sure how to tell the lines that are product sales apart from those that aren't.

[georgewash]

So, should I import the data into a different table and then display it on my layout via a portal?

How do I parse it out? Do I need to manually create the lines in Excel first?

BTW, thanks for the help.

[comment]

You haven't answered my questions. Assuming a line item is one that has a total, I believe I'd do it this way:

Parse.fp7.zip

[georgewash]

That looks perfect. I'll have a go at it.

Thanks!

[Shiaumo]

You can concatenate all of the items if you have some way of sort them.