topic Re: uploading data from XL sheet in Application Development and Automation Discussions

uploading data from XL sheet

Former Member — Tue, 22 Jan 2008 04:26:05 GMT

Hi,

How to upload data from XL sheet to internal table , can u please provide the sample code.

thanks

krishna

Re: uploading data from XL sheet

gopi_narendra — Tue, 22 Jan 2008 04:28:42 GMT

* types declaration of ty_tab
TYPES : BEGIN OF ty_tab,
          bukrs TYPE anla-bukrs,
          anln1 TYPE anla-anln1,
          anln2 TYPE anla-anln2,
          anlkl TYPE anla-anlkl,
        END OF ty_tab.

* data declaration for ty_tab
DATA : it_tab TYPE TABLE OF ty_tab INITIAL SIZE 0,
       is_tab TYPE ty_tab.

* data declaration to upload excel file to internal table
DATA : it_data TYPE TABLE OF alsmex_tabline INITIAL SIZE 0,
       is_data TYPE alsmex_tabline.

* selection screen
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS       : p_file TYPE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK b1.

* at selection screen on value request
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
       EXPORTING
            program_name  = syst-cprog
            dynpro_number = syst-dynnr
            field_name    = ' '
       IMPORTING
            file_name     = p_file.

* start of selection
START-OF-SELECTION.

* sub routine to upload data from excel sheet to internal table
  PERFORM upload_data.

*&---------------------------------------------------------------------*
*&      Form  upload_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM upload_data.
  IF NOT p_file IS INITIAL.
    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
         EXPORTING
              filename                = p_file
              i_begin_col             = 1
              i_begin_row             = 1
              i_end_col               = 256
              i_end_row               = 65536
         TABLES
              intern                  = it_data
         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.
  ENDIF.

  IF NOT it_data[] IS INITIAL.
    LOOP AT it_data INTO is_data.

      AT NEW row.
        CLEAR : is_tab.
      ENDAT.

      IF is_data-col = '001'.
        MOVE is_data-value TO is_tab-bukrs.
      ENDIF.
      IF is_data-col = '002'.
        MOVE is_data-value TO is_tab-anln1.
        CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
             EXPORTING
                  input  = is_tab-anln1
             IMPORTING
                  output = is_tab-anln1.
      ENDIF.
      IF is_data-col = '003'.
        MOVE is_data-value TO is_tab-anln2.
        CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
             EXPORTING
                  input  = is_tab-anln2
             IMPORTING
                  output = is_tab-anln2.
      ENDIF.
      IF is_data-col = '004'.
        MOVE is_data-value TO is_tab-anlkl.
        CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
             EXPORTING
                  input  = is_tab-anlkl
             IMPORTING
                  output = is_tab-anlkl.
      ENDIF.

      AT END OF row.
        APPEND is_tab TO it_tab.
      ENDAT.
      CLEAR : is_data.

    ENDLOOP.
  ENDIF.

ENDFORM.                    " upload_data

Regards

Gopi

Re: uploading data from XL sheet

Former Member — Tue, 22 Jan 2008 04:36:33 GMT

HI Krishna

See this code where i had writen to UPLOAD A EXCEL SHEET AND again pass that to APPLICATION server

i think it wil be very useful for you

you can understand this very easily

REPORT ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,

f1(20),

f2(40),

f3(20),

end of it_tab1.

data : it_tab type table of ALSMEX_TABLINE with header line,

file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,

wa_tab2 type it_tab1,

w_message(100) TYPE c.

at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

STATIC = 'X'

MASK = ' '

CHANGING

file_name = file_nm

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.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '10'

i_end_row = '35'

tables

intern = it_tab

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.

loop at it_tab.

case it_tab-col.

when '002'.

wa_tab2-f1 = it_tab-value.

when '004'.

wa_tab2-f2 = it_tab-value.

when '008'.

wa_tab2-f3 = it_tab-value.

endcase.

at end of row.

append wa_tab2 to it_tab2.

clear wa_tab2.

endat.

endloop.

data : p_file TYPE rlgrap-filename value 'TEST3.txt'.

OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

*--- Display error messages if any.

IF sy-subrc NE 0.

MESSAGE e001(zsd_mes).

EXIT.

ELSE.

*---Data is downloaded to the application server file path

LOOP AT it_tab2 INTO wa_tab2.

TRANSFER wa_tab2 TO p_file.

ENDLOOP.

ENDIF.

*--Close the Application server file (Mandatory).

CLOSE DATASET p_file.

loop at it_tab2 into wa_tab2.

write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

endloop.

Re: uploading data from XL sheet

Former Member — Tue, 22 Jan 2008 08:53:09 GMT

1) Upload file using this FM into internal table.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_hfile

i_begin_col = 1

i_begin_row = 1

i_end_col = 7

i_end_row = 1000

TABLES

intern = itab_excel1

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

ENDIF.

2) Format the input data

LOOP AT itab_excel1 INTO wa_excel1.

CASE wa_excel1-col .

WHEN '1' .

wa_vbak-auart = wa_excel1-value.

WHEN '2'.

wa_vbak-vkorg = wa_excel1-value.

WHEN '3'.

wa_vbak-vtweg = wa_excel1-value.

WHEN '4'.

wa_vbak-spart = wa_excel1-value.

WHEN '5'.

wa_vbak-bstkd = wa_excel1-value.

WHEN '6'.

wa_vbak-bstdk = wa_excel1-value.

WHEN '7'.

wa_vbak-kunnr = wa_excel1-value.

ENDCASE.

AT END OF row.

CONDENSE :wa_vbak-auart,wa_vbak-vkorg,wa_vbak-vtweg,wa_vbak-spart,wa_vbak-bstkd,

wa_vbak-bstdk,wa_vbak-kunnr.

APPEND wa_vbak TO it_vbak.

CLEAR wa_vbak.

ENDAT.

ENDLOOP.

Re: uploading data from XL sheet

Former Member — Tue, 22 Jan 2008 09:23:12 GMT

Hi,

Try:

REPORT ZUPLOAD.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_datatab,

col1(30) TYPE c,

col2(30) TYPE c,

col3(4069) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab,

it_words type table of tdline with header line,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

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.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "ACTUAL DATA

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc ne 0.

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

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

ENDIF.

***********************************************************************

*END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP.

Regards,