Load carriage return-ed fields as separate values?

[#1]

I have a field (ITEMS) in the database I'm using in which the data entry team uses carriage returns for additional items:

ORDER_NUMBER	ITEMS
96496498	ball umbrella pool cue frog
94165498	zydeco ocarina guitar

Is there any way to dice up ITEMS in a different field like ITEMS_2, so each item is a different value/row, but still associated to the ORDER_NUMBER? Like this:

ORDER_NUMBER	ITEMS_2
96496498	ball
	umbrella
	pool cue
	frog
94165498	zydeco
	ocarina
	guitar

[swuehl]

You can probably use Subfield() function for this, like

LOAD ORDER_NUMBER,

     ITEMS,

     SubField(ITEMS, chr(13)) as SUBITEM;

//Just a demo string

LOAD 'String1'&chr(13)&'String2'&chr(13)&'String3' as ITEMS,

     1234 as ORDER_NUMBER

AutoGenerate 1;

You'll need to find the correct ascii code for your separator.

[#3 · Accepted solution]

This is the code that worked for my particular situation:

SubField

(ITEMS, '
', ) as [SUB_ITEMS]

;

- instead of using a character code of 0A which wasn't working for the parsing, I just used pressed "Enter".