Excel file not getting uploaded in the internal table

[former_member201541]

Can anyone help me in uploading the excel file data into the internal table. I have tried using " ALSM_EXCEL_TO_INTERNAL_TABLE" and " TEXT_CONVERT_XLS_TO_SAP" FM but I'm getting blank data in the internal table. I also used  "GUI_UPLOAD" but there only .txt file is getting uploaded not .xls

[former_member193737]

what is the error u r getting or give u r program

[former_member201541]

No error, blank alv is getting displayed.

INITIALIZATION.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR gd_fname.

   CALL FUNCTION 'F4_FILENAME'

*EXPORTING

*  field_name = gd_fname

    IMPORTING

      file_name           = file_name.

gd_fname = file_name.

AT SELECTION-SCREEN OUTPUT.

START-OF-SELECTION.

   gd_repid = sy-repid.

   f_name = gd_fname.

   f_name1 = gd_fname.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

      EXPORTING

        i_field_seperator    = 'X'

        i_line_header        = 'X'

        i_tab_raw_data       = raw

        i_filename           = f_name1

      TABLES

        i_tab_converted_data = it_tab

      EXCEPTIONS

        conversion_failed    = 1

        OTHERS               = 2.

[former_member193737]

check this sample How to use FM ALSM_EXCEL_TO_INTERNAL_TABLE - Code Gallery - SCN Wiki

[former_member289261]

disable the exporting parameter 'i_field_seperator    = 'X''

[Former Member]

Hi Ajinkya,

I used the ALSM_EXCEL_TO_INTERNAL_TABLE like this and I can retrieve values.

See if it works for you too.

(Top Include)

TYPES:  gy_raw_data   TYPE STANDARD TABLE OF alsmex_tabline,

FORM f_get_data  CHANGING ct_data TYPE gy_raw_data.

   IF sy-batch NE abap_true.
     CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
         filename                = p_file
         i_begin_col             = 1
         i_begin_row             = 1
         i_end_col               = 99
         i_end_row               = 999
       TABLES
         intern                  = ct_data
       EXCEPTIONS
         inconsistent_parameters = 1
         upload_ole              = 2
         OTHERS                  = 3.
     IF sy-subrc <> 0.
           MESSAGE 'Error in reading file'(009)
           TYPE c_information.
           LEAVE LIST-PROCESSING.
     ENDIF.
   ENDIF.

ENDFORM.

[Private_Member_3759]

Hi Ajinkya,

Please try the following code for uploading file and let me know in case of any issue.

   DATA: it_upload_data TYPE TABLE OF string,
      wa_upload_data TYPE string,
      g_windowtitle  type string,          "Window title
      gt_filetable   type filetable,       "File selected
      gv_rc          type i,               "Return-code from Me
      gw_file        type file_table,
      w_off          type i,
      gv_filename    type string.

SELECTION-SCREEN BEGIN OF BLOCK bl_filetype WITH FRAME TITLE text-002.
PARAMETERS :   p_pres TYPE sapb-sappfad MODIF ID id1.
SELECTION-SCREEN END OF BLOCK bl_filetype.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_pres.

*   * F4 on local pc
  g_windowtitle = 'Select file'.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = g_windowtitle
    CHANGING
      file_table              = gt_filetable
      rc                      = gv_rc
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.
  CHECK ( sy-subrc IS INITIAL ) AND ( NOT gt_filetable[] IS INITIAL ).

  READ TABLE gt_filetable INTO gw_file INDEX 1.
  p_pres = gw_file-filename.

  REPLACE ALL OCCURRENCES OF '\' IN p_pres WITH '\' IN CHARACTER MODE
  REPLACEMENT OFFSET w_off.              " Get the position of  last \

  ADD 1 TO w_off.

* Clear all variables
  CLEAR: w_off,  g_windowtitle,
         gt_filetable,
         gw_file.
  REFRESH: gt_filetable.

START-OF-SELECTION.
  gv_filename = p_pres.
  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename                = gv_filename
      filetype                = 'ASC'
      has_field_separator     = 'X'
    TABLES
      data_tab                = it_upload_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.

Try debugging and you will find the values in internal table.

Regards,

Ankita

[asim_isik]

hi ,

Excel and internal table have to have same fields and also in same sequences.. Did you check this condition ?

[former_member221194]

hi frnd,

Below code will work...

DATA: BEGIN OF ITAB OCCURS 0,

       KUNNR LIKE KNA1-KUNNR,

*       BUKRS LIKE KNA1-BUKRS,

       ANRED LIKE KNA1-anred,

       NAME1 LIKE KNA1-NAME1,

       ORT01 LIKE KNA1-ORT01,

      END OF ITAB.

DATA : I_EXCEL TYPE STANDARD TABLE OF ALSMEX_TABLINE WITH HEADER LINE ,

       W_EXCEL TYPE ALSMEX_TABLINE .

SELECTION-SCREEN BEGIN OF BLOCK B1.

