data upload from xls to internal table

[Former Member]

Suppose I have data in the form of a matrix and I need to upload the data into the database after validation. The excel sheet is of the format:

Emp Empdept E1 E2 E3 E4 E5 E6 E7 E8 E9

E1 MEC 0 2 3 4 5 6 6 5 7

E2 ELE 4 0 5 5 6 2 2 1 3

E3 ELE 4 4 0 6 1 4 7 8 9

E4 ECE 2 2 4 0 6 7 5 9 4

E5 CHE 7 8 9 5 0 5 6 1 3

E6 ELE 8 9 10 12 11 0 5 6 4

E7 MEC 4 5 4 6 7 6 0 5 7

E8 MEC 9 4 7 5 2 2 8 0 1

E9 ELE 1 5 2 4 2 6 4 6 0

The first column is the employee number

The second column is the employee department and

The matrix indicates the time taken by emplyee1 to be trained to handle position of employee E2 is 2 months (map using row of E1 and column of E2).

Similarly E9 to E8 is 6 months.

I have to get the above data into an internal table of the format

Emp_from Emp_to Duration

E1 E1 0

E1 E2 2

E1 E3 3

E1 E4 4

E1 E5 5

E1 E6 6

E1 E7 6

E1 E8 5

E1 E9 7

E2 E1 4

E2 E2 0

E2 E3 5

can someone help me in building the logic for this

regards,

seenu

[Former Member]

frnds,

any solution????

regards,

seenu

[Former Member]

Hi Seenu,

you cannot do this directly, first you will

need to read the Excel sheet into the internal table

using Function Module 'TEXT_CONVERT_XLS_TO_SAP'

then loop over the internal table with new logic

for getting the reqd output.

Regards,

Samson Rodrigues.

[Former Member]

Hi

First move the data to one internal table.So that you will get all the details for each employee seperately.From that internal table you can easily map the data according to your requirement and move it to you final internal table.

Regards

Shibin

[Former Member]

hi,

first u declere the internal table structure as ur input in excel sheet.

then use function module 'ALSM_EXCEL_TO_INTERNAL_TABLE' to upload excel sheet to internal table.

check the sample code below

TABLES:zmatnr.

TYPE-POOLS truxs.

DATA : itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

DATA row LIKE alsmex_tabline-row.

data : gi_final like zmatnr occurs 0 with header line.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETER : pfname LIKE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pfname.

PERFORM search.

START-OF-SELECTION.

perform process.

form process.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = pfname

i_begin_col = 1

i_begin_row = 2

i_end_col = 12

i_end_row = 65000

TABLES

intern = itab

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.

describe table itab lines itab_count.

row = 1.

loop at itab.

if itab-row <> row.

append gi_final.

clear gi_final.

endif.

case itab-col.

when '1'.

gi_final-MATNR = itab-value.

when '2'.

gi_final-Maktx = itab-value.

endcase.

row = itab-row.

endloop.

append gi_final.

clear gi_final.

endform.

FORM search .

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = pfname.

ENDFORM.

regards

siva

[Former Member]

Hello,

This is a very small sample code...hope it helps u

DATA: v_file LIKE rlgrap-filename,

v_filename TYPE string.

REFRESH i_matcontrol.

CLEAR i_matcontrol.

TRANSLATE v_exten TO UPPER CASE.

*

IF v_exten = 'XLS'.

v_file = p_p_file.

*

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_field_seperator = 'X'

i_tab_raw_data = ws_rawdata

i_filename = v_file

TABLES

i_tab_converted_data = i_data.

ELSEIF v_exten = 'TXT'.

v_filename = p_p_file.

• File upload

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = v_filename

filetype = 'ASC'

has_field_separator = 'T'

TABLES

data_tab = i_data

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

OTHERS = 17.

CASE sy-subrc.

WHEN 1.

MESSAGE e001(00) WITH text-001.

STOP.

WHEN 2.

MESSAGE e001(00) WITH text-002.

STOP.

WHEN 0.

IF i_data IS INITIAL.

MESSAGE s001(00) WITH text-008.

ENDIF.

ENDCASE.

ENDIF.

*

DELETE i_data WHERE matnr IS INITIAL AND

werks IS INITIAL.

try this

[Former Member]

Hi Seenu,

After Getting the data into the internal table,

use below logic to get ur reqd output.

data: begin of ntab occurs 0,
        emp(4),
        dept(5),
        E1 type i,
        E2 type i,
        E3 type i,
        E4 type i,
        E5 type i,
        E6 type i,
      end of ntab,
      wa_ntab like line of ntab.

data : begin of itab occurs 0,
        empfrom(4),
        empto(4),
        duration type i,
       end of itab,
wa_itab like line of itab.

wa_ntab-emp = 'E1'.
wa_ntab-dept = 'MCE'.
wa_ntab-E1 = 0.
wa_ntab-E2 = 2.
wa_ntab-E3 = 4.
wa_ntab-E4 = 5.
wa_ntab-E5 = 6.
wa_ntab-E6 = 7.

