topic Re: Dynamic Excel Template Upload in Application Development and Automation Discussions

Dynamic Excel Template Upload

saravanan_subramaniyan — Sat, 31 Aug 2013 10:20:37 GMT

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 .

Re: Dynamic Excel Template Upload

Former Member — Sat, 31 Aug 2013 18:23:45 GMT

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

Re: Dynamic Excel Template Upload

saravanan_subramaniyan — Sat, 31 Aug 2013 20:28:41 GMT

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.

Re: Dynamic Excel Template Upload

Former Member — Mon, 02 Sep 2013 09:38:55 GMT

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

Re: Dynamic Excel Template Upload

Former Member — Mon, 02 Sep 2013 10:19:37 GMT

Or you can use the FM CONVERT_XLS_TO_SAP

Re: Dynamic Excel Template Upload

saravanan_subramaniyan — Mon, 02 Sep 2013 10:56:23 GMT

Hi Sumit,

Thanks a lot !

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