Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

upload excel file to internal table

Former Member

‎10-06-2008 6:41 AM

0 Kudos

Hi all,

My requirement is to upload a excel file from presentation server into internal table.

i cannot use FM 'ALSM_EXCEL_TO_INTERNAL_TABLE' it does not fits my requirement,

I have tried FM 'TEXT_CONVERT_XLS_TO_SAP' but it returns SY-TABIX 1 which means conversion

failed.

I have used FM GUI_UPLOAD but i get the scrap data in my table for keeping the filetype ASC in exporting parameters and if i give the filetype XLS for excel than i get nothing in my return table.

please suggest some helpfull solution.

Thanks

Devender.

7 REPLIES 7

Former Member

‎10-06-2008 6:45 AM

0 Kudos

Hi

I send one program u go through this u can get idea...Same time u have 2 check ur flat file first according to ur recoeding.

Ex:

no standard page heading line-size 255.

*include bdcrecx1.

DATA: BEGIN OF ls_infrec OCCURS 0,

recnnr(12) TYPE c,

bukrs(4) TYPE c,

END OF ls_infrec.

DATA: lt_bdcdata LIKE bdcdata OCCURS 0 WITH HEADER LINE.

DATA: lt_messtab LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE.

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS: p_flie TYPE localfile.

PARAMETERS: p_mode TYPE ctu_mode OBLIGATORY DEFAULT 'A' MODIF ID ch3.

SELECTION-SCREEN : END OF BLOCK b1.

DATA : BEGIN OF lt_messtab1 OCCURS 0,

srnno(3) TYPE c.

INCLUDE STRUCTURE lt_messtab.

DATA : END OF lt_messtab1.

DATA : lt_infrec LIKE ls_infrec OCCURS 0 .

DATA : lv_srnno TYPE i,

lv_message(300) TYPE c

.

DATA: idx(2) TYPE c VALUE '01',

var TYPE string.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_flie.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

field_name = p_flie

CHANGING

file_name = p_flie

EXCEPTIONS

mask_too_long = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

START-OF-SELECTION.

PERFORM upload_file.

PERFORM process_bdc_data.

PERFORM format_bdc_message.

&----

*& Form upload_file

&----

FORM upload_file .

DATA : lv_file TYPE string.

lv_file = p_flie.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = lv_file

filetype = 'DAT'

TABLES

data_tab = lt_infrec

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

OTHERS = 17.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

ENDFORM. " upload_file

&----

*& Form process_bdc_data

&----

FORM process_bdc_data .

LOOP AT lt_infrec INTO ls_infrec .

REFRESH lt_bdcdata.

*perform open_group.

perform bdc_dynpro using 'SAPLRECA_BDT_APPL_INITIAL' '1000'.

perform bdc_field using 'BDC_OKCODE'

'=RECA_CHANGE'.

perform bdc_field using 'BDC_CURSOR'

'RECN_CONTRACT_X-BUKRS'.

perform bdc_field using 'RECN_CONTRACT_X-BUKRS'

ls_infrec-bukrs.

perform bdc_field using 'RECN_CONTRACT_X-RECNNR'

ls_infrec-recnnr.

perform bdc_dynpro using 'SAPLRECA_BDT_APPL_TOOL' '0100'.

perform bdc_field using 'BDC_OKCODE'

'=RECA_COSETTLERULE'.

perform bdc_field using 'BDC_CURSOR'

'REGCFLDS_FE-OBJIDENT'.

perform bdc_field using 'REGCFLDS_FE-OBJIDENT'

'BU IN01/10000000/10000002'.

*perform bdc_field using 'REGCFLDS_FE-PARTNER'

  • '2000000032'.

*perform bdc_field using 'REGCFLDS_FE-RECNBEG'

  • '2007/01/01'.

*perform bdc_field using 'REGCFLDS_FE-RECNEND1ST'

  • '2010/12/31'.

*perform bdc_field using 'REGCFLDS-RECNTXT'

  • 'Test CLI contract with WHT'.

perform bdc_dynpro using 'SAPLKOBS' '0130'.

perform bdc_field using 'BDC_CURSOR'

'COBRB-KONTY(01)'.

perform bdc_field using 'BDC_OKCODE'

'=PARA'.

perform bdc_dynpro using 'SAPLKOBS' '0110'.

perform bdc_field using 'BDC_CURSOR'

'COBRA-ABSCH'.

perform bdc_field using 'BDC_OKCODE'

'=BACK'.

*perform bdc_field using 'COBRA-APROF'

  • 'SAS1RE'.

perform bdc_field using 'COBRA-ABSCH'

'A4'.

perform bdc_dynpro using 'SAPLKOBS' '0130'.

perform bdc_field using 'BDC_CURSOR'

'COBRB-KONTY(01)'.

perform bdc_field using 'BDC_OKCODE'

'=BACK'.

perform bdc_dynpro using 'SAPLRECA_BDT_APPL_TOOL' '0100'.

perform bdc_field using 'BDC_OKCODE'

'=RECA_BDT_STORE'.

perform bdc_field using 'BDC_CURSOR'

'REGCFLDS_FE-OBJIDENT'.

perform bdc_field using 'REGCFLDS_FE-OBJIDENT'

