routine to split single record into multiple records

[Former Member]

Hi,

Here i have a scenario where i have to extract data from the flatfile.while loading the data from flat file to the BW i need to split single record into multiple recordsBelow is the flat file structure and the required format in BW .Can any one help me out how and where to write the routine.It wud be great help if any one help me out with the code.

FLAT FILE STRUCTURE:

ITEM MONTH YEAR KF1 KF2 KF3

aaa 10 2006 10 20 30

This record shud be splitted into below format when loading the data into BW

ITEM CALMONTH KF

aaa 102006 10 (KF1 value)

aaa 092006 20 (KF2 value)

aaa 082006 30 (KF3 value)

Thanks,

Jag

[Former Member]

Hi,

While uploading a flat file, are you using an internal table of string or an internal table with columns ?

What is the flat file format ? is it a txt file or a xls file ?

Also, is it certain that only first 3 columns will be static ?

Reward points if the answer is helpful.

Regards,

Mukul

[Former Member]

Hi Mukul,

My flat file format is Xls.csv format. I did not get you wht do you mean by 3 columns will be static. Wht iam trying to do is while loading the data i want to write a start /update routine which can split the records into my required format and load into the data target.Plz Let me know if u want me to give any more details.

Thanks,

Jag.

[Former Member]

Hi Jag,

I meant that first 3 columns will be common for each record. Am i right ?

I think following code will sove your problem.

Upload the file in an internal table (itab1).

Declare another internal table with the required structure(itab2).

loop at itab1.

clear : it_itab2.

split itab1 at ',' into itab2-item itab2-month itab2-year l_string.

do 20 times.

split l_string at ',' into itab2-condition l_string.

append it_itab2.

enddo.

itab2-condition = l_string.

append it_itab2.

clear : itab1, itab2.

endloop.

Reward points if the answer is helpful.

Regards,

Mukul

[Former Member]

hi

you have to first upload the flat file into a internal table as it is and then manipulate as required by you.

loop at itab.

do 3 times.

<write the logic to append the record as per your requirement>

enddo.

endloop.

regards

kishore

[Former Member]

Hi,

Is the number of columns fixed..or it differs..KF1 KF2 KF3..OR KF1 KF2 KF3 KF4..KFn.

Thanks,

Naren

[Former Member]

Hi Naren ,

My number of columns are fixed to 24. please let me know if u have any idea.

Thanks,

Jag.

[Former Member]

FLAT FILE STRUCTURE:

ITEM MONTH YEAR KF1 KF2 KF3

aaa 10 2006 10 20 30

check how many times u have to do.

val1 = 1.

do <no. of times>.

itab-item = <item>'aaa'.

itab-cal = <cal>'102006'.

itab-month = <mon>'10'.

itab-val = <val>'KF1'.

append itab.

clear itab.

mon = mon - 1.

val1 = val1 + 1.

concatenate 'KF' val1 into val.

enddo.

[Former Member]

Hi,

In the file structure the number of columns is fixed to 24

ITEM MONTH YEAR KF1 KF2 KF3 KF4 KF5 .... KF24

Please correct me if I am wrong..

Thanks,

Naren

[Former Member]

Yes Naren you are right. it wud be great if u can let me know the code.

Thanks,

Jag

[Former Member]

Hi,

Check this example...I simulated your scenario..

DATA: BEGIN OF ITAB_FILE OCCURS 0,

ITEM(3),

MONTH(2),

YEAR(4),

KF1(2),

KF2(2),

KF3(2),

KF4(2),

KF5(2),

END OF ITAB_FILE.

DATA: BEGIN OF ITAB_OUTPUT OCCURS 0,

ITEM(3),

MONTH_YEAR(6),

KF(2),

END OF ITAB_OUTPUT.

DATA: V_INDEX TYPE SYINDEX.

FIELD-SYMBOLS: <FS>.

• INPUT FILE DATA.

<b>ITAB_FILE-ITEM = 'aaa'.

ITAB_FILE-MONTH = '10'.

ITAB_FILE-YEAR = '2006'.

ITAB_FILE-KF1 = '10'.

ITAB_FILE-KF2 = '20'.

ITAB_FILE-KF3 = '30'.

ITAB_FILE-KF4 = '40'.

ITAB_FILE-KF5 = '50'.

APPEND ITAB_FILE.</b>

LOOP AT ITAB_FILE.

V_INDEX = 4.

• MOVE THE ITEM

ITAB_OUTPUT-ITEM = ITAB_FILE-ITEM.

• MOVE THE MONTH AND YEAR.

CONCATENATE ITAB_FILE-MONTH ITAB_FILE-YEAR

INTO ITAB_OUTPUT-MONTH_YEAR.

DO.

ASSIGN COMPONENT V_INDEX OF STRUCTURE ITAB_FILE TO <FS>.

IF SY-SUBRC <> 0.

EXIT.

ENDIF.

• MOVE THE KF values.

ITAB_OUTPUT-KF = <FS>.

APPEND ITAB_OUTPUT.

V_INDEX = V_INDEX + 1.

ENDDO.

ENDLOOP.

LOOP AT ITAB_OUTPUT.

WRITE: / ITAB_OUTPUT-ITEM,ITAB_OUTPUT-MONTH_YEAR,ITAB_OUTPUT-KF.

ENDLOOP.

Thanks,

Naren