FM to Upload and download from excel sheet

[Former Member]

Hi All,

My requiremet is to upload data from an excel sheet into an internal table for processing.

I have checked out GUI_UPLOAD for the same..this is not sufficing my requirement.

Also i need to download data from an internal table(having one field of type string having separators) into an excel sheet...

Could anyone provide me the FM to do the above requirement?

Thanks

Shiva

[former_member181995]

Sk,

use ws_excel for download into excel.

and use below for uploading.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR          =
      i_line_header              = 'X'
      i_tab_raw_data             = it_raw
      i_filename                 = pfile
    TABLES
      i_tab_converted_data       = it_excel[]
*   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.

Amit.

[Former Member]

Hii!

Suppose you have a file named Book1 on your desktop.

Check out this sample code

REPORT  z_file3.

DATA: fname(40),
      w_line TYPE i VALUE 1,
      w_file TYPE rlgrap-filename.

DATA:
  t_tab LIKE
  TABLE OF ALSMEX_TABLINE
  WITH HEADER LINE.

DATA: fs_tab LIKE LINE OF t_tab.


w_file = 'C:\Documents and Settings\ABC\Desktop\Book1.xls'.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = w_file
    i_begin_col                   = 1
    i_begin_row                   = 1
    i_end_col                     = 10
    i_end_row                     = 100
  tables
    intern                        = t_tab
 EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3
          .
IF sy-subrc NE 0.
  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
  WRITE: 'UPLOAD SUCCESSFUL'.
ENDIF.

fname = '.\z_file.xls'.
OPEN DATASET fname FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
LOOP AT t_tab INTO fs_tab.
  TRANSFER fs_tab TO fname.
ENDLOOP.

IF sy-subrc EQ 0.
  WRITE: / 'FILE OPENED ON APPS SERVER'.
ELSE.
  WRITE: / 'FILE COULD NOT BE OPENED'.
ENDIF.

Regards

Abhijeet

[Former Member]

Hi,

For Upload from Presentation server use

ALSM_EXCEL_TO_INTERNAL_TABLE function module.

PARAMETERS
  p_file TYPE ibipparms-path. 

DATA:
  w_dfile TYPE string.                 " Data Storing File.

*"-------------------------------------------------------------------"*
*Internal Table Declaration To Hold Accounts Data.
*"-------------------------------------------------------------------"*
DATA:
  BEGIN OF fs_bkpf,
    belnr TYPE bkpf-belnr,             " Accounting Document Number.
    gjahr TYPE bkpf-gjahr,             " Fiscal Year.
    blart TYPE bkpf-blart,             " Document type.
    bldat TYPE bkpf-bldat,             " Document Date in Document.
    budat TYPE bkpf-budat,             " Posting Date in the Document.
    monat TYPE bkpf-monat,             " Fiscal Period.
    cpudt TYPE bkpf-cpudt,             " Accounting Document Entry Date
  END OF fs_bkpf.                      " BEGIN OF fs_bkpf.
*"-------------------------------------------------------------------"*
*Internal Table Declaration To Hold Accounts Data.
*"-------------------------------------------------------------------"*
DATA:
  t_bkpf LIKE STANDARD TABLE           " Internal Table.
      OF fs_bkpf.
DATA:
  t_excel TYPE TABLE OF alsmex_tabline,
  fs_excel LIKE LINE OF t_excel.

 CALL FUNCTION 'F4_FILENAME'
* EXPORTING
*   PROGRAM_NAME        = SYST-CPROG
*   DYNPRO_NUMBER       = SYST-DYNNR
*   FIELD_NAME          = ' '
   IMPORTING
     file_name           = p_file
            .
  w_dfile = p_file.


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = '1'
      i_begin_row             = '1'
      i_end_col               = '7'
      i_end_row               = '4'
    TABLES
      intern                  = t_excel
    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.
    MESSAGE 'Upload Unsucessful'(001) TYPE 'S'.
  ELSE.
    MESSAGE 'Uploading Done'(002) TYPE 'S'.
  ENDIF.

For Download int Presentation Server Use :

GUI_DOWNLOAD.

call function 'GUI_DOWNLOAD'
    exporting
*     BIN_FILESIZE                  =
     filename  = 'C:\Documents and Settings\yashsap\Desktop\w_dfile.xls'
        filetype                    = 'ASC'