'BU IN01/10000000/10000002'.

*perform bdc_field using 'REGCFLDS_FE-PARTNER'

  • '2000000032'.

*perform bdc_field using 'REGCFLDS_FE-RECNBEG'

  • '2007/01/01'.

*perform bdc_field using 'REGCFLDS_FE-RECNEND1ST'

  • '2010/12/31'.

*perform bdc_field using 'REGCFLDS-RECNTXT'

  • 'Test CLI contract with WHT'.

CALL TRANSACTION 'RECN' USING lt_bdcdata MODE p_mode MESSAGES INTO lt_messtab.

*perform close_group.

endloop.

LOOP AT lt_messtab.

lv_srnno = lv_srnno + 1.

MOVE-CORRESPONDING lt_messtab TO lt_messtab1.

lt_messtab1-srnno = lv_srnno.

APPEND lt_messtab1.

ENDLOOP.

ENDFORM. "process_bdc_data

----

  • Start new screen *

----

FORM bdc_dynpro USING program dynpro.

CLEAR lt_bdcdata.

lt_bdcdata-program = program.

lt_bdcdata-dynpro = dynpro.

lt_bdcdata-dynbegin = 'X'.

APPEND lt_bdcdata.

ENDFORM. "bdc_dynpro

----

  • Insert field *

----

FORM bdc_field USING fnam fval.

IF fval <> space.

CLEAR lt_bdcdata.

lt_bdcdata-fnam = fnam.

lt_bdcdata-fval = fval.

APPEND lt_bdcdata.

ENDIF.

ENDFORM. "BDC_FIELD

&----

*& Form format_bdc_message

&----

  • text

----

FORM format_bdc_message .

LOOP AT lt_messtab1 WHERE msgtyp = 'S' .

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

id = lt_messtab1-msgid

lang = sy-langu

no = lt_messtab1-msgnr

v1 = lt_messtab1-msgv1

v2 = lt_messtab1-msgv2

v3 = lt_messtab1-msgv3

v4 = lt_messtab1-msgv4

IMPORTING

msg = lv_message

EXCEPTIONS

not_found = 1

OTHERS = 2.

WRITE:/2 lt_messtab1-srnno,6 lv_message .

ENDLOOP.

*

ENDFORM.

Former Member
In response to Former Member

‎10-06-2008 7:11 AM

0 Kudos

hi prabhu,

we can only have 'BIN' or 'ASC' type in filetype exporting parameter for FM GUI_UPLOAD. if i use it sy-tabix return is 5 which says invalid type.

Thanks

Devender

Former Member

‎10-06-2008 6:47 AM

0 Kudos

Hi

Good

Check the below SDN link, hope this will help you to solve your problem.

https://forums.sdn.sap.com/post!reply.jspa?threadID=994822

Thanks

mrutyun^

Former Member

‎10-06-2008 6:51 AM

0 Kudos

Hi,

Pls apply the below code:

TYPE-POOLS: TRUXS.

DATA: IT_RAW TYPE TRUXS_T_TEXT_DATA.

PARAMETERS: P_FILE TYPE RLGRAP-FILENAME OBLIGATORY MEMORY ID FILENAME.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = P_FILE.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_LINE_HEADER = 'X'

I_TAB_RAW_DATA = IT_RAW

I_FILENAME = P_FILE

TABLES

I_TAB_CONVERTED_DATA = LT_DATA[]

  • EXCEPTIONS

  • CONVERSION_FAILED = 1

  • OTHERS = 2

.

IF SY-SUBRC <> 0.

  • MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

  • WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Former Member
In response to Former Member

‎10-06-2008 7:13 AM

0 Kudos

HI,

I have already used this and i have mentioned it also in my post the sy-tabix return is which says unable to convert

Thanks

Devender

Former Member

‎10-06-2008 6:57 AM

0 Kudos

DATA: it_test TYPE STANDARD TABLE OF alsmex_tabline WITH HEADER LINE.

DATA :v_start_col TYPE i VALUE '1',

v_start_row TYPE i VALUE '1',

v_end_col TYPE i VALUE '256',

v_end_row TYPE i VALUE '65536',

v_text TYPE repti.

  • Funtion Module to upload values from excel to the Internal table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = v_start_col

i_begin_row = v_start_row

i_end_col = v_end_col

i_end_row = v_end_row

TABLES

intern = it_test

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

Former Member

‎10-06-2008 7:11 AM

0 Kudos

hi,

The function module ALSM_EXCEL_TO_INTERNAL_TABLE requires you to declare the internal table as shown below. Or else the data wonu2019t be uploaded from excel. Please check if u have declared the internal table and other parameters in the same way. Else you can use GUI_UPLOAD.

DATA: it_test TYPE STANDARD TABLE OF alsmex_tabline WITH HEADER LINE.

DATA :v_start_col TYPE i VALUE '1',

v_start_row TYPE i VALUE '1',

v_end_col TYPE i VALUE '256',

v_end_row TYPE i VALUE '65536',

v_text TYPE repti.

  • Funtion Module to upload values from excel to the Internal table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = v_start_col

i_begin_row = v_start_row

i_end_col = v_end_col

i_end_row = v_end_row

TABLES

intern = it_test

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

hope it helps you...

regards,

Praveena.