Upload EXCEL file From Application Server into Internal Table

[Former Member]

Hello All,

I need to Upload Excel file into Internal Table from application server.

File format is :

Header : MATNR Description Plant

Data : 1 abc c101

2 aaa c101

3 aaa c101

4 bbc c101

Would you please help me how to read this file?

Regards,

[Former Member]

hi

try using

DATA:

it_text TYPE truxs_t_text_data .

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER =

i_tab_raw_data = it_text

i_filename = p_file " path of file

TABLES

i_tab_converted_data = t_material " internal table to store material

EXCEPTIONS

conversion_failed = 1

make sure there are no texts before the data, remove the header titlesin the excel sheet..

Thanks

Sharath

Edited by: Sharath Panuganti on Feb 6, 2009 12:00 PM

[Former Member]

hi ,

I did used it but it's giving me the conversion error.

thanks.

[Former Member]

Hi,

there are lot of threads on this like:

Regards,

Rahul

[Former Member]

HI,

To read data from the application server you need to use OPEN DATASET, READ DATASET & CLOSE DATASET statemetns.

OPEN DATASET p_file FOR INPUT IN TEXT MODE ENCODING DEFAULT.
*--- Display error messages if any.
  IF sy-subrc NE 0.
    MESSAGE e001(zsd_mes).
    EXIT.
  ELSE.
DO.
    READ DATASET p_file INTO datatab.
     IF sy-subrc NE 0.
      EXIT.
    ELSE.
      APPEND datatab.
    ENDIF.
  ENDDO.
 
*--Close the Application server file (Mandatory).
  CLOSE DATASET p_file.

Edited by: Avinash Kodarapu on Feb 6, 2009 4:34 PM

Edited by: Avinash Kodarapu on Feb 6, 2009 4:35 PM

[Former Member]

Hi,

Make sure the the of the field in internal table tat you are creating is of C type

example

ID(20) TYPE C.

NOW USE THIS FUNCTION MODULE

AS you previously used

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*    I_FIELD_SEPERATOR          = 'X'
     i_line_header              = 'X'
      i_tab_raw_data             = it_raw
      i_filename                 = 'c:\temp.xls'  "file name
    TABLES
      i_tab_converted_data       = it_final[]
   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.

Thanks

Arun

[Former Member]

go throught these links :

you will be getting ur answer.

[BH2408]

Hi ,

Use the tcode cg3z to upload the data from the presntation server to Application server and , Use the Open dataset and Transfer , Upload into one Internal table.

Regards,

bharani

[Former Member]

Hi Reddy,

Below is the source code to download the data from the Application server into a internal table.

+*perform fileupload using p_afile.*+_

form file_upload using v_lfilename like v_filename.

data: v_msg(100).

• Open the file for upload in text mode, and trap any messages.*

open dataset v_lfilename for input in text mode message v_msg.

if sy-subrc ne 0.

• Input file not found!*

• Process the error with an abort*

message e006(zmsg) with v_lfilename.

else.

• The file has opened successfully. Upload every line in the file.*

do.

• Clear the work area, and read the record from the dataset.*

clear v_rawdata.

read dataset v_lfilename into v_rawdata.

if sy-subrc ne 0.

• The end of the file has been reached, so exit the 'DO' loop.*

exit.

else.

v_linesread = v_linesread + 1.

endif.

• Append the raw data line to the raw data internal table*

append v_rawdata to tbl_rawdata.

enddo.

close dataset v_lfilename.

endif. "

endform. " file_upload

Regards,

Sunil B.

[Former Member]

Hi,

try this ....

TYPES

type_s_raw(4096) TYPE c.

data fs_tab TYPE type_s_raw.

data t_tab LIKE

STANDARD TABLE

OF fs_tab.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER =

i_tab_raw_data = t_tab

i_filename = <file path >

TABLES

i_tab_converted_data = internal table

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.

Edited by: Sreesudha Gullapalli on Feb 6, 2009 1:10 PM

[Former Member]

Hello Vandu,

Your requirement to get Application data to be stored in an Excel sheet, is pretty logical task.

I tried with the following example and it really worked.

The following report draws records from BKPF table and stores the same on Application Server. Further, it saves the whole information onto an excel-sheet.

Selection-screen seeks Application-server file-name and Presentation-server file-name as well.

REPORT  zahackson_app_excel NO STANDARD PAGE HEADING.

TYPE-POOLS truxs.

*---------------------------------------------------------------------*
* Structure for Accounting Information                                *
*---------------------------------------------------------------------*
TYPES:
  BEGIN OF type_s_bkpf,
    bukrs TYPE bkpf-bukrs,             " Company Code
    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
  END OF type_s_bkpf.                  " BEGIN OF TYPE_S_BKPF

*---------------------------------------------------------------------*
* Field String for Accounting Information                             *
*---------------------------------------------------------------------*
DATA:
  fs_bkpf TYPE type_s_bkpf.

*---------------------------------------------------------------------*
* Internal Table For Accounting Information                           *
*---------------------------------------------------------------------*
DATA:
    t_bkpf LIKE
  STANDARD TABLE
        OF fs_bkpf.

