topic Re: upload data from excel in Application Development and Automation Discussions

upload data from excel

Former Member — Thu, 15 Sep 2005 13:35:19 GMT

Hi all,

Is there any function module to upload data from Excel sheet to internal table?

Thanks,

Shashikanth.

Re: upload data from excel

Former Member — Thu, 15 Sep 2005 13:42:17 GMT

Hi,

GUI_DOWNLOAD is used for sap to excel

GUI_UPLOAD is used for excel to sap.

below is a sample programme for downloading and uploading from excel to sap or sap to excel.


 This program once created will allow you to download or upload table
* data from any SAP table. It has the functionality to allow you to
* select whether data should be appended or original data cleaed before
* inserting new data.
* This is very useful when attempting to transfer data from one client
* to another
*----------------------------------------------------------------------

REPORT ytest5 LINE-SIZE 80
                LINE-COUNT 65
                NO STANDARD PAGE HEADING.

TABLES: dd02l, dd03l.


* selection screen
SELECTION-SCREEN BEGIN OF BLOCK b00 WITH FRAME TITLE text-b00.
*
SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-b01.
PARAMETERS: tabname     LIKE dd02l-tabname OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b01.
*
SELECTION-SCREEN BEGIN OF BLOCK b03 WITH FRAME TITLE text-b03.
PARAMETERS: path(30)    TYPE c DEFAULT 'C:SAPWorkdir'.
SELECTION-SCREEN END OF BLOCK b03.
*
SELECTION-SCREEN BEGIN OF BLOCK b04 WITH FRAME TITLE text-b04.
PARAMETERS: p_exp RADIOBUTTON GROUP radi,
            p_imp RADIOBUTTON GROUP radi,
            p_clear     AS CHECKBOX.
SELECTION-SCREEN END OF BLOCK b04.

SELECTION-SCREEN END OF BLOCK b00.

* data
DATA: q_return     LIKE syst-subrc,
      err_flag(1)  TYPE c,
      answer(1)    TYPE c,
      w_text1(62)  TYPE c,
      w_text2(40)  TYPE c,
      winfile(128) TYPE c,
      w_system(40) TYPE c,
      winsys(7)    TYPE c,
      zname(8)     TYPE c,
      w_line(80)   TYPE c.

* internal tables
DATA : BEGIN OF textpool_tab OCCURS 0.
        INCLUDE STRUCTURE textpool.
DATA : END OF textpool_tab.

* table for subroutine pool
DATA : itab(80) OCCURS 0.

* events
INITIALIZATION.
  PERFORM check_system.
*
AT SELECTION-SCREEN ON tabname.
  PERFORM check_table_exists.
*
START-OF-SELECTION.
  PERFORM init_report_texts.
  PERFORM request_confirmation.
*
END-OF-SELECTION.
  IF answer = 'J'.
    PERFORM execute_program_function.
  ENDIF.
*
TOP-OF-PAGE.
  PERFORM process_top_of_page.

* forms
*---------------------------------------------------------------------*
*       FORM CHECK_TABLE_EXISTS                                      *
*---------------------------------------------------------------------*
FORM check_table_exists.
  SELECT SINGLE * FROM dd02l
  INTO CORRESPONDING FIELDS OF dd02l
  WHERE tabname = tabname.
  CHECK syst-subrc NE 0.
  MESSAGE e402(mo) WITH tabname.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM INIT_REPORT_TEXTS                                        *
*---------------------------------------------------------------------*
FORM init_report_texts.
  READ TEXTPOOL syst-repid
  INTO textpool_tab LANGUAGE syst-langu.
  LOOP AT textpool_tab
  WHERE id EQ 'R' OR id EQ 'T'.
    REPLACE '&1............................'
    WITH tabname INTO textpool_tab-entry.
    MODIFY textpool_tab.
  ENDLOOP.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM REQUEST_CONFIRMATION                                     *
*---------------------------------------------------------------------*
FORM request_confirmation.

