Converting the alsmex_tabline format to excel sheet format

[Former Member]

Hi All

I am using ALSM_EXCEL_TO_INTERNAL_TABLE to get data from an excel sheet to an internal table.

I am able to get values into the internal table.

please help me with converting the internal table in the format alsmex_tabline to the required format

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_sfile
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 100
      i_end_row               = 100
    TABLES
      intern                  = i_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

data in excel sheet is in the format

DATA: BEGIN OF t_gb,
        rebuscode(15),
        pt(1),
        longtxt(30),
        shrttxt(20),
        code(4),
        infotype(4),
        templete(4),
        sapcode(4),
        paydeduct(4),
        amtunit(20),
        pensionable(1),
        rmcpenfund4(1),
        rmcexecfund5(1),
        rugbynoncon1(1),
        rugbycon2(1),
        rmcover4024(1),
        rmcmoneypurch12(1),
        rmcdc30to36(1),
        ni(1),
        tax(1),
        arrestablepayments(1),
        factoredprorata(1),
        pretaxdeductions(1),
        netdedpay(1),
        totalgrosstaxablepay(1),
        truegrossallgrosspay(1),
        annualperiodic(1),
        cashrateformula(1),
        validation(1),
        locationcontractvalidation(1),
        aweavweeklyearnings(1),
        payslipnra(1),
        ytdnra(1),
        ficoignore(1),
END OF t_gb.

[Former Member]

Hi Sriram,

This is one example to convert the data to internal table from excel sheet.Using 'ALSM_EXCEL_TO_INTERNAL_TABLE' function module internal table contains the data like ROW, COL,VALUE. So now we have to convert it to the actual internal table structure.

Below is the internal table structure.

BEGIN OF WA_BKPF,
    BUKRS LIKE BKPF-BUKRS,             " company Code
    BELNR LIKE BKPF-BELNR,             " Accounting Document Number
    GJAHR LIKE BKPF-GJAHR,             " Fiscal Year
    BLART LIKE BKPF-BLART,             " Document type
    BLDAT LIKE BKPF-BLDAT,             " Document Date in Document
    BUDAT LIKE BKPF-BUDAT,             " Posting Date in the Document
    MONAT LIKE BKPF-MONAT,             " Fiscal Period
    CPUDT LIKE BKPF-CPUDT,             " Accounting Document Entry Date
END OF WA_BKPF.

Below is how to convert the excel sheet data to internal table. You need to take the entire ROW with all the columns one by one and move it to one variable . Here I took LW_LINE . LW_LINE contains the values of the one ROW of actual internal table structure .After getting all the ROW values append it to the actual internal table.

FORM UPLOAD .

  DATA:
    LW_ROW        TYPE I,              " Index to read t_value table
    LW_LINE(100)  TYPE C,              " Holds VALUE from t_rowcol
    LW_COMPONENTS TYPE I,              " Nimber of fields in t_bkpf
    LW_TYPE       TYPE C,              " Holds Field type of fs_bkpf
    LW_TIMES      TYPE I.              " No of rows in internal table

*  finding no.of fields in fs_bkpf structure
  DESCRIBE FIELD FS_BKPF TYPE LW_TYPE COMPONENTS LW_COMPONENTS.

*describe table t_bkpf.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                = W_FNAME1
      I_BEGIN_COL             = 1
      I_BEGIN_ROW             = 1
      I_END_COL               = LW_COMPONENTS
      I_END_ROW               = 50
    TABLES
      INTERN                  = T_ROWCOL
    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.                               " IF SY-SUBRC <> 0.

* Finding no.of rows in T_ROWCOL internal table
  DESCRIBE TABLE T_ROWCOL.

  LW_TIMES = SY-TFILL DIV LW_COMPONENTS.

  LW_ROW = 0.


  DO LW_TIMES TIMES.
    LW_ROW = LW_ROW + 1.
    LOOP AT T_ROWCOL WHERE ROW = LW_ROW  .

      CONCATENATE LW_LINE
                  T_ROWCOL-VALUE
             INTO LW_LINE
        SEPARATED BY SPACE.
    ENDLOOP.                           " LOOP AT T_ROWCOL...

    SHIFT LW_LINE LEFT.

* Separating w_line into fields of T_BKPF
    SPLIT LW_LINE AT SPACE INTO  FS_BKPF-BUKRS
                                 FS_BKPF-BELNR
                                 FS_BKPF-GJAHR
                                 FS_BKPF-BLART
                                 FS_BKPF-BLDAT
                                 FS_BKPF-BUDAT
                                 FS_BKPF-MONAT
                                 FS_BKPF-CPUDT.
* Appending each row value from excel file to t_bkpf

    APPEND FS_BKPF TO T_BKPF.

    CLEAR : LW_LINE,
            FS_BKPF.
  ENDDO.                               " DO LW_TIMES TIMES.

* Displaying data from internal table
  LOOP AT T_BKPF INTO FS_BKPF.
    WRITE :/
      FS_BKPF-BUKRS UNDER TEXT-001,
      FS_BKPF-BELNR UNDER TEXT-002,
      FS_BKPF-GJAHR UNDER TEXT-003,
      FS_BKPF-BLART UNDER TEXT-004,
      FS_BKPF-BLDAT UNDER TEXT-005,
      FS_BKPF-BUDAT UNDER TEXT-006,
      FS_BKPF-MONAT UNDER TEXT-007,
      FS_BKPF-CPUDT UNDER TEXT-008.
  ENDLOOP.                             " LOOP AT T_BKPF INTO FS_BKPF.

ENDFORM.                               " UPLOAD

Regards,

Rajitha.

[Former Member]

There should be an option through field symbols too.

Can you please let me know that?

[Former Member]

Hi Sriram,

I think there is no option using field symbols in this case.

But using field symbols , If field symbol contailns internal table then to get internal table data we can use

ASSIGN COMPONENT sy-index...

Components is the fields of the internal table.Here also field values are retrieved one by one.

Regards,

Rajitha.