how to uploda excel file to internal table?

[Former Member]

Hi Experts,

My requirnment is so simple. I need to upload excel file into internal table.

sadly it's hard to me...

I have to create user role data from excel file.

this is an excel form.

userid	role1	rol2	...	role n
user 01	role1	role2	...	role n
user 02	role1	role2	...
user ...	role1	role2	...	role n
user n	role1	role2	...	role n

DATA :  BEGIN OF rolename,

                    rolename(50) TYPE c,

           END OF rolename.

DATA :  BEGIN OF is_data,

                     uname(30) TYPE c,

               role LIKE TABLE OF rolename,

            END OF is_data,

            it_data LIKE TABLE OF is_data.

I created this internal table. but i can't upload that excel file to internal table...

help me please.

[Former Member]

Convert the excel file into tab delimited(.txt) format and then use the Fm 'GUI_UPLOAD' ..

and can try with  Func Mod "ALSM_EXCEL_TO_INTERNAL_TABLE" also. If its available in your system.

[Former Member]

Hello,

You can also use this FM.

ALSM_EXCEL_TO_INTERNAL_TABLE

And your return table should be of type ALSMEX_TABLINE.

Thanks and Kind Regards,

Yovish.

[GirieshM]

Hi Jimmy,

You can find lot of FM to convert excel into internal table.

Please do a small search before posting. In addition to the above,TEXT_CONVERT_XLS_TO_SAP FM is also used for converting excel or tab delimited txt file into internal table.

Hope it helps.

With Regards,

Giriesh M

[former_member536879]

Hello Jimmy,

Kindly check the below mentioned code.

Report XXXXXX

TYPE-POOLS truxs.

TABLES : zscarr.

* Selection screen

PARAMETER p_file TYPE rlgrap-filename DEFAULT 'C:\TEST.xls'.

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.

* Uploading the data into the database table
  LOOP AT T_UPLOAD INTO WA_UPLOAD.
    ZSCARR-CARRID = WA_UPLOAD-CARRID.
    ZSCARR-TOTAL_SEATS = WA_UPLOAD-SEATS.
    MODIFY ZSCARR.
  ENDLOOP.

Thanks & Regards,

Sumodh.P

[former_member339717]

Jimmy use this code.

data : begin of tab occurs 0,

         col1(4),

         col2(4) ,

         col3(10)  ,

         col4(15),

       end of tab.

data: itab1 like alsmex_tabline occurs 0 with header

line.

data: gd_currentrow type i.

data : purchaseorder like ekko-ebeln.

data: tot_rec type i,     "Total Records

      gd_update type i,   "Main Table Increement Counter

      gd_lines type i,    "Success Table increement Counter

      w_textout like t100-text. "VARIABLE TO GET ERRORLOG

data : begin of it_success occurs 0,

         salesdocument like bapivbeln-vbeln,  "PROJECT

       end of it_success.

DATA: t_listobject TYPE abaplist OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF t_ascilist OCCURS 0,

         line(1000).

DATA: END OF t_ascilist.

selection-screen begin of block b1 with frame.

skip 3.

parameter:p_infile like rlgrap-filename obligatory.

skip 3.

selection-screen end  of block b1.

at selection-screen on value-request for p_infile.

   perform value_help.

start-of-selection.

   call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

     exporting

       filename                = p_infile

       i_begin_col             = '1'

       i_begin_row             = '2' "Do not requireheadings

       i_end_col               = '22'

       i_end_row               = '10000'

     tables

       intern                  = itab1

     exceptions

       inconsistent_parameters = 1

       upload_ole              = 2

       others                  = 3.

   if sy-subrc <> 0.

     message e010(zz) with text-001. "Problem uploadingExcel Spreadsheet

   endif.

*perform open_group.

   sort itab1 by row col.

* Get first row retrieved

   read table itab1 index 1.

* Set first row retrieved to current row

   gd_currentrow = itab1-row.

   loop at itab1.

* Reset values for next row

     if itab1-row ne gd_currentrow.

       append tab .

       clear tab.

       gd_currentrow = itab1-row.

     endif.

     shift itab1-value left deleting leading space.

     case itab1-col.

       when '0001'.

         tab-col1 = itab1-value.

       when '0002'.

         tab-col2 = itab1-value.

       when '0003'.

         tab-col3 = itab1-value.

       when '0004'.

         tab-col4 = itab1-value.

     endcase.

   endloop.

   append tab.

   clear tab.

   form value_help .

   call function 'DSVAS_DOC_WS_FILENAME_GET_50'

     exporting

       def_filename     = ' '

       def_path         = ' '

       mask             = ',*.*,*.*.'

       mode             = 'O'

       title            = ' '

     importing

       filename         = p_infile

     exceptions

       inv_winsys       = 1

       no_batch         = 2

       selection_cancel = 3

       selection_error  = 4

       others           = 5.

   if sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

   endif.

endform.