* import selected, confirm action
  IF p_imp = 'X'.
*   build message text for popup
    CONCATENATE 'Data for table'
                 tabname
                 'will be imported' INTO w_text1 SEPARATED BY space.
*   check if delete existing selected, and change message text
    IF p_clear = ' '.
      w_text2 = 'and appended to the end of existing data'.
    ELSE.
      w_text2 = 'Existing Data will be deleted'.
    ENDIF.

    CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
         EXPORTING
              defaultoption  = 'N'
              textline1      = w_text1
              textline2      = w_text2
              titel          = 'Confirm Import of Data'
              cancel_display = ' '
         IMPORTING
              answer         = answer
         EXCEPTIONS
              OTHERS         = 1.
  ELSE.
*   export selected, set answer to yes so export can continue
    answer = 'J'.
  ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM EXECUTE_PROGRAM_FUNCTION                                 *
*---------------------------------------------------------------------*
FORM execute_program_function.
  PERFORM build_file_name.
  CLEAR: q_return,err_flag.

  IF p_imp = 'X'.
    PERFORM check_file_exists.
    CHECK err_flag = ' '.
    PERFORM func_import.
  ELSE.
    PERFORM func_export.
  ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM BUILD_FILE_NAME                                          *
*---------------------------------------------------------------------*
FORM build_file_name.
  MOVE path TO winfile.
  WRITE '' TO winfile+30.
  WRITE tabname TO winfile+31.
  WRITE '.TAB' TO winfile+61(4).
  CONDENSE winfile NO-GAPS.
ENDFORM.
*---------------------------------------------------------------------*
*       FORM CHECK_FILE_EXISTS                                        *
*---------------------------------------------------------------------*
FORM check_file_exists.

  CALL FUNCTION 'WS_QUERY'
       EXPORTING
            filename = winfile
            query    = 'FE'
       IMPORTING
            return   = q_return
       EXCEPTIONS
            OTHERS   = 1.

  IF syst-subrc NE 0 OR q_return NE 1.
    err_flag = 'X'.
  ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
*     FORM func_export                                              *
*---------------------------------------------------------------------*
FORM func_export.
  CLEAR itab. REFRESH itab.

  APPEND 'PROGRAM SUBPOOL.' TO itab.

  APPEND 'FORM DOWNLOAD.' TO itab.
  APPEND 'DATA: BEGIN OF IT_TAB OCCURS 0.' TO itab.
  CONCATENATE 'INCLUDE STRUCTURE'
              tabname
              '.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'DATA: END OF IT_TAB.' TO itab.

  CONCATENATE 'SELECT * FROM'
              tabname
              'INTO TABLE IT_TAB.' INTO w_line  SEPARATED BY space.
  APPEND w_line TO itab.

  APPEND 'CALL FUNCTION ''WS_DOWNLOAD''' TO itab.
  APPEND 'EXPORTING' TO itab.
  CONCATENATE 'filename = ' ''''
              winfile '''' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'filetype = ''DAT''' TO itab.
  APPEND 'TABLES' TO itab.
  APPEND 'DATA_TAB = IT_TAB.' TO itab.

  APPEND 'DESCRIBE TABLE IT_TAB LINES sy-index.' TO itab.

  APPEND 'FORMAT COLOR COL_NORMAL INTENSIFIED OFF.' TO itab.
  APPEND 'WRITE: /1 syst-vline,' TO itab.
  APPEND '''EXPORT'',' TO itab.
  APPEND '15 ''data line(s) have been exported'',' TO itab.
  APPEND '68 syst-index,' TO itab.
  APPEND '80 syst-vline.' TO itab.
  APPEND 'ULINE.' TO itab.

  APPEND 'ENDFORM.' TO itab.

  GENERATE SUBROUTINE POOL itab NAME zname.
  PERFORM download IN PROGRAM (zname).
