Function module to pass data from excel file in BD...

Former Member · ‎2008 Dec 09

Hi guys,

I am using 'TEXT_CONVERT_XLS_TO_SAP' to pass data from excel file. Am using ECC 6.0

My excel file contains 1 header list and multiple line items. For this I think I must use 'ALSM_EXCEL_TO_INTERNAL_TABLE' instead of 'TEXT_CONVERT_XLS_TO_SAP'. Can you please suggest and send me the procedure/code for the above mentioned file to upload data.

Thanks in advance.

Kiran

Former Member · ‎2008 Dec 09

HI,

Check this Code..

DATA l_count TYPE sy-tabix.

CONSTANTS: lc_begin_col TYPE i VALUE '1',

lc_begin_row TYPE i VALUE '2',

lc_end_col TYPE i VALUE '2',

lc_end_row TYPE i VALUE '3000'.

* Begin of CALK912848 - Carlos Werberich - 16Sep08

CLEAR p_i_excel_data. REFRESH p_i_excel_data.

* End of CALK912848 - Carlos Werberich - 16Sep08

* Function module to read excel file and convert it into internal table

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_p_file

i_begin_col = lc_begin_col

i_begin_row = lc_begin_row

i_end_col = lc_end_col

i_end_row = lc_end_row

TABLES

intern = i_data

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

* Error in file upload

IF sy-subrc NE 0 .

MESSAGE text-006 TYPE 'E'.

EXIT.

ENDIF.

IF i_data[] IS INITIAL .

MESSAGE text-007 TYPE 'E'.

EXIT.

ELSE.

SORT i_data BY row col .

* Loop to fill data in Internal Table

LOOP AT i_data .

MOVE i_data-col TO l_count .

ASSIGN COMPONENT l_count OF STRUCTURE p_i_excel_data TO .

AT END OF row .

* Append data into internal table

APPEND p_i_excel_data.

CLEAR p_i_excel_data.

ENDAT .

ENDLOOP .

ENDIF

Former Member · ‎2008 Dec 09

Hi Kiran,

Use it like this

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE "name of file

I_BEGIN_COL = 1 "Starting column

I_BEGIN_ROW = 2 "Starting row

I_END_COL = 17 "Ending coloumn

I_END_ROW = 3000 "Ending row

TABLES

INTERN = itab "Internal table

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3 .

basically what this FM will do is it will read the file starting second row as first name can be the field names. It will take the data from each cell in Excel sheet. then you can use it in programming to put in seprate internal tables for header and Item.

loop at itab into wa.

if wa-col = '0001'.

record-Control = wa-value.

endif.

elseif wa-col = '0002'.

record-COMP_CODE = wa-value.

elseif wa-col = '0003'.

record-DOC_TYPE = wa-value.

elseif wa-col = '0004'.

record-VENDOR = wa-value.

endif.

Like this you can use it.

Regards,

Sachin

tarangini_katta · ‎2008 Dec 09

HI kiran,

Do like this .THis idid for KB31N transaction.

TYPES : BEGIN OF fl_header,

budat(10) TYPE c,

bldat(10) TYPE c,

bltxt TYPE coheader-bltxt,

END OF fl_header.

TYPES : BEGIN OF fl_item,

ekostl TYPE ekostl,

eaufnr TYPE eaufnr,

stagr TYPE stagr,

mbgbtr(10) TYPE c,

sgtxt TYPE sgtxt,

END OF fl_item.

*Selection Screen

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.

PARAMETERS : p_file TYPE rlgrap-filename,

p_kokrs TYPE kokrs DEFAULT 'ZIE1',

p_batch TYPE apqi-groupid.

SELECTION-SCREEN END OF BLOCK b1.

*Data Declarations

DATA : it_intern TYPE TABLE OF alsmex_tabline,

wa_intern TYPE alsmex_tabline,

it_bdcdata TYPE TABLE OF bdcdata,

wa_bdcdata TYPE bdcdata,

wa_header TYPE fl_header,

wa_item TYPE fl_item,

it_item TYPE TABLE OF fl_item,

t_fnam(20) TYPE c,

t_idx TYPE n,

t_file TYPE rlgrap-filename,

t_amt_int(10) TYPE c,

t_amt_int1(10) TYPE c,

t_amt_int2(10) TYPE c.

*F4 Help for the FilePath

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

IMPORTING

file_name = p_file.

START-OF-SELECTION.

t_file = p_file.

*Upload data from excel sheet to the Internal Table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = t_file

i_begin_col = 1

i_begin_row = 1

i_end_col = 10

i_end_row = 500

TABLES

intern = it_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc = 0.

*Append data internal table to corresponding strctures

LOOP AT it_intern INTO wa_intern.

*Header Data

CASE sy-tabix.

CASE wa_intern-row.

WHEN '0002'.

wa_header-budat = wa_intern-value.

WHEN '0004'.

wa_header-bldat = wa_intern-value.

WHEN '0006'.

wa_header-bltxt = wa_intern-value.

ENDCASE.

*Item Data

IF sy-tabix GE '12'.

ON CHANGE OF wa_intern-row.

IF sy-tabix GT '12'.

APPEND wa_item TO it_item.

CLEAR wa_item.

ENDIF.

ENDON.

CASE wa_intern-col.

WHEN '0001'.

wa_item-ekostl = wa_intern-value.

WHEN '0002'.

wa_item-eaufnr = wa_intern-value.

WHEN '0003'.

wa_item-stagr = wa_intern-value.

WHEN '0004'.

wa_item-mbgbtr = wa_intern-value.

WHEN '0005'.

wa_item-sgtxt = wa_intern-value.

ENDCASE.

ENDIF.

AT LAST.

APPEND wa_item TO it_item.

CLEAR wa_item.

ENDAT.

ENDLOOP.

*Open the Session in SM35.

PERFORM open_group.

*BDC Recording

PERFORM bdc_dynpro USING 'SAPLSPO4' '0300'.

PERFORM bdc_field USING 'BDC_CURSOR'

'SVALD-VALUE(01)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=FURT'.

PERFORM bdc_field USING 'SVALD-VALUE(01)'

p_kokrs.

PERFORM bdc_dynpro USING 'SAPLK23F1' '1200'.

perform bdc_field using 'BDC_CURSOR'

'COHEADER-SEND_REC_REL'.

perform bdc_field using 'BDC_OKCODE'

'=PVAR'.

PERFORM bdc_field USING 'COHEADER-SEND_REC_REL'

'03SAP'.

PERFORM bdc_field USING 'RK23F-STATUS'

'L'.

perform bdc_dynpro using 'SAPLK23F1' '1200'.

perform bdc_field using 'BDC_OKCODE'

'=POST'.

perform bdc_field using 'COHEADER-SEND_REC_REL'

'03SAP'.

perform bdc_field using 'RK23F-STATUS'

'L'.

REPLACE ALL OCCURRENCES OF '/' IN wa_header-budat WITH '.' .

PERFORM bdc_field USING 'COHEADER-BUDAT'

wa_header-budat.

REPLACE ALL OCCURRENCES OF '/' IN wa_header-bldat WITH '.' .

PERFORM bdc_field USING 'COHEADER-BLDAT'

wa_header-bldat.

PERFORM bdc_field USING 'COHEADER-BLTXT'

wa_header-bltxt.

MOVE 1 TO t_idx.

t_idx = 1.

LOOP AT it_item INTO wa_item.

CONCATENATE 'EL2(' t_idx ')' INTO t_fnam.