Append wa_ntab to ntab.

wa_ntab-emp = 'E2'.
wa_ntab-dept = 'MCE'.
wa_ntab-E1 = 4.
wa_ntab-E2 = 0.
wa_ntab-E3 = 5.
wa_ntab-E4 = 8.
wa_ntab-E5 = 2.
wa_ntab-E6 = 1.

Append wa_ntab to ntab.
wa_ntab-emp = 'E3'.
wa_ntab-dept = 'MCE'.
wa_ntab-E1 = 4.
wa_ntab-E2 = 6.
wa_ntab-E3 = 0.
wa_ntab-E4 = 4.
wa_ntab-E5 = 3.
wa_ntab-E6 = 9.

Append wa_ntab to ntab.
wa_ntab-emp = 'E4'.
wa_ntab-dept = 'MCE'.
wa_ntab-E1 = 2.
wa_ntab-E2 = 3.
wa_ntab-E3 = 4.
wa_ntab-E4 = 0.
wa_ntab-E5 = 5.
wa_ntab-E6 = 2.

Append wa_ntab to ntab.

wa_ntab-emp = 'E5'.
wa_ntab-dept = 'MCE'.
wa_ntab-E1 = 4.
wa_ntab-E2 = 4.
wa_ntab-E3 = 7.
wa_ntab-E4 = 5.
wa_ntab-E5 = 0.
wa_ntab-E6 = 8.

Append wa_ntab to ntab.

wa_ntab-emp = 'E6'.
wa_ntab-dept = 'MCE'.
wa_ntab-E1 = 1.
wa_ntab-E2 = 1.
wa_ntab-E3 = 2.
wa_ntab-E4 = 4.
wa_ntab-E5 = 7.
wa_ntab-E6 = 0.
Append wa_ntab to ntab.


write:/ 'EMP','DEPT',21 'E1' ,33 'E2',45 'E3',57 'E4',69 'E5',81 'E6'.
write:/ '----------------------------------------------------------------------------------'.
loop at ntab.
write:/ ntab-EMP, ntab-DEPT, ntab-E1, ntab-E2, ntab-E3, ntab-E4, ntab-E5, ntab-E6.
endloop.

loop at ntab.

wa_itab-empfrom = ntab-emp.
wa_itab-empto = 'E1'.
wa_itab-duration = ntab-E1.
append wa_itab to itab.

wa_itab-empfrom = ntab-emp.
wa_itab-empto = 'E2'.
wa_itab-duration = ntab-E2.
append wa_itab to itab.

wa_itab-empfrom = ntab-emp.
wa_itab-empto = 'E3'.
wa_itab-duration = ntab-E3.
append wa_itab to itab.

wa_itab-empfrom = ntab-emp.
wa_itab-empto = 'E4'.
wa_itab-duration = ntab-E4.
append wa_itab to itab.

wa_itab-empfrom = ntab-emp.
wa_itab-empto = 'E5'.
wa_itab-duration = ntab-E5.
append wa_itab to itab.

wa_itab-empfrom = ntab-emp.
wa_itab-empto = 'E6'.
wa_itab-duration = ntab-E6.
append wa_itab to itab.

*add above 4 lines for more number
* of rows...
endloop.

skip 5.
write:/ 'EMPFROM', 'EMPTO', 'DURATION'.

loop at itab.
write:/ itab-empfrom, itab-empto, itab-duration.
endloop.

Regards,

Samson Rodrigues.

[Former Member]

hi samson,

that is the format of the excel sheet that i got. but the number of employees that will be there is unknown. the number i have given is only a sample. Actually it can be any number, but one thing, the number of employees that are given in the coloumns and rows would always be equal. How abt doing it in this case?

Regards,

Seenu

[Former Member]

Hi Seenu,

I understand ur requirement and have modified my code accordingly.

*&---------------------------------------------------------------------*
*& Report  ZSAMTEST_SAP
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

report  zsamtest_sap.

data: begin of ntab occurs 0,
        emp(4),
        dept(5),
        e1 type i,
        e2 type i,
        e3 type i,
        e4 type i,
        e5 type i,
        e6 type i,
      end of ntab,
      wa_ntab like line of ntab.

data: fldname(3) type c,
      fldlabel(3) type c,
      counter type string,
      typ(1) type c,
      comp type i.

field-symbols : <fs> type any.

data : begin of itab occurs 0,
        empfrom(4),
        empto(4),
        duration type i,
       end of itab,
wa_itab like line of itab.

wa_ntab-emp = 'E1'.
wa_ntab-dept = 'MCE'.
wa_ntab-e1 = 0.
wa_ntab-e2 = 2.
wa_ntab-e3 = 4.
wa_ntab-e4 = 5.
wa_ntab-e5 = 6.
wa_ntab-e6 = 7.

append wa_ntab to ntab.

wa_ntab-emp = 'E2'.
wa_ntab-dept = 'MCE'.
wa_ntab-e1 = 4.
wa_ntab-e2 = 0.
wa_ntab-e3 = 5.
wa_ntab-e4 = 8.
wa_ntab-e5 = 2.
wa_ntab-e6 = 1.