ENDFORM.
*---------------------------------------------------------------------*
*       FORM func_import                                              *
*---------------------------------------------------------------------*
FORM func_import.
  CLEAR itab. REFRESH itab.
  APPEND 'PROGRAM SUBPOOL.' TO itab.

  APPEND 'FORM UPLOAD.' TO itab.
  APPEND 'DATA: BEGIN OF IT_TAB OCCURS 0.' TO itab.
  CONCATENATE 'INCLUDE STRUCTURE'
              tabname
              '.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'DATA: END OF IT_TAB.' TO itab.
  APPEND 'DATA: BEGIN OF IT_TAB2 OCCURS 0.' TO itab.
  CONCATENATE 'INCLUDE STRUCTURE'
              tabname
              '.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'DATA: END OF IT_TAB2.' TO itab.

  APPEND 'CALL FUNCTION ''WS_UPLOAD''' TO itab.
  APPEND 'EXPORTING' TO itab.
  CONCATENATE 'filename = ' ''''
              winfile '''' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'filetype = ''DAT''' TO itab.
  APPEND 'TABLES' TO itab.
  APPEND 'DATA_TAB = IT_TAB.' TO itab.

  IF p_clear = 'X'.
    CONCATENATE 'SELECT * FROM'
                tabname
                'INTO TABLE IT_TAB2.' INTO w_line SEPARATED BY space.
    APPEND w_line TO itab.

    APPEND 'LOOP AT IT_TAB2.' TO itab.
    CONCATENATE 'DELETE'
                tabname
                'FROM IT_TAB2.' INTO w_line SEPARATED BY space.
    APPEND w_line TO itab.
    APPEND 'ENDLOOP.' TO itab.
    APPEND 'COMMIT WORK.' TO itab.
  ENDIF.

  APPEND 'LOOP AT IT_TAB.' TO itab.
  CONCATENATE 'MODIFY'
              tabname
              'FROM IT_TAB.' INTO w_line SEPARATED BY space.
  APPEND w_line TO itab.
  APPEND 'ENDLOOP.' TO itab.

  APPEND 'DESCRIBE TABLE IT_TAB LINES sy-index.' TO itab.

  APPEND 'FORMAT COLOR COL_NORMAL INTENSIFIED OFF.' TO itab.
  APPEND 'WRITE: /1 syst-vline,' TO itab.
  APPEND '''IMPORT'',' TO itab.
  APPEND '15 ''data line(s) have been imported'',' TO itab.
  APPEND '68 syst-index,' TO itab.
  APPEND '80 syst-vline.' TO itab.
  APPEND 'ULINE.' TO itab.

  APPEND 'ENDFORM.' TO itab.

  GENERATE SUBROUTINE POOL itab NAME zname.
  PERFORM upload IN PROGRAM (zname).
ENDFORM.
*---------------------------------------------------------------------*
*       Form  CHECK_SYSTEM
*            Check users workstation is running
*            WINDOWS 95, or WINDOWS NT.
*            OS/2 uses 8.3 file names which are no good for
*            this application as filenames created are 30 char
*            same as table name.
*            You could change the logic to only use the first 8 chars
*            of the table name for the filename, but you could possibly
*            get problems if users had exported already with a table
*            with the same first 8 chars.
*            As an alternate method you could request the user to input
*            the full path including filename and remove the logic to
*            build the path using the table name.
*---------------------------------------------------------------------*
FORM check_system.
  CALL FUNCTION 'WS_QUERY'
       EXPORTING
            query  = 'WS'
       IMPORTING
            return = winsys.

  IF winsys NE 'WN32_95'.
    WRITE: 'Windows NT or Windows 95/98 is required'.
    EXIT.
  ENDIF.

