Upload Excel File to ITAB

[Former Member]

Hey Everyone,

i need to Upload an Excel File into a Itab.

--> so far i use KD_GET_FILENAME_ON_F4 to get my Filename

then i Upload the File with GUI_UPLOAD but i only get yy##### into the Itab.

I checked all ready for KCD_EXCEL_OLE_TO_INT_CONVERT and ALSM_EXCEL_TO_INTERNAL_TABLE both are not available in the system.

Does anyone know how i can get the Data from the Excel sheet into the itab?

Thanks

Tobias

[Former Member]

Hi,

while calling FM GUI_UPLOAD pass filetype as 'DAT' , if your file contains numeric or date fields then check the import parameter "DAT_MODE" for that. Also, once go through the function module documentation for every parameter.

Best of luck !

Thanks,

Ravi Aswani

[Former Member]

Hey Ravi,

i tried to change the filetype to 'DAT' allready but i still get the same Result in my itab.

Maybe this helps for more Information the Excel contains two Lines.

MAXID:12324 H0123445 (this is the excample for the input in the Excel Lines).

i checked the documentation but didnt see any answer for this problem.

thanks

[Former Member]

DATA : itab TYPE STANDARD TABLE OF gt_sdtab WITH HEADER  LINE.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE  TEXT-001.
PARAMETERS:    P_FILE LIKE RLGRAP-FILENAME.
           
SELECTION-SCREEN END OF BLOCK B1.
At selection-screen on value-request for P_FILE.
    perform get_file_name.
FORM get_file_name.

        CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

  CHANGING
    file_name           = P_FILE.
          .
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 TABLES itab  USING p_file.


FORM upload_file  TABLES   p_it_tab       USING    p_file.

  FIELD-SYMBOLS : <fs>.
*BREAK DEVELOPER.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = '1'
      i_begin_row             = '2'
      i_end_col               = '256'
      i_end_row               = '10000'
    TABLES
      intern                  = l_intern
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc NE 0.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE : / 'File Error'.
    EXIT.
  ENDIF.

  IF l_intern[] IS INITIAL.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE : / 'No Data Uploaded'.
    EXIT.
  ELSE.
    SORT l_intern BY row col.
    LOOP AT l_intern.
      MOVE l_intern-col TO l_index.
      ASSIGN COMPONENT l_index OF STRUCTURE p_it_tab TO  <fs>.
      MOVE l_intern-value TO <fs>.
      AT END OF row.
        APPEND p_it_tab.
        CLEAR p_it_tab.
      ENDAT.
    ENDLOOP.

  ENDIF.


ENDFORM.                    " UPLOAD_FILE

Edited by: kk.adhvaryu on Sep 22, 2010 12:41 PM

[Former Member]

Hi,

After using GUI_UPLOAD use TEXT_CONVER_XLS_TO SAP. This will convert the xls to sap as shown below:

v_filename_string = p_file.

  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename                      = v_filename_string
      filetype                      = 'ASC'
      has_field_separator           = 'X'
*     HEADER_LENGTH                 = 0
*     READ_BY_LINE                  = 'X'
      dat_mode                      = ''
*   IMPORTING
*     FILELENGTH                    =
*     HEADER                        =
    TABLES
      data_tab                      = i_text_data
    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 EQ 0.


*    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_field_seperator          = 'X'
*     I_LINE_HEADER              =
      i_tab_raw_data             = i_text_data
      i_filename                 = p_file
    TABLES
      i_tab_converted_data       = itab
    EXCEPTIONS
      conversion_failed          = 1
      OTHERS                     = 2.
  IF sy-subrc EQ 0.
*    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

FOR SURE THIS WILL HELP YOU

[_IvanFemia_]

Hi,

if your file is an Excel 2007 try xlsx2abap.

Take a look to these blogs:

[xlsx2abap - Read and edit your Excel files from ABAP|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20791] [original link is broken] [original link is broken] [original link is broken];

[abap2xlsx - Generate your professional Excel spreadsheet from ABAP|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20046] [original link is broken] [original link is broken] [original link is broken];

Regards,

Ivan

[Former Member]

Hi everyone,

Here is the program to upload an excel file to an internal table.

REPORT ZEXCEL_UPLOAD.

TYPE-POOLS truxs.

TABLES : zscarr.

• Selection screen

PARAMETER p_file TYPE rlgrap-filename.

TYPES:

BEGIN OF t_tab,

carrid TYPE zscarr-carrid,

seats TYPE zscarr-total_seats,

END OF t_tab.

DATA :

t_upload TYPE STANDARD TABLE OF t_tab,

wa_upload TYPE t_tab,

it_type TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

• PROGRAM_NAME = SYST-CPROG

• DYNPRO_NUMBER = SYST-DYNNR

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

START-OF-SELECTION.

• Uploading the data in the file into internal table

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = t_upload[]

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.

• to update to the database table

LOOP AT T_UPLOAD INTO WA_UPLOAD.

ZSCARR-CARRID = WA_UPLOAD-CARRID.

ZSCARR-TOTAL_SEATS = WA_UPLOAD-SEATS.

MODIFY ZSCARR.

ENDLOOP.

Edited by: Sandeep Ramesh on Sep 22, 2010 2:43 PM

[Former Member]

Thanks everybody for the great help.

Works fine now.

Tobias