Validating some fields like Co Code & Bus Area

[Former Member]

Dear Friends,

I am uploading Co Codes & Bus Area from excel sheets into the Ztable. Now I want to check if the Co Codes & Bus Area are not existing in the table T001 & TGSB then those values should not be uploaded in the zTable & should throw the error.

Plz help me in doing this.

Waiting for ur responses.

Thanks & Reg,

Nishu

[Former Member]

Hi Nishu,

Use the FM ALSM EXCELTO_INTERNAL_TABLE to transfer data from EXCEL..

after having transfered..

assume columns 3 and 4 are the ones you have to check in each record..

  loop at itab where col = '0003' and col = '0004'.
select from table...
 where <f> = itab-value.
if sy-subrc NE 0.
*throw error.
endif.
   endloop.

regards

satesh

[Former Member]

Hi Satesh,

I am posting the code for you, plz tell me where to put the correct code to check the fields from the table.

tables: bseg, zfi_tbl_qty.

data:

t_excel like alsmex_tabline occurs 0 with header line,

l_excelfile(128) type c,

l_excelfile1(128) type c,

l_excelfile2(128) type c,

l_excelfile3(128) type c.

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,

l_count type i.

data : q_count type i value 1,

v_flag type i value 0,

v_gsber like t_type2-gsber.

selection-screen begin of block b1 with frame title text-001.

parameter : p_file1 type rlgrap-filename obligatory,

p_file2 type rlgrap-filename obligatory,

p_file3 type rlgrap-filename obligatory,

p_file4 type rlgrap-filename obligatory.

selection-screen end of block b1.

*perform updation_tbl.

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

delete from zfi_tbl_qty.

*selection-screen

at selection-screen on value-request for p_file1.

call function 'KD_GET_FILENAME_ON_F4'

exporting

field_name = p_file1

changing

file_name = p_file1.

at selection-screen on value-request for p_file2.

call function 'KD_GET_FILENAME_ON_F4'

exporting

field_name = p_file2

changing

file_name = p_file2.

at selection-screen on value-request for p_file3.

call function 'KD_GET_FILENAME_ON_F4'

exporting

field_name = p_file3

changing

file_name = p_file3.

at selection-screen on value-request for p_file4.

call function 'KD_GET_FILENAME_ON_F4'

exporting

field_name = p_file4

changing

file_name = p_file4.

at selection-screen.

l_excelfile = p_file1.

l_excelfile1 = p_file2.

l_excelfile2 = p_file3.

l_excelfile3 = p_file4.

if l_excelfile = l_excelfile1 or l_excelfile = l_excelfile2 or l_excelfile = l_excelfile3

or l_excelfile1 = l_excelfile2 or l_excelfile1 = l_excelfile3 or l_excelfile2 = l_excelfile3.

message e010(zn) with 'Files with the same name found Please enter again. '.

endif.

start-of-selection.

perform upload_table using l_excelfile 5 2 6 4000.

perform upload_table using l_excelfile1 5 2 6 4000.

perform upload_table using l_excelfile2 5 2 6 4000.

perform upload_table using l_excelfile3 5 2 6 4000.

message i010(zn) with 'Files Uploaded Successfully.'.

************Calling the other program for posting

SUBMIT ZFI_REP_IS_CHARGES_POST.

*Calling the subroutine.

form upload_table using filename

i_begin_col type i

i_begin_row type i

i_end_col type i

i_end_row type i.

clear : t_excel.

refresh : t_excel.

v_flag = v_flag + 1.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = filename

i_begin_col = 5

i_begin_row = 2

i_end_col = 6

i_end_row = 4000

tables

intern = t_excel

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

case v_flag.

when '1'.

perform fill_int_tab.

when '2'.

perform fill_int_tab.

when '3'.

perform fill_int_tab.

when '4'.

perform fill_int_tab.

endcase.

endform. "upload_table

*uploading data from internal table into database table

&----

---

*& Form fill_int_tab

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form fill_int_tab .

clear : t_type2, q_count.

refresh : t_type2.

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.

loop at t_type2.

clear v_gsber.

l_count = l_count + 1 .

v_gsber = t_type2-gsber.

at end of gsber.

at end of bukrs.

zfi_tbl_qty-company_code = t_type2-bukrs.

zfi_tbl_qty-barea = v_gsber.

zfi_tbl_qty-comb = l_count.

if v_flag = 1.

zfi_tbl_qty-type = 'D'. " Desktop

endif.

if v_flag = 2.

zfi_tbl_qty-type = 'L'. " Lap Top

endif.

if v_flag = 3.

zfi_tbl_qty-type = 'N'. " Lotus Notes

endif.

if v_flag = 4.

zfi_tbl_qty-type = 'S'. " SAP ID

endif.

insert into zfi_tbl_qty values zfi_tbl_qty.

clear l_count.

endat.

endat.

endloop.

endform. " fill_int_tab

[Former Member]

Hi Nishu,

after the call to FM ALSM_EXCEL_TO_INTERNAL_TABLE the table t_excel has the fields

row|col|value

the value is stroerd in the corresponding field of itab for a given row and column ..

if you want to do a check on two of these fields with T001 .. then as said above just do a loop at t_excel at required columns and do a select on the tables.. find the column numbers before that

loop at t_excel where col = '0002' and col = '0003'.

*do a select with itab-value in the where condition..

*then throw an error mesage if it fails..

endloop.

regards

satesh

[Former Member]

Hi nishu,

1. Since u are directly uploading in your Z Table,

u need to explicitly write code

to check the valid values.

2. First u can upload the data of excel file

into internal table.

3. Then , before putting the data in your Z Table,

Loop at the internal table,

and query against T001 and TGSB.

If some values are found which are not valid,

then u can give some warning/error message.

4. The system will not automatically check these values.

regards,

amit m.