ENDFORM.                               " CHECK_SYSTEM
*---------------------------------------------------------------------*
*       FORM PROCESS_TOP_OF_PAGE                                      *
*---------------------------------------------------------------------*
FORM process_top_of_page.
  FORMAT COLOR COL_HEADING INTENSIFIED ON.
  ULINE.

  CONCATENATE syst-sysid
              syst-saprl
              syst-host INTO w_system SEPARATED BY space.

  WRITE : AT /1(syst-linsz) w_system CENTERED.
  WRITE : AT 1 syst-vline, syst-uname.
  syst-linsz = syst-linsz - 11.
  WRITE : AT syst-linsz syst-repid(008).
  syst-linsz = syst-linsz + 11.
  WRITE : AT syst-linsz syst-vline.

  LOOP AT textpool_tab WHERE id EQ 'R'.
    WRITE : AT /1(syst-linsz) textpool_tab-entry CENTERED.
  ENDLOOP.
  WRITE : AT 1 syst-vline, syst-datum.
  syst-linsz = syst-linsz - 11.
  WRITE : AT syst-linsz syst-tcode(004).
  syst-linsz = syst-linsz + 11.
  WRITE : AT syst-linsz syst-vline.

  LOOP AT textpool_tab WHERE id EQ 'T'.
    WRITE : AT /1(syst-linsz) textpool_tab-entry CENTERED.
  ENDLOOP.
  WRITE : AT 1 syst-vline, syst-uzeit.
  syst-linsz = syst-linsz - 11.
  WRITE : AT syst-linsz 'Page', syst-pagno.
  syst-linsz = syst-linsz + 11.
  WRITE : AT syst-linsz syst-vline.
  ULINE.

  FORMAT COLOR COL_HEADING INTENSIFIED OFF.
  LOOP AT textpool_tab WHERE id EQ 'H'.
    WRITE : AT /1(syst-linsz) textpool_tab-entry.
  ENDLOOP.

  ULINE.
ENDFORM.

regards,

venu.

Re: upload data from excel

Former Member — Thu, 15 Sep 2005 13:45:43 GMT

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_inp

i_begin_col = 1

i_begin_row = 1

i_end_col = 100

i_end_row = 65536

TABLES

intern = l_t_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

Re: upload data from excel

Former Member — Thu, 15 Sep 2005 13:50:01 GMT

Hi Shashikant

There is a beautiful FM for your purpose: ALSM_EXCEL_TO_INTERNAL_TABLE

It uploads the data from presentation server. Good thing is that you can specify which columns and how many rows you want to upload.

Example:

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\TEST.XLS'

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 6

I_END_ROW = 500

TABLES

INTERN = ITAB

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.

in above example, the excel sheet can even contain first row as row for headings, as it will read data starting from row 2 :

I_BEGIN_ROW = 2

Cheers

Ashish

Re: upload data from excel

Former Member — Thu, 15 Sep 2005 13:57:02 GMT

Hi Shashikanth

For the above FM 'ALSM_EXCEL_TO_INTERNAL_TABLE', i just forgot to mention the internal table's type, its: ALSMEX_TABLINE

Example:

DATA: ITAB TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

Cheers

Ashish

Re: upload data from excel

Former Member — Thu, 15 Sep 2005 13:59:35 GMT

Hi Shashikanth

For the above FM 'ALSM_EXCEL_TO_INTERNAL_TABLE', i just forgot to mention the internal table's type, its: ALSMEX_TABLINE

Example:

DATA: ITAB TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

Cheers

Ashish

Re: upload data from excel

Former Member — Thu, 10 Nov 2005 10:26:03 GMT

Hi Venu Narayana,

i am very intersted in in this function you posted.And I try to run by myself.But I met the problem.

I gave the table name(e.g. /TEST/FIRST) in field of 'TABNAME' anf the path was 'C:\SAPWORKDIR',kept 'p_exp',but got the popup message:

'Error when opening the download file'.

After I continue,I got the message:

'data line(s) have been exported'.

And the line number of my table name is shown correcty.But I the data is not under the expected directory.

Is anything wrong with my table name?

It would be greaty appreciated if I got some feedback from you.

Thanks in advance.

Regards,

Liying

Re: upload data from excel

Former Member — Thu, 10 Nov 2005 10:54:02 GMT

I solved it.