13 Replies Latest reply: Jul 29, 2011 12:24 AM by Clemens Li RSS

Application server Excel file to Internal table

anil kumar thunam
Currently Being Moderated

Hi,

 

I have excel file in Application Server.

And i want to upload that file into sap internal table.

 

Pleae give me response asap.....

 

Advance Thanks,

Kumar A

  • Re: Application server Excel file to Internal table
    reshma alumadathil
    Currently Being Moderated

    hi,

     

    chk this, put the data into an excel file.then download.

     

    suppose data base name is

    db_name_age

    fields inside it are name and age.

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

    sample excel sheet.

     

    coloumn 1 is name and column 2 is age

     

    name age

    A     8

    C     13

    D     55

     

    ************************************
    
    DATA : int_excel LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.
    
    data : record like db_name_age occurs 0 with header line.
    
    DATA : v_start_col TYPE i VALUE '1', "starting col
    
           v_start_row TYPE i VALUE '1', " starting row
    
           v_end_col   TYPE i VALUE '2', " total columns
    
           v_end_row   TYPE i VALUE '10'. "total no of record
    
    
    FORM f_upload .
    
      CLEAR : int_excel, int_excel[].
    
      CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
        EXPORTING
          filename                = wf_filename
          i_begin_col             = v_start_col
          i_begin_row             = v_start_row
          i_end_col               = v_end_col
          i_end_row               = v_end_row
        TABLES
          intern                  = int_excel
        EXCEPTIONS
          inconsistent_parameters = 1
          upload_ole              = 2
          OTHERS                  = 3.
    
      IF sy-subrc <> 0.
    *Message is 'Unable to upload data from  '  wf_filename.
        MESSAGE e169(zm050) WITH wf_filename.
      ELSE.
    
        SORT int_excel BY row col.
        REFRESH : record.
        CLEAR   : record.
    
    
        LOOP AT int_excel.
    
          CASE int_excel-col. "go thru each column.
    
            WHEN 1.
              record-name  = int_excel-value. 
            WHEN 2.
              record-age = int_excel-value.      
    
          ENDCASE.
    
          AT END OF row.
    
            APPEND record.
    
            CLEAR record.
    
          ENDAT.
    
        ENDLOOP.
    
      ENDIF.
    
    *reocrd has all the datas

    Rgds

    Reshma

  • Re: Application server Excel file to Internal table
    Felipe Gelme
    Currently Being Moderated

    Hi Anil.

     

    You can use the Function Module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

     

    Reward Points if it is helpful.

     

    Regards

     

    Felipe Gelme

     

     

    See the Example code -

     

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

    • Program : ZLWMI151_UPLOAD(Data load to ZBATCH_CROSS_REF Table)

    • Type : Upload program

    • Author : Seshu Maramreddy

    • Date : 05/16/2005

    • Transport : DV3K919574

    • Transaction: None

    • Description: This program will get the data from XLS File

    • and it upload to ZBATCH_CROSS_REF Table

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

     

    REPORT ZLWMI151_UPLOAD no standard page heading

    line-size 100 line-count 60.

     

    *tables : zbatch_cross_ref.

     

     

    data : begin of t_text occurs 0,

    werks(4) type c,

    cmatnr(15) type c,

    srlno(12) type n,

    matnr(7) type n,

    charg(10) type n,

    end of t_text.

     

    data: begin of t_zbatch occurs 0,

    werks like zbatch_cross_ref-werks,

    cmatnr like zbatch_cross_ref-cmatnr,

    srlno like zbatch_cross_ref-srlno,

    matnr like zbatch_cross_ref-matnr,

    charg like zbatch_cross_ref-charg,

    end of t_zbatch.

     

    data : g_repid like sy-repid,

    g_line like sy-index,

    g_line1 like sy-index,

    $v_start_col type i value '1',

    $v_start_row type i value '2',

    $v_end_col type i value '256',

    $v_end_row type i value '65536',

    gd_currentrow type i.

     

     

    data: itab like alsmex_tabline occurs 0 with header line.

     

    data : t_final like zbatch_cross_ref occurs 0 with header line.

     

     

    selection-screen : begin of block blk with frame title text.

    parameters : p_file like rlgrap-filename obligatory.

    selection-screen : end of block blk.

     

    initialization.

    g_repid = sy-repid.

     

    at selection-screen on value-request for p_file.

     

    CALL FUNCTION 'F4_FILENAME'

    EXPORTING

    PROGRAM_NAME = g_repid

    IMPORTING

    FILE_NAME = p_file.

     

     

    start-of-selection.

     

    • Uploading the data into Internal Table

    perform upload_data.

     

    perform modify_table.

     

    top-of-page.

    CALL FUNCTION 'Z_HEADER'

    • EXPORTING

    • FLEX_TEXT1 =

    • FLEX_TEXT2 =

    • FLEX_TEXT3 =

    .

     

     

     

     

    &----


    *& Form upload_data

    &----


    • text

    ----


    FORM upload_data.

     

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    EXPORTING

    FILENAME = p_file

    I_BEGIN_COL = $v_start_col

    I_BEGIN_ROW = $v_start_row

    I_END_COL = $v_end_col

    I_END_ROW = $v_end_row

    TABLES

    INTERN = itab

    EXCEPTIONS

    INCONSISTENT_PARAMETERS = 1

    UPLOAD_OLE = 2

    OTHERS = 3.

    IF SY-SUBRC <> 0.

    write:/10 'File '.

    ENDIF.

     

    if sy-subrc eq 0.

     

    read table itab index 1.

    gd_currentrow = itab-row.

     

     

    loop at itab.

     

    if itab-row ne gd_currentrow.

     

    append t_text.

    clear t_text.

    gd_currentrow = itab-row.

    endif.

     

    case itab-col.

    when '0001'.

    t_text-werks = itab-value.

    when '0002'.

    t_text-cmatnr = itab-value.

    when '0003'.

    t_text-srlno = itab-value.

    when '0004'.

    t_text-matnr = itab-value.

    when '0005'.

    t_text-charg = itab-value.

    endcase.

     

    endloop.

    endif.

     

    append t_text.

     

    ENDFORM. " upload_data

     

    &----


    *& Form modify_table

    &----


    • Modify the table ZBATCH_CROSS_REF

    ----


     

    FORM modify_table.

     

    loop at t_text.

     

    t_final-werks = t_text-werks.

    t_final-cmatnr = t_text-cmatnr.

    t_final-srlno = t_text-srlno.

    t_final-matnr = t_text-matnr.

    t_final-charg = t_text-charg.

    t_final-erdat = sy-datum.

    t_final-erzet = sy-uzeit.

    t_final-ernam = sy-uname.

    t_final-rstat = 'U'.

    append t_final.

    clear t_final.

     

    endloop.

     

    delete t_final where werks = ''.

    describe table t_final lines g_line.

    sort t_final by werks cmatnr srlno.

     

    • Deleting the Duplicate Records

    perform select_data.

     

    describe table t_final lines g_line1.

     

    modify zbatch_cross_ref from table t_final.

     

     

    if sy-subrc ne 0.

    write:/ 'Updation failed'.

    else.

    Skip 1.

    Write:/12 'Updation has been Completed Sucessfully'.

    skip 1.

    Write:/12 'Records in file ',42 g_line .

    write:/12 'Updated records in Table',42 g_line1.

    endif.

     

    delete from zbatch_cross_ref where werks = ''.

     

     

    ENDFORM. " modify_table

    &----


    *& Form select_data

    &----


    • Deleting the duplicate records

    ----


    FORM select_data.

     

    select werks

    cmatnr

    srlno from zbatch_cross_ref

    into table t_zbatch for all entries in t_final

    where werks = t_final-werks

    and cmatnr = t_final-cmatnr

    and srlno = t_final-srlno.

     

     

    sort t_zbatch by werks cmatnr srlno.

     

     

    loop at t_zbatch.

     

    read table t_final with key werks = t_zbatch-werks

    cmatnr = t_zbatch-cmatnr

    srlno = t_zbatch-srlno.

    if sy-subrc eq 0.

    delete table t_final .

    endif.

     

    clear: t_zbatch,

    t_final.

    endloop.

     

    ENDFORM. " select_data

  • Re: Application server Excel file to Internal table
    Naveen Babu Devabathini
    Currently Being Moderated

    Hi,

     

    Use the Fm  "ALSM_EXCEL_TO_INTERNAL_TABL" . this  will upload the data from Excel file to Inernal table.

    Take little bit care , as this will upload the data to inernal table in different structure..

    Revert back if any issues.

     

    Reward with points if helpful.

     

    Regards,

    Naveen.

  • Re: Application server Excel file to Internal table
    VIJAY CHATURVEDI
    Currently Being Moderated

    Hi,

     

     

    ex-

     

     

    DATA: BEGIN OF ITAB2 OCCURS 0,

     

             VALUE(250) TYPE C,

     

             END   OF ITAB2.

     

    **************************************DATA DECLERATION************************************************

     

    DATA: PATH LIKE DYNPREAD-FIELDNAME.

     

    DATA : P TYPE STRING,

     

           D TYPE STRING.

     

    DATA : V_DATASET    LIKE FILENAMECI-FILEEXTERN.

     

    **************************************SELECTION SCREEN***********************************************

     

    PARAMETERS: V_PATH TYPE RLGRAP-FILENAME DEFAULT 'C:test.txt'.

     

    PARAMETERS: D_PATH TYPE RLGRAP-FILENAME.

     

    SKIP 5.

     

    SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME.

     

    PARAMETERS: PC_ONE    RADIOBUTTON GROUP RAD1 DEFAULT 'X',

     

                PC_OTHER    RADIOBUTTON GROUP RAD1.

     

     

    SELECTION-SCREEN END OF BLOCK B1.

     

     

    AT SELECTION-SCREEN ON VALUE-REQUEST FOR V_PATH.

     

                                                      • HELP FOR SEARCHING FILES PATH************************************************

     

      IF PC_ONE = 'X'.

     

        CALL FUNCTION 'F4_FILENAME'

         EXPORTING

    •   PROGRAM_NAME        =  'ZTRIAL'

    •   DYNPRO_NUMBER       = SYST-DYNNR

           FIELD_NAME          = PATH

         IMPORTING

           FILE_NAME           = V_PATH.

     

    *******CONVERT IN TO STRING************

     

        P = V_PATH.

     

      ENDIF.

     

    **************************************SELECTION SCREEN VALIDATION***********************************

     

    AT SELECTION-SCREEN.

     

      IF V_PATH = ''.

     

        MESSAGE E014.                    "FILE PATH NOT FOUND!!!!!!!

     

      ENDIF.

     

      IF D_PATH = ''.

     

        MESSAGE E016.                "TARGET FILE NOT FOUND!!!!!!!

     

      ENDIF.

     

    *******************FOR DOWNLOAD**********

     

     

     

    AT SELECTION-SCREEN ON VALUE-REQUEST FOR D_PATH.

     

      CALL FUNCTION 'F4_FILENAME'

       EXPORTING

    •   PROGRAM_NAME        = 'ZTRIAL'

    •   DYNPRO_NUMBER       = SYST-DYNNR

         FIELD_NAME           = PATH

       IMPORTING

         FILE_NAME           = D_PATH.

     

     

     

    ***************CONVERT INTO STRING***********

     

      D = D_PATH.

     

     

    *******************************************MAIN LOGIC************************************************

     

    START-OF-SELECTION.

     

      IF P = ''.

     

        P = 'test.txt'.

     

      ENDIF.

     

    ******************UPLOAD DATA FROM OTHERS TO INTERNAL TABLE****************

     

      CALL FUNCTION 'GUI_UPLOAD'

              EXPORTING

                FILENAME                      = P

    •          FILETYPE                      = 'ASC'

    •          HAS_FIELD_SEPARATOR           = ' '

    •          HEADER_LENGTH                 = 0

    •          READ_BY_LINE                  = 'X'

    •          DAT_MODE                      = ' '

    •          CODEPAGE                      = ' '

    •          IGNORE_CERR                   = ABAP_TRUE

    •          REPLACEMENT                   = '#'

    •          CHECK_BOM                     = ' '

    •        IMPORTING

    •          FILELENGTH                    =

    •          HEADER                        =

              TABLES

                DATA_TAB                      = ITAB2

             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 = 1.

    *

    •        MESSAGE E014.                    "FILE PATH NOT FOUND!!!!!!!

    *

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

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

    •       ENDIF.

     

    loop at itab2.

    write:/

    endloop.

     

     

    ****do rewards if usefull

    regards,

    vijay

  • Re: Application server Excel file to Internal table
    Li yan
    Currently Being Moderated

    hi anil 

     

    Did you resolve your quesion for this issue? I met the same issue now. if you get the solution, can you please share it?

  • Re: Application server Excel file to Internal table
    Gowri Sankar Dinakaran
    Currently Being Moderated

    Hi,

     

    Please try like this.

     

    Data: p_file type rlgrap-filename

     

    • Getting the flat file from application server

        open dataset p_file for input in text mode encoding default.

    *---Data is downloaded to the application server file path

        if sy-subrc = 0.

          do.

            read dataset p_file into ls_input-wa_string.

            if sy-subrc eq 0.

              append ls_input to lt_input.

            else.

              exit.

            endif.

          enddo.

    *--Close the Application server file (Mandatory).

          close dataset p_file.

     

    after getting the value from application server

     

    loop at lt_input into ls_input.

     

    split ls_input-wa-string at ',' into table lt_commasepfile.

     

    endloop.

  • Re: Application server Excel file to Internal table
    Abhijeet Kulshreshtha
    Currently Being Moderated

    Hii!

      I have a excel file named ztest on Application server.

    I wrote the following code to upload that file in my internal table

    Check this sample code.

     

    REPORT  Z_file5.
    
    DATA:
      fname(40).
    
    
    DATA:
      BEGIN OF fs_flight,
        carrid   LIKE sflight-carrid,
        connid   LIKE sflight-connid,
        fldate   LIKE sflight-fldate,
        price    LIKE sflight-price,
        currency LIKE sflight-currency,
      END OF fs_flight.
    DATA:
      t_flight LIKE
         TABLE OF
               fs_flight.
    fname = '.\ztest.xls'.
    OPEN DATASET fname FOR INPUT IN BINARY MODE.
    
    DO 100 TIMES.
      READ DATASET fname INTO fs_flight.
      APPEND fs_flight TO t_flight.
      IF fs_flight IS INITIAL.
        EXIT.
      ENDIF.
      CLEAR fs_flight.
    ENDDO.
    
    
    LOOP AT t_flight INTO fs_flight.
      WRITE: / fs_flight-carrid,
               fs_flight-connid,
               fs_flight-fldate,
               fs_flight-price,
               fs_flight-currency.
    ENDLOOP.

     

    Regards

    Abhijeet

  • Re: Application server Excel file to Internal table
    Laveen Pawar
    Currently Being Moderated

    hey there,

     

    you just have to deploy the 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    fm...and fill teh parameters as required,

     

    this shall do the needful for you.

     

    take care

  • Re: Application server Excel file to Internal table
    Laveen Pawar
    Currently Being Moderated

    hey there,

     

    you just have to deploy the 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    fm...and fill teh parameters as required,

     

    this shall do the needful for you.

     

    take care

  • Re: Application server Excel file to Internal table
    Kiran Kumar
    Currently Being Moderated

    please let me know the solution

Actions