01-10-2007 11:59 AM
Hi
i need help.iam working on excelsheet.The excel sheet has 50fields,one of the field is company code.my work is to check the company code . If company code is repeated more than once, we are going to reject the file. The excel file will be tranferred to inbound control.. Pl give me a helpful solution. The company code is in column 6 of the excel sheet. the following is the sample code that im providing:
perform read_file .
loop at it_datatab into wa_datatab.
if wa_datatab-col06 GT '1'.
Stop.
endif.
endloop.
form read_file.
if wa_datatab-col06 > 1.
endif.
endform.
thanks
01-10-2007 12:18 PM
if wa_datatab-col06 GT '1'.
how will this suffice ?
GT ?? i think it must be a EQ just check this once.
Okay u have to check the company code if its occurance is more than one then u need to reject .
This is a possible solution.
sort itab by f6 .
loop at itab into wa_itab.
at new bukrs.
v_bukrs = wa_itab-bukrs. " take the company code into a variable
endat.
if wa_itab-f6 = v_bukrs . "equate the company code with the iterations
cnt = cnt + 1 . "initialise for the first time
if cnt gt 1.
exit.
endif.
endif.
at end of bukrs .
clear : v_bukrs,cnt. "clear the company code and count
endat.
endloop.
the logic is u need to take the file with only one company code .
so when cnt > 1 just reject the file .
if cnt eq 1.
validate this in at end of bukrs and then process u need to look this area .
just check this ..
regards,
vijay
01-10-2007 12:09 PM
Read file as follows - save ur file as text tab.make changes to the code as per ut Internal table
parameters: p_file like rlgrap-filename default 'C:\temp\emp.txt'.
data :begin of itab occurs 0,
pernr(8),
bdate(10),
edate(10),
mail(30),
end of itab.
start-of-selection.
perform read_file.
loop at itab.
if itab-pernr GT '1'.
Stop.
endif.
endloop.
End-of-selection.
message e000(000) with 'Duplicate Company Code'.
FORM read_file .
DATA: full_file_name TYPE string.
full_file_name = p_file.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = full_file_name
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = ','
HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =
HEADER =
TABLES
DATA_TAB = itab
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
.
IF SY-SUBRC <> 0.
MESSAGE e000(000) WITH 'Upload-Error; RC:' sy-subrc.
ENDIF.
ENDFORM. " read_file
01-10-2007 12:18 PM
if wa_datatab-col06 GT '1'.
how will this suffice ?
GT ?? i think it must be a EQ just check this once.
Okay u have to check the company code if its occurance is more than one then u need to reject .
This is a possible solution.
sort itab by f6 .
loop at itab into wa_itab.
at new bukrs.
v_bukrs = wa_itab-bukrs. " take the company code into a variable
endat.
if wa_itab-f6 = v_bukrs . "equate the company code with the iterations
cnt = cnt + 1 . "initialise for the first time
if cnt gt 1.
exit.
endif.
endif.
at end of bukrs .
clear : v_bukrs,cnt. "clear the company code and count
endat.
endloop.
the logic is u need to take the file with only one company code .
so when cnt > 1 just reject the file .
if cnt eq 1.
validate this in at end of bukrs and then process u need to look this area .
just check this ..
regards,
vijay
01-10-2007 1:12 PM
Hi vijay,
thanks for ur answer,here i have one more condition i.e "number of line items per document more than 200" we are rejecting the file.this field in excel sheet is col24.so,how can we proceed for this condition?
thanks
01-10-2007 1:23 PM
same process ..
fetch this field into a variable for no of line items
v_linno = wa_itab-linno. "---->ur field value.
if cnt gt 1 or v_linno Gt 200 . " ---> use the or condition
exit.
endif.
Just try to keep the code as simple as u can.
in case if cnt is eq 1 but number of lines gt 200 then we should not process that file so better use the or condition.
regards,
vijay
01-10-2007 1:46 PM
hi vijay,
once again thank you for ur reaply.and one more help i need from u.that is I have to convert the data in the internal table to a certain format XXXXXX and need to perform mapping for the same.
I got stuck at a point were in i need to follow certain rules to achieve that format like
1. left justify character fields
2. right justify numeric values
3. right justify amount decimal point and 2 decimal places not to use commas and $ symbol.
4.Null values need to be blank.
plz help me from this issue.
thanks
01-10-2007 12:28 PM
Hi,
After uploading that Excel file into your internal table, you can check that Comnay code is repeated or not.
First it is better move that internal table to another internla table
LOOP at ITAB.
read Second Internal table with keu BUKRS = First internal table-BUKRS.
If sy-subrc = 0.
Delete the Second Internal table header record.
Endif.
read Second Internal table with keu BUKRS = First internal table-BUKRS.
If Sy-Subrc = 0.
If this is success, then the Company code is repeted in the excel sheet. so reject the excel sheet
endif.
ENDLOOP.
Regards
Sudheer
01-10-2007 1:27 PM
Read file as follows - save ur file as text tab.make changes to the code as per ut Internal table
parameters: p_file like rlgrap-filename default 'C:\temp\emp.txt'.
data :begin of itab occurs 0,
pernr(8),
bdate(10),
edate(10),
mail(30),
end of itab.
start-of-selection.
perform read_file.
loop at itab.
if itab-pernr GT '1'.
Stop.
endif.
endloop.
End-of-selection.
message e000(000) with 'Duplicate Company Code'.
FORM read_file .
DATA: full_file_name TYPE string.
full_file_name = p_file.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = full_file_name
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = ','
HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =
HEADER =
TABLES
DATA_TAB = itab
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
.
IF SY-SUBRC <> 0.
MESSAGE e000(000) WITH 'Upload-Error; RC:' sy-subrc.
ENDIF.
ENDFORM. " read_file
Award points and close duplicate threads
01-10-2007 2:02 PM
You need to work this out with logic ..
rule 1.
see character fields are by default left justified
pass the value t
1. Try Fm NUMERIC_CHECK
example.
parameters : p_input(20) type c.
data : pout type string.
data : HTYPE LIKE DD01V-DATATYPE.
CALL FUNCTION 'NUMERIC_CHECK'
EXPORTING
string_in = p_input
IMPORTING
STRING_OUT = pout
HTYPE = htype.
write:/ p_input , htype .
so h type will tell u if its a Char or Numc
so based on this logic u can do the left justified or right justified.
( or )
suppose val = 'ABC1234' "--> WE KNOW THIS IS A CHAR.
If val <b>co</b> '0123456789'.
then val is numeric
else
val is character .
endif.
so o/p here is char and write it as left justified
for numc it is right justified.
this covers ur 1 and 2 .
3.
logic here is
val = 123456789.00
if val co '<b>.0123456789'</b>
then val is type p format as there is a dot and numbers so we 'll make the logic work as amount format right justified.
for commas and '$' use logic as
if val cs ',' or 'val = '$'
replace ',' with space into val. "---> u need to use this
replace '$' with space into val. "---> use replace if u encounter '$' or ',' okay.
4.
if val = ' ' or val = 0 .
val = space.
endif.
Im just giving u an idea how to make this thing work .
work on these logics . this will work .
regards,
vijay
01-10-2007 2:05 PM
check this logic to catch only numerics ..
parameters : val(30) type c.
data : final(30) type c,
cnt type i,
v type c,
n type i.
cnt = strlen( val ).
do cnt times.
move val+n(1) to v.
if v ca '0123456789'.
move v to final+n(1).
endif.
n = n + 1.
if n = cnt .
exit.
endif.
enddo.
condense final no-gaps.
write:/ final .
you need to work the logic like this ..
regards,
vijay
01-11-2007 1:21 PM
Hi vijay,
Thanks for ur reply.iam using the code wht u have suggested Really thank u very much.and one more help.iam working on excelsheet.user can fill the coloumns manually.it has 50 coloums.now i convert the excel file to certain Structure and performing mapping for the excel file fields and the Structure.The Structure realted to finanacial related one and excel fields are also same.now the doubt is how can we map Financial fields suppose GL-GL,GL-AP.GL-AR,AP-AR,AR-AR etc.
plz help me
helpful answers get rewarded.
thanks