How to separate comma delimited field during load

[#1]

I have data in TXT FILE . It contains two field OBJECT AND VALUES. The Values field is always comma delimited as seen below in Current Structure.

I would like to change the table structure as shown in desired structure.

Can someone please help me how to achieve this during load??

Thank you.

Pranita

Current structure

OBJECT	VALUES
A	123,456,789
B	123,7888,9998
C	123232323,232323,08309273
D	456,7888,232323

Desired Structure:

OBJECT	VALUES
A	123
A	456
A	789
B	123
B	7888
B	9998
C	123232323
C	232323
C	08309273
D	456
D	7888
D	232323
D	23232389

[#2]

Hi Praneetha

Can u give me any sample file ...

[#3]

with this function: subfield( your fieldOBJECT ,',')

e.g.

LOAD

                                        [OBJECT,

                                        trim(subfield([Values],';')) AS [Project Ref. ProjectId TEMP]

                              RESIDENT Issue_Category

[erichshiino · Accepted solution]

Hi, you can use iterno() to run to a single line many times.

You can use subfield to retrieve part of the string separeted by the commas:

TABLE:

LOAD * INLINE [

    OBJECT, VALUE

    A, "123,456"

    B, 1234

    C, "136,1532,16323"

]

;

RESULT:

lOAD OBJECT, SubField(VALUE,',',ITERNO()) AS VALUE_RESULT

RESIDENT TABLE

WHILE(ITERNO()<=SubStringCount(VALUE,',')+1);

DROP TABLE TABLE;

Hope this helps,

Erich

[#5]

Hi,

Erich is correct, Subfield will do the job for you. But Iterno() function is not mandatory. If you use subfield at back end with out any third parameter will loop through the field.

TABLE:
LOAD *, SUBFIELD(VALUE,',') AS NEWVAULE;
LOAD * INLINE [
    OBJECT, VALUE
    A, "123,456"
    B, 1234
    C, "136,1532,16323"
];

Hope this may help you.

- Sridhar

[#6]

Thank you all for the quick reponse!!

Erich,  Thank you for the solution. Worked perfectly!!

[dileep]

Hi Erich,

Your post was very help full thanks. 

Dileep