PARAMETERS : FILENAME TYPE RLGRAP-FILENAME,

             P_BEG TYPE I ,

             P_END TYPE I .

SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR FILENAME .

  CALL FUNCTION 'F4_FILENAME'

   EXPORTING

     PROGRAM_NAME        = SYST-CPROG

     DYNPRO_NUMBER       = SYST-DYNNR

*     FIELD_NAME          = ' '

   IMPORTING

     FILE_NAME           = FILENAME.

START-OF-SELECTION .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

  EXPORTING

    FILENAME                      = FILENAME

    I_BEGIN_COL                   = 0001   "STARTING COLUMN OF EXCEL.

    I_BEGIN_ROW                   = P_BEG

    I_END_COL                     = 0004   "ENDING COLUMN OF EXCEL

    I_END_ROW                     = P_END

  TABLES

    INTERN                        = I_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.

  ENDIF .

LOOP AT I_EXCEL .

  CASE I_EXCEL-COL .

    WHEN '0001'.

      ITAB-kunnr = I_EXCEL-VALUE .

    WHEN '0002' .

      ITAB-anred = I_EXCEL-VALUE .

       WHEN '0003' .

      ITAB-name1 = I_EXCEL-VALUE .

      when '0004'.

      ITAB-ort01 = i_excel-value.

  ENDCASE .

       AT END OF ROW .

       APPEND ITAB . " internal table

       CLEAR ITAB .

       ENDAT .

  ENDLOOP .

[vivek_mishra0123]

Hi Ajinkya,

I think below piece of code will work for you.

*&- Types decleration

**same fields as that of the excel file

TYPES: BEGIN OF ty_data,

        id   TYPE char2,

        name TYPE char10,

        END OF ty_data.

DATA: it_raw  TYPE truxs_t_text_data,

       it_data TYPE TABLE OF ty_data.

PARAMETERS: s_file TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR s_file.

   CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

     CHANGING

       file_name     = s_file

     EXCEPTIONS

       mask_too_long = 1

       OTHERS        = 2.

   IF sy-subrc <> 0.

* Implement suitable error handling here

   ENDIF.

START-OF-SELECTION.

   CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

     EXPORTING

       i_tab_raw_data       = it_raw

       i_filename           = s_file

     TABLES

       i_tab_converted_data = it_data

     EXCEPTIONS

       conversion_failed    = 1

       OTHERS               = 2.

   IF sy-subrc <> 0.

* Implement suitable error handling here

   ENDIF.

Now internal table it_data will have the data from the excel file.

Regards,

Vivek Mishra

[vivek_mishra0123]

Hello Ajinkya,

Your code is correct should have value in it_tab, but have you checked that structure of  internal table it_tab is same as that of the input the Excel file or the file is not empty?

Regards,

Vivek MISHRA

[former_member196768]

Hello Ajinkya,

Create a internal table in ABAP report with same fields as in Excel file.

Below is the code worked for me.

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

type-pools: truxs, trwbo.

data: begin of gt_record occurs 0,

         reswk(004),     "Supplying plant

         werks(004),     "Plant

         bsart(004),      "Type   

       end of gt_record.

data: gt_raw     type truxs_t_text_data,

         gv_file1   type rlgrap-filename.

data: gv_file type string.

parameters: p_file(128) type c.

*----------------------------------------------------------------------*

*   at selection screen on value-request for                           *

*----------------------------------------------------------------------*

at selection-screen on value-request for p_file.

   call function 'F4_FILENAME'

     exporting

       program_name  = syst-cprog

       dynpro_number = syst-dynnr

     importing

       file_name     = p_file.

*-----------------------------------------------------------------------

* START OF SELECTION

*-----------------------------------------------------------------------

start-of-selection.

   gv_file = p_file.

   clear gt_record.

   gv_file1 = gv_file.

***FM to get data from Excel***

   call function 'TEXT_CONVERT_XLS_TO_SAP'

     exporting

       i_tab_raw_data       = gt_raw

       i_filename              = gv_file1

     tables

       i_tab_converted_data = gt_record[]

     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.

   else.

     delete gt_record index 1.

   endif.

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

Now gt_record[] will have data from excel.

And excel file should look like this--

reswk(004)	werks(004)	bsart(004)
N114	N202	N1UB
N114	N203	N1UB
N114	N204	N1UB
N114	N205	N1UB
N114	N206	N1UB
N114	N207	N1UB
N114	N208	N1UB
N114	N209	N1UB

[former_member201541]

Now I'm getting the data, but there is error in fieldcatalog now "NO FIELDCATALOG AVAILABLE" error. I have used the " REUSE_ALV_FIELDCATALOG_MERGE" FM. The structure of internal table and excel file is same. Please help.

[former_member196768]

Define your internal table with header line.

go through the following link.

http://scn.sap.com/thread/550111

Regards,

Siddhesh Satghare.

[philipdavy]

• Please do not recommend obsolete programming techniques like Internal tables with header lines.
• Internal table with header lines is bad programming practice since it is difficult to differentiate what is header and what is body.

Regards,

Philip