*---------------------------------------------------------------------*
* Work Variables                                                      *
*---------------------------------------------------------------------*
DATA:
  w_filename TYPE string,              " Selected Filename
  w_filename1 TYPE rlgrap-filename,    " Selected Filename
  w_tab_raw_data TYPE truxs_t_text_data.
                                       " Raw Data

*---------------------------------------------------------------------*
*      INITIALIZATION                                                 *
*---------------------------------------------------------------------*
INITIALIZATION.
  PARAMETERS:
   p_filedw(128) VISIBLE LENGTH 15,    " File Name to be open
   p_file TYPE dxfields-longpath.      " Name of File to be saved

*---------------------------------------------------------------------*
*      TOP-OF-PAGE                                                    *
*---------------------------------------------------------------------*
TOP-OF-PAGE.
  FORMAT COLOR 4 INTENSIFIED ON.
  WRITE: 30(255) 'Downloaded Data'.
  WRITE:/5 'Company Code'(001),
        21 'Accounting Document Number'(003),
        52 'Fiscal Year'(004),
        67 'Document Type'(005),
        84(255) 'Document Date in Document'(006).
  FORMAT COLOR OFF.

*---------------------------------------------------------------------*
*      AT SELECTION-SCREEN ON VALUE-REQUEST                           *
*---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM save_customer_file.
  CONCATENATE w_filename1 w_filename INTO p_file.

*---------------------------------------------------------------------*
*      START-OF-SELECTION                                             *
*---------------------------------------------------------------------*
START-OF-SELECTION.
  IF ( p_file IS INITIAL ) OR ( p_filedw IS INITIAL ).
    MESSAGE 'No File Selected' TYPE 'S' DISPLAY LIKE 'E'.
  ELSE.
    PERFORM open_file.
    PERFORM save_file.
  ENDIF.                               " IF ( P_FILE IS INITIAL )...

*&---------------------------------------------------------------------*
*&      Form  save_customer_file
*&---------------------------------------------------------------------*
* This Subroutine saves Customer File on the Presentation Server.
*----------------------------------------------------------------------*
* This Subroutine has got no Interface Parameters.
*----------------------------------------------------------------------*
FORM save_customer_file .
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
*  EXPORTING
*    WINDOW_TITLE         =
*    DEFAULT_EXTENSION    = ' '
*    DEFAULT_FILE_NAME    =
*    file_filter          = ' '
*    INITIAL_DIRECTORY    =
    CHANGING
      filename             = w_filename
      path                 = w_filename1
      fullpath             = w_filename1.
*    USER_ACTION          =
*  EXCEPTIONS
*    CNTL_ERROR           = 1
*    ERROR_NO_GUI         = 2
*    NOT_SUPPORTED_BY_GUI = 3
*    others               = 4

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.                               " IF SY-SUBRC <> 0
ENDFORM.                               " FORM SAVE_CUSTOMER_FILE

*&---------------------------------------------------------------------*
*&      Form  save_file
*&---------------------------------------------------------------------*
* This Subroutine facilitates download on Presentation Server.
*----------------------------------------------------------------------*
* This Subroutine has got no Interface Parameters.
*----------------------------------------------------------------------*
FORM save_file .

  CONCATENATE w_filename1 w_filename INTO w_filename1.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
     EXPORTING
*   I_FIELD_SEPERATOR =
*   I_LINE_HEADER  = 'X'
     I_TAB_RAW_DATA = w_tab_raw_data
     I_FILENAME     = w_filename1
    TABLES
     I_TAB_CONVERTED_DATA = t_bkpf
  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.
  ELSE.
    LOOP AT t_bkpf INTO fs_bkpf.
      WRITE:/ fs_bkpf-bukrs under text-001,
              fs_bkpf-belnr under text-003,
              fs_bkpf-gjahr under text-004,
              fs_bkpf-blart under text-005,
              fs_bkpf-bldat under text-006.
    ENDLOOP.                           " LOOP AT T_BKPF INTO FS_BKPF
  ENDIF.                               " IF SY-SUBRC NE 0
ENDFORM.                               " FORM SAVE_FILE

*&---------------------------------------------------------------------*
*&      Form  open_file
*&---------------------------------------------------------------------*
* This Subroutine fetches data from the File saved on Application Server
*----------------------------------------------------------------------*
* This Subroutine has got no Interface Parameters.
*----------------------------------------------------------------------*
FORM open_file .
  OPEN DATASET p_filedw FOR INPUT IN TEXT MODE ENCODING DEFAULT.
  DO.
    READ DATASET p_filedw INTO fs_bkpf.
    IF sy-subrc <> 0.
      EXIT.
    ELSE.
      APPEND fs_bkpf TO t_bkpf.
    ENDIF.                             " IF SY-SUBRC EQ 0
  ENDDO.                               " DO
  CLOSE DATASET p_filedw.
ENDFORM.                               " FORM OPEN_FILE

*----------------------------------------------------------------------*
*******************END OF PROGRAM***************************************
*----------------------------------------------------------------------*

Thanks: Zahackson