Dynamic Excel Template Upload

[saravanan_subramaniyan]

Hi Gurus,

I have an requirement to upload excel with dynamic template from abap program i.e..the excel template won't be a constant, it might change.

For example,

the field positions may change (some customer name in 1st column & sometimes in 5th column),

the number of columns of the excel may change ( some times fewer & sometimes same as the sap table to be uploaded ),

here my difficulty is, how can i map the fields and their respective values from excel to internal table, from there to database table.

Thanks in advance .

[Former Member]

Hi Saravanan,

This is the common requirement. you can use the Function Module,

ALSM_EXCEL_TO_INTERNAL_TABLE.

This Function Module takes the data from excel file ( Whatever the column positions be) in to internal table. this internal table will have all the data along with headings of the columns, so you dont have to worry about the order of columns.

Regards,

Sumit

[saravanan_subramaniyan]

Hi Sumit,

I am using the same FM only, but not as you said its not taking columns by Field Label, but its taking by positions which is to fixed.

if we change the positions , definitely it takes the what ever data in that fixed position, not the right dis data which is some other position.

Moreover, hope u know, in ALSM_EXCEL_INTERNAL_TABLE, We have move the data from excel to internal table by using branch statements ie.Case, since it reads records by row & columns not as an entire work area.

so definitely we have take care of the internal table.

Kindly provide some sample code snippets by changing column positions using the same FM, which will be helpful, if i am wrong.

Your timely help ( points) will be rewarded.

[Former Member]

I have used this Function Module and also able to successfully upload the headings of the excel table also.

With this I have populated the fieldcatalog also, which is dependent on the positions of columns at the time of file upload.

"Data Declaration..................

data: it_excel_tab type standard table of alsmex_tabline,

       it_fcat type  lvc_t_fcat.

   call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

     exporting

       filename                = p_file

       i_begin_col             = 1

       i_begin_row             = 1

       i_end_col               = p_column

       i_end_row               = p_row

     tables

       intern                  = it_excel_tab

     exceptions

       inconsistent_parameters = 1

       upload_ole              = 2.

You can enter p_column and p_row as per your requirement or give it more in number to be on safe side.

Now create a field catalog as under :

loop at it_excel_tab into wa_excel_tab where row = 1.

     wa_fcat-fieldname = wa_excel_tab-value.

     wa_fcat-coltext = wa_excel_tab-value.

     append wa_fcat to it_fcat.

     clear wa_fcat.

   endloop.

Now create Dynamic Internal table, which would help you in dynamic column position conditions :

data: it_dyn_table type ref to data,

        wa_dyn_table type ref to data.

data: value(10) type c.

field-symbols: <fs_tab> type standard table,

                <fs_line> type any,

                <fs_now> type any.

*&-- Creating dynamic internal table

  cl_alv_table_create=>create_dynamic_table(

     exporting

       it_fieldcatalog           = it_fcat

    importing

       ep_table                  = it_dyn_table

     exceptions

       generate_subpool_dir_full = 1

          ).

   if sy-subrc <> 0.

     message id sy-msgid type sy-msgty number sy-msgno

                with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

   endif.

   "Create a workarea for dynamic internal table.............

   assign it_dyn_table->* to <fs_tab>.

   create data wa_dyn_table like line of <fs_tab>.

   assign wa_dyn_table->* to <fs_line>.

Now fill your internal table as follows :

loop at it_excel_tab into wa_excel_tab where wa_excel_tab-row NE 1.

       read table it_fcat into wa_fcat index wa_excel_tab1-col.

       if sy-subrc = 0.

         value = wa_fcat-fieldname.

         assign  component value of structure <fs_line> to <fs_now>.

         move wa_excel_tab1-value to <fs_now>.

       endif.

     at end of row.

       append <fs_line> to <fs_tab>.

       clear <fs_line>.

     endat.

   endloop.

Hope this will help you to handle dynamic column positions in excel file.

Regards,

Sumit

[Former Member]

Or you can use the FM CONVERT_XLS_TO_SAP

[saravanan_subramaniyan]

Hi Sumit,

Thanks a lot !

Will try it and definitely reward for your hard work, if its worked out.