Parse Text

[Jeff M]

I have a field that contains values separated by semi-colons. I need to take each of those values and turn them into their own records, maintaining the data from the other fields in the original record. For Instance... I have the following fields:

ProductNumber Code Keywords

Sample values would be...

5522 IM Boards; Dry Erase; 3M

And I want it broken into the following:

5522 IM Boards

5522 IM Dry Erase

5522 IM 3M

I'm trying to create some form of looping script that will do this to a table of 50,000 records. : Help!

[Raybaudi]

I need more examples...

1) Code is always ONE word ?

2) There is really a space between the semi-colon and the next word ?

[Raybaudi]

If the answer is yes to all my questions, you can try:

Freeze Window

Set Variable [ $Original ; Value: YourTable::YourOriginalField ]

Go To Layout [ "TheNewTableLayout" ]

Loop

Set Variable [ $count ; Value: $count + 1 ]

Set Variable [ $record ; Value: GetValue ( $Original ; $count ) ]

Set Variable [ $Keywords ; Value: Substitute ( RightWords ( $record ; WordCount ( $record ) - 2 ) ; ";" ; ¶ ) ]

Exit Loop If [ $count > ValueCount ( $Original ) ]

New Record/Request [ ]

Set Field [ NewTable::ProductNumber ; MiddleWords ( $record ; 1 ; 1 ) ]

Set Field [ NewTable::Code ; MiddleWords ( $record ; 2 ; 1 ) ]

Set Field [ NewTable::Keywords ; Trim ( GetValue ( $Keywords ; 1 ) ) ]

Duplicate Record

Set Field [ NewTable::Keywords ; Trim ( GetValue ( $Keywords ; 2 ) ) ]

Duplicate Record

Set Field [ NewTable::Keywords ; Trim ( GetValue ( $Keywords ; 3 ) ) ]

End Loop

Go To Layout [ original layout ]

[comment]

That doesn't look right: are there always 3 keywords?

[comment]

I believe it should be:

Show All Records

Go to Record [ First ]

Freeze Window

#

Loop

Set Variable [ $product ; Value: YourTable::ProductNumber ]

Set Variable [ $code ; Value: YourTable::Code ]

Set Variable [ $key ; Value: Substitute ( YourTable::Keywords ; ";" ; ¶ ) ]

#

Go To Layout [ "TheNewTableLayout" ]

Set Variable [ $i ; 1 ]

#

Loop

Exit Loop If [ $i > ValueCount ( $key ) ]

New Record/Request

Set Field [ NewTable::ProductNumber ; $product ]

Set Field [ NewTable::Code ; $code ]

Set Field [ NewTable::Keyword ; Trim ( GetValue ( $key ; $i ) ) ]

Set Variable [ $i ; Value: $i + 1 ]

End Loop

#

Go To Layout [ original layout ] 

Go to Record [ Next; Exit after last ]

End Loop

See also an alternative method here:

http://fmforums.com/forum/showpost.php?post/358232/

[Raybaudi]

I don't know.

But if they are more, a nested Loop will solve.

[Raybaudi]

Didn't YourTable contain only a text field with many values inside ?

[comment]

(not in this order)

I have the following fields: ProductNumber Code Keywords

I have a field that contains values separated by semi-colons.

I need to take each of those values and turn them into their own records, maintaining the data from the other fields in the original record.

And I should add that one the two fields, ProductNumber and Code, is probably redundant in the new table - but I don't know for sure which one.

Edited August 27, 2010 by Guest

[Raybaudi]

I (mis)understand that he had only one text field ( probably imported ) that contains values like this:

5522 IM Boards; Dry Erase; 3M

5523 IN Ccccc; DD DDDD; 1A

and wish to obtain, in a new table, new records like this:

5522 IM Boards

5522 IM Dry Erase

5522 IM 3M

5523 IN Ccccc

5523 IN DD DDDD

5523 IN 1A

[Jeff M]

I'm sorry if I wasn't clear.

I have 3 field in this imported table.

1. ProductNumber (text)

2. Code (text)

3. Keywords (text)

The keyword field contains separated by '; '

For instance, "Boards; 3M Markers; Erasers;

The keyword field could contain any number of keywords, each separated by a semi colon and a space.

ProductNum, code, keywords

53241, IM, Boards; 3M Markers; Erasers; sticky notes; mounting kits;

55332, IB, 3-ring binders; multiple-uses; 12 color choices;

I would like my end result to be:

ProductNum, code, keyword

53241, IM, Boards

53241, IM, 3M Markers

53241, IM, Stickie Notes

53241, IM, Mounting Kits

55332, IB, 3-ring binders

55332, IB, multiple-uses

55332, IB; 12 color choices