*     APPEND                        = 'X'
        write_field_separator       = 'X'
*     HEADER                        = '00'
*     TRUNC_TRAILING_BLANKS         = ' '
*     WRITE_LF                      = 'X'
*     COL_SELECT                    = ' '
*     COL_SELECT_MASK               = ' '
*     DAT_MODE                      = ' '
*     CONFIRM_OVERWRITE             = ' '
*     NO_AUTH_CHECK                 = ' '
*     CODEPAGE                      = ' '
*     IGNORE_CERR                   = ABAP_TRUE
*     REPLACEMENT                   = '#'
*     WRITE_BOM                     = ' '
*   IMPORTING
*     FILELENGTH                    =
    tables
      data_tab                      = t_bkpf
   exceptions
     file_write_error              = 1
     no_batch                      = 2
     gui_refuse_filetransfer       = 3
     invalid_type                  = 4
     no_authority                  = 5
     unknown_error                 = 6
     header_not_allowed            = 7
     separator_not_allowed         = 8
     filesize_not_allowed          = 9
     header_too_long               = 10
     dp_error_create               = 11
     dp_error_send                 = 12
     dp_error_write                = 13
     unknown_dp_error              = 14
     access_denied                 = 15
     dp_out_of_memory              = 16
     disk_full                     = 17
     dp_timeout                    = 18
     file_not_found                = 19
     dataprovider_exception        = 20
     control_flush_error           = 21
     others                        = 22
            .
  if sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    message 'Download Not Complete' type 'S'.
  endif.                               " IF sy-subrc <> 0.
endform.                               " Form download_file.

Regards,

Sujit

[Former Member]

Hi Shiva,

Fucntion module to upload from excel to internal table

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                = file_path1
    i_begin_col             = 1
    i_begin_row             = 1
    i_end_col               = 10
    i_end_row               = 10
  TABLES
    intern                  = t_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-msg

Hope this helps you.

Regards,

Chandra Sekhar

[Former Member]

Hi Shiva,

FM's to upload Excel:

GUI_UPLOAD

KCD_EXCEL_OLE_TO_INT_CONVERT

ALSM_EXCEL_TO_INTERNALTABLE

refer to link for sample program:

http://diocio.wordpress.com/2007/02/12/sap-upload-excel-document-into-internal-table-2/

FM's to download in Excel;

RH_START_EXCEL_WITH_DATA starts Excel with the contents of an internal table. This function finds Excel in the desktop registry. It also uses a local PC working directory to save the file (that's what the 'W' value for data path flag does). Very transparent to user!

SAP_CONVERT_TO_XLS_FORMAT : Convert data to Microsoft Excel format.

http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

With luck,

Pritam.

[Former Member]

hi,

Might want to try this FM :

KCD_EXCEL_OLE_TO_INT_CONVERT

do reward if helpful

preet

[Former Member]

Hello All,

What about the FM to downlaod data into excel directly from an internal table?

Regards

Shiva

[Former Member]

Hi Shiva,

Download into excel from internal table:

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
i_filename = gf_file
TABLES
i_tab_sap_data = gt_output
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
 
IF sy-subrc = 0.
gf_error = 1. "To check if the data is converted to excel format
ENDIF.

Check this link :

Hope this helps you.

Regards,

Chandra Sekhar

Edited by: Chandrasekhar Gandla on Jul 17, 2008 11:20 AM

Edited by: Chandrasekhar Gandla on Jul 17, 2008 11:21 AM

[Former Member]

Uploading the data from excek you can use : ALSM_EXCEL_TO_INTERNAL_TABLE function module.

Downloading the data into excel u can use GUI_download passing the value in field seperater and file tye 'ASC' and if you have req of field name to be displayed at the top levele then use this function module two times, in first time declare a table of field name and append the values and pass the table in function module and in second time pass ur internal table and pass the value of APPEND as 'X'.

[Former Member]

Hi Shiva.

ALSM_EXCEL_TO_INTERNAL_TABLE - Suitable function module

I would like to suggest a reference, it is quite similar to your issue,

[SDN - Standard Reference - PDF - Upload data from Excel file to Internal tables|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/36f08758-0a01-0010-c291-c03004aeb1af]

[SDN - Reference for transferring data from excel to internal table - function module|;

Hope that's usefull.

Good Luck & Regards.

Harsh Dave