append wa_ntab to ntab.
wa_ntab-emp = 'E3'.
wa_ntab-dept = 'MCE'.
wa_ntab-e1 = 4.
wa_ntab-e2 = 6.
wa_ntab-e3 = 0.
wa_ntab-e4 = 4.
wa_ntab-e5 = 3.
wa_ntab-e6 = 9.

append wa_ntab to ntab.
wa_ntab-emp = 'E4'.
wa_ntab-dept = 'MCE'.
wa_ntab-e1 = 2.
wa_ntab-e2 = 3.
wa_ntab-e3 = 4.
wa_ntab-e4 = 0.
wa_ntab-e5 = 5.
wa_ntab-e6 = 2.

append wa_ntab to ntab.

wa_ntab-emp = 'E5'.
wa_ntab-dept = 'MCE'.
wa_ntab-e1 = 4.
wa_ntab-e2 = 4.
wa_ntab-e3 = 7.
wa_ntab-e4 = 5.
wa_ntab-e5 = 0.
wa_ntab-e6 = 8.

append wa_ntab to ntab.

wa_ntab-emp = 'E6'.
wa_ntab-dept = 'MCE'.
wa_ntab-e1 = 1.
wa_ntab-e2 = 1.
wa_ntab-e3 = 2.
wa_ntab-e4 = 4.
wa_ntab-e5 = 7.
wa_ntab-e6 = 0.
append wa_ntab to ntab.


write:/ 'EMP','DEPT',21 'E1' ,33 'E2',45 'E3',57 'E4',69 'E5',81 'E6'.
write:/ '----------------------------------------------------------------------------------'.
loop at ntab.
  write:/ ntab-emp, ntab-dept, ntab-e1, ntab-e2, ntab-e3, ntab-e4, ntab-e5, ntab-e6.
endloop.

fldlabel = 'E'.
counter = 1.

*get number of fields.
describe field ntab type typ components comp.
*removing the first 2 (name and dept)
comp = comp - 2.

loop at ntab.

  do  comp  times.
    concatenate fldlabel counter into fldname.

    wa_itab-empfrom = ntab-emp.
    wa_itab-empto = fldname.

    translate fldname to upper case.

    assign component fldname of structure ntab to <fs>.

    wa_itab-duration = <fs>.
    append wa_itab to itab.
    counter = counter + 1.

  enddo.

  counter = 1.
endloop.



skip 5.
write:/ 'EMPFROM', 'EMPTO', 'DURATION'.

loop at itab.
  write:/ itab-empfrom, itab-empto, itab-duration.
endloop.

I've tested this, hope it helps.

Regards,

Samson Rodrigues.

[Former Member]

HI Check this :

DATA: BEGIN OF intern OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF intern.

PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,

begcol TYPE i DEFAULT 1 NO-DISPLAY,

begrow TYPE i DEFAULT 1 NO-DISPLAY,

endcol TYPE i DEFAULT 100 NO-DISPLAY,

endrow TYPE i DEFAULT 32000 NO-DISPLAY.

You can declare above as Constants or variables also as per ur need :

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = begcol

i_begin_row = begrow

i_end_col = endcol

i_end_row = endrow

TABLES

intern = intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

hope it helps .

Praveen

[Former Member]

HI refer this program

in this

excel to internal table and then application server

&----

---

*& Report ZSD_EXCEL_INT_APP

*&

&----

---

*&

*&

&----

---

REPORT ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,

f1(20),

f2(40),

f3(20),

end of it_tab1.

data : it_tab type table of ALSMEX_TABLINE with header line,

file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,

wa_tab2 type it_tab1,

w_message(100) TYPE c.

at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

• PROGRAM_NAME = SYST-REPID

• DYNPRO_NUMBER = SYST-DYNNR

• FIELD_NAME = ' '

STATIC = 'X'

• MASK = ' '

CHANGING

file_name = file_nm

EXCEPTIONS

MASK_TOO_LONG = 1

OTHERS = 2

.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

start-of-selection.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '10'

i_end_row = '35'

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.

loop at it_tab.

case it_tab-col.

when '002'.

wa_tab2-f1 = it_tab-value.

when '004'.

wa_tab2-f2 = it_tab-value.

when '008'.

wa_tab2-f3 = it_tab-value.

endcase.

at end of row.

append wa_tab2 to it_tab2.

clear wa_tab2.

endat.

endloop.

data : p_file TYPE rlgrap-filename value 'TEST3.txt'.

OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

*--- Display error messages if any.

IF sy-subrc NE 0.

MESSAGE e001(zsd_mes).

EXIT.

ELSE.

*---Data is downloaded to the application server file path

LOOP AT it_tab2 INTO wa_tab2.

TRANSFER wa_tab2 TO p_file.

ENDLOOP.

ENDIF.

*--Close the Application server file (Mandatory).

CLOSE DATASET p_file.

loop at it_tab2 into wa_tab2.

write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

endloop.