Uploading multiple excel sheets

[Former Member]

Dear All,

We are having 4 different excel sheets where data is stored regarding the quantity of laptops, desktops, printers & xerox machines. This data is to be loaded into the SAP tables with Co Code & BA Code as primary key which is there available in excel sheets.

Plz help me in code to upload all the 4 files in SAP tables based on a combination of CO Code & BA Code.

The selection screen will have 4 fields to specify the path of all the 4 excel sheets & all the fields are obligatory.

Thanks & Reg,

Nishant

[Former Member · Accepted solution]

Hai Nishu,

The Help documentation (Desktop Office Integration (BC-CI)--> Spreadsheet interface) is quite helpfull.

Program <b>SAPRDEMO_TABLES_IN_EXCEL</b> uses some of the methods as described in the SAPHelp. You can get a pretty good idea of how to work with it.

Regards,

Srikanth.

[Former Member]

Nishu,

Use the cl_gui_frontend_services=>file_open_dialog to get the file names and then GUI_UPLOAD to upload all the files into 4 internal tables.

If all 4 internal tables have the same structure you can simply use APPEND LINE OF ITAB1 TO MAIN_TAB to get data into one table.

Then you can SORT it by Co Code and BA Code and process the data.

Regards,

Ravi

note : Please mark the helpful answers

[Former Member]

Hi Ravi,

Can u provide me with the code for this.

Thanks,

nishu

[Former Member]

hi

good

if you want to upload the data into the SAP tables than you can use the LSMW to do the same.

thanks

mrutyunj

[Former Member]

Hi Nishu,

As suggested yesterday..

you can do like this..

data itab1 like table of <tablename> with header line.

loop at itab.
 move-corresponding itab to itab1.
 append itab1.
Endloop.
INSERT <tablename> from TABLE itab1.

here itab is the table built from the excel..

if you stil have problem in getting the soln..reply

regards

satesh

[Former Member]

Hi Satesh,

Thanks very much for your prompt reply.

I am attaching the code herewith, please guide me where to use FORM & PERFORM to upload the other 3 excel sheets of the same format.

REport nis1234.

TABLES: bseg,

zfi_is_qty.

DATA:

t_excel LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE,

l_nodata TYPE c,

l_excelfile(128) TYPE c. " filename to be passed to excel file

DATA: BEGIN OF t_type,

bukrs LIKE bseg-bukrs,

gsber LIKE bseg-gsber,

COUNT TYPE I,

END OF t_type.

data: l_charlen1 type i,

l_charlen2 type i.

DATA: t_type2 LIKE t_type OCCURS 0 WITH HEADER LINE.

DATA: l_end_row TYPE i,

l_endrow1 TYPE i,

charlen type c.

DATA l_count TYPE I.

DATA : q_count TYPE i value 1.

PARAMETER :

• p_endrow TYPE i OBLIGATORY,

p_file TYPE rlgrap-filename OBLIGATORY.

****clearing the contents of database table before uploading a new file everytime

DELETE FROM ZFI_IS_QTY.

*****selection-screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

field_name = p_file

CHANGING

file_name = p_file.

START-OF-SELECTION.

l_excelfile = p_file.

• l_endrow1 = p_endrow.

• p_endrow = p_endrow + 1.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = l_excelfile

i_begin_col = 5

i_begin_row = 2

i_end_col = 6

• i_end_row = p_endrow

i_end_row = 4000

TABLES

intern = t_excel

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.

MESSAGE i010(zn) WITH 'Error in upload Please check the upload file'.

ENDIF.

DESCRIBE TABLE T_EXCEL LINES L_ENDROW1.

L_ENDROW1 = L_ENDROW1 / 2.

DO.

LOOP AT t_excel WHERE row = q_count.

IF t_excel-col = 1.

t_type2-gsber = t_excel-value.

ELSEIF t_excel-col = 2.

t_type2-bukrs = t_excel-value.

ENDIF.

ENDLOOP.

l_charlen1 = strlen( t_type2-bukrs ).

l_charlen2 = strlen( t_type2-gsber ).

if ( t_type2-bukrs <> ' ' and t_type2-gsber <> ' ' ).

if ( l_charlen1 = 4 and l_charlen2 = 4 ).

APPEND t_type2. CLEAR t_type2.

endif.

endif.

q_count = q_count + 1.

IF q_count > l_endrow1.

EXIT.

ENDIF.

ENDDO.

SORT T_TYPE2 BY BUKRS GSBER.

*****uploading data from internal table into database table

LOOP AT T_TYPE2.

MOVE-CORRESPONDING T_TYPE2 TO T_TYPE.

L_COUNT = L_COUNT + 1.

AT END OF GSBER.

AT END OF BUKRS.

• write:/ t_type-BUKRS,

• t_type-GSBER,

• L_COUNT.

zfi_is_qty-COMPANY_CODE = t_type-bukrs.

zfi_is_qty-BAREA = t_type-gsber.

zfi_is_qty-DT = l_count.

insert zfi_is_qty.

CLEAR L_COUNT.

ENDAT.

ENDAT.

ENDLOOP.

************to read the data from the database table.

data: itab like zfi_is_qty occurs 0 with header line.

select * from zfi_is_qty into corresponding fields of table itab.

ULINE.

loop at itab.

WRITE:/ ITAB-COMPANY_CODE,

ITAB-BAREA,

ITAB-DT.

Endloop.

Thanks in advance,

nishu

[Former Member]

Hi Nishu,

Call this FORM as many times with the filenames and the start and end columns and rows..

form table_excel using filename(30)
                       startcol type i
                       endcol type i
                       startrow type i
                       endrow type i
                                              
           
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = filename
i_begin_col = startcol
i_begin_row = startrow
i_end_col = endcol
i_end_row = endrow
i_end_row = 4000
TABLES
intern = t_excel
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.
MESSAGE i010(zn) WITH 'Error in upload Please check the upload file'.
ENDIF.

DESCRIBE TABLE T_EXCEL LINES L_ENDROW1.

L_ENDROW1 = L_ENDROW1 / 2.


LOOP AT t_excel WHERE row = q_count.
IF t_excel-col = 1.
t_type2-gsber = t_excel-value.
ELSEIF t_excel-col = 2.
t_type2-bukrs = t_excel-value.
ENDIF.
ENDLOOP.
l_charlen1 = strlen( t_type2-bukrs ).
l_charlen2 = strlen( t_type2-gsber ).
if ( t_type2-bukrs <> ' ' and t_type2-gsber <> ' ' ).

if ( l_charlen1 = 4 and l_charlen2 = 4 ).

APPEND t_type2. 
CLEAR t_type2.

endif.

endif.
q_count = q_count + 1.
IF q_count > l_endrow1.
EXIT.
ENDIF.
ENDDO.


data : it_zfi_is_qty like table of zfi_is_qty with header line.

loop at t_type2.
 move-corresponding t_type2 to it_zfi_is_qty.
 append it_zfi_is_qty.
endloop.


insert zfi_is_qty from TABLE it_zfi_is_qty .
endform.

hope it works..

regards

satesh

[Former Member]

Hi Satesh,

Sorry to disturb u again.

My z table is having fields like Co Code, BA Code, Qty1, qty2, qty3, qty4.

When we are uploading the data into database table the first sheet will upload the co code, BA code & quantity for first sheet. But when we are using FORM statement then how will it upload the qty2 , qty3 & qty4 for the other sheets keeping the same combination of the Co Code & BA Code.

Plz help in this requirement, as I am very confused.

Thanks,

nishu

[Former Member]

Hi Sateeh,

I got your point. I need to call

form table_excel (As many times as I per sheets... Suppose I have 4 excel sheets in excel file I need to call it 4 times...)

But here my query is, Before calling

form table_excel

We need to set active sheet in excel right....Other wise It will pick data from the same sheet.... How can we active sheet...

sheet 1. Active form table_excel

sheet 2. Active form table_excel

sheet 3. Active form table_excel

sheet 4. Active form table_excel

How can we do this???? Please help me

Rayudu