Parsing a variable field

[DPS]

I have a data parsing challenge with a CSV file of web orders I download from our website. Each order comes through as one record with fields for name, shipping address etc.. Then there is one field that contains ALL the ordered items. This could be one item or 10 items. I now need to create a script to parse this into separate records. So if for instance someone ordered 4 items, the script needs to create 4 new records and in each insert the data from one of the ordered items into several fields.

This is how the data arrives in one field. You can see the delimiters "|" separate "fields within the field" and "~" defines a "new record within the field".

1|SKU1|$14.50|1|Name1~2|SKU2|$9.95|1|Name 2~3|SKU3|$29.95|1|Name 3~4|TSKU4|$129.95|1|Name 4

So from this data, I need to create the following in 4 different records (where the | actually means it is a different field).

1|SKU1|$14.50|1|Name1

2|SKU2|$9.95|1|Name 2

3|SKU3|$29.95|1|Name 3

4|TSKU4|$129.95|1|Name 4

So where do I start with such a situation?

[Lee Smith]

Run your import though a text editor such as TextWrangler (Free from Barebones Software, or BBEdit, also from Barebones. Just do a Replace All for | with t, and ~ with r, and then import the file.

Edited September 14, 2008 by Guest

[comment]

See the attached file for an example of how this might be done.

ParseOutChildren.fp7.zip