topic Re: Dynamic ITAB from Excel in Application Development and Automation Discussions

Dynamic ITAB from Excel

Former Member — Wed, 03 Mar 2010 05:14:14 GMT

Dear Experts,

1) While uploading the Characteristics in the result recording the user provides the data in the Excel sheet.

2) While Uploading the Sales Orders from 4.0 to ECC6.0 the data from excel is being is used.

For fixed format this is working fine but when they change the order of the columns or number of columns this approch fails.

I have searched the WIKI, SDN and others, i found creation of dynamic internal table but could not handle when the number of columns change or their order changes.

1) How to capture the Columns Heading names

2) How to handle the Number of columns and when the order of columns change

Please guide me

Warm Regards

Ram

Re: Dynamic ITAB from Excel

rainer_hbenthal — Wed, 03 Mar 2010 06:18:44 GMT

Are you really uploading an excel sheet? In most cases excel sheets are nothing else but csv files.

Re: Dynamic ITAB from Excel

Former Member — Wed, 03 Mar 2010 06:29:27 GMT

Hi Rainer,

The relavant data is provided in the excel format.

Now i am able to collect this into an Internal table using FM ALSM_EXCEL_TO_INTERNAL_TABLE

here i am using CASE key word and capturing the individual columns.

now

1) i want to know number of columns. " I got this.

2) i want to build a dynamic itab based on the columns provided

3) they can change the order of the columns and no of columns aswell.

Please suggest further.

Warm Regards

Ram

Re: Dynamic ITAB from Excel

anup_deshmukh4 — Wed, 03 Mar 2010 06:35:02 GMT

You can opt for OLE automation for excel and check the number of columns and then do it....!

hope it helps..!

Re: Dynamic ITAB from Excel

jrg_wulf — Wed, 03 Mar 2010 19:30:12 GMT

Hi Ram,

you've got your informations on number of columns and order (identified by col-header) in the first rows of your internal table with row = '001'

Easiest approach to your request, would be a nested structure like:


TYPES: lty_tabline type table of CHAR50, 
       lty_t_tab type table of lty_tabline.
DATA: a_cell type char50.
DATA: gt_thetable type lty_t_tab, wa_int type lty_tabline.
DATA: xltab type table of ALSMEX_TABLINE,
      wa_xl type ALSMEX_TABLINE.
...
loop at xltab into wa_xl.
  at new row.
    check wa_int is not initial.
    append wa_int to gt_thetable.
    clear wa_int.
  endat.
  append wa_xl-value to wa_int.
endloop.
...

regards

Jörg

Re: Dynamic ITAB from Excel

kesavadas_thekkillath — Wed, 03 Mar 2010 19:43:58 GMT

Hi,

I hope you can do a work around with this fm TEXT_CONVERT_XLS_TO_SAP

Re: Dynamic ITAB from Excel

Former Member — Thu, 04 Mar 2010 06:53:14 GMT

Check this


REFRESH IT_TAB.
  CLEAR IT_TAB.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                      = FILEPATH
      i_begin_col                   = 1
      i_begin_row                   = 3
      i_end_col                     = 5
      i_end_row                     = 9
    tables
      intern                        = IT_TAB
*   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.
  SORT IT_TAB BY ROW COL.
  CLEAR wa_GLMAP.
  CLEAR wa_tab.
  IF IT_TAB[] IS NOT INITIAL.
    LOOP AT IT_TAB into wa_tab .
      CASE wa_tab-COL.
        WHEN '0001'.
          wa_GLMAP-L_GL_ACC = wa_tab-VALUE.
        WHEN '0002'.
          wa_GLMAP-DESC_LGL = wa_tab-VALUE.
        WHEN '0003'.
          wa_GLMAP-B_UNIT = wa_tab-VALUE.
        WHEN '0004'.
          wa_GLMAP-SAKNR = wa_tab-VALUE.
        WHEN '0005'.
          wa_GLMAP-DEL_IND = wa_tab-VALUE.
      ENDCASE.
      AT END OF ROW.
        APPEND wa_GLMAP TO t_GLMAP_DATA.
        CLEAR wa_GLMAP.
      ENDAT.
    ENDLOOP.
  ELSE.
    LEAVE LIST-PROCESSING.
  ENDIF.
.

also check the following link

http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=60655105

Edited by: kk.adhvaryu on Mar 4, 2010 7:53 AM

Re: Dynamic ITAB from Excel

Former Member — Thu, 04 Mar 2010 07:59:44 GMT

If you can fix that first row of excel is header that tells about order of columns and columns,

you can declare an itab with all the fields needed in any case/scenario.

Then use field symbols to move values corrospondingly to correctfields of itab.

So, in few cases, few of the columns will be blank, it will not affect anything and you can continue processing.

In this case no need to create dynamic itab.