10-09-2007 9:52 PM
Hi,
Iam using BAPI_SALESORDER_CREATEFROMDAT1 to create sales orders by writing custom program. The input file is in excel format with different sizes of rows as I have header record, Item record, Conditions, Partners etc..
Can anyone tell me how can I upload this type of file in my program?
Thanks,
Smitha.
10-09-2007 9:57 PM
Smitha,
one suggestion... If you have record indicators at the start of record then read the entire file into one internal table ..... the based on the record indicators devide the split the data into different internal tables based on the structure of the lines...
continue ur validations n transformations then assign the field values to the corresponding fields in bapi structures.....
10-09-2007 9:57 PM
Smitha,
one suggestion... If you have record indicators at the start of record then read the entire file into one internal table ..... the based on the record indicators devide the split the data into different internal tables based on the structure of the lines...
continue ur validations n transformations then assign the field values to the corresponding fields in bapi structures.....
10-10-2007 12:52 AM
Hi Naveen,
Can I upload the excel file directly? and How should I design my internal tables?
Thanks,
Smitha.
10-10-2007 3:03 AM
Hi,
I have an excel file with data in it. Each row has different set of data/size becoz of Header/Item/Schedule Line/Conditions data.
Could anyone please clarify the below doubts:
1) What is the best of way uploading this file. Using the excel file directly or converting this file to .txt file.
2) If I wanna use the excel file directly how do I do that as I have different sets of data in it. I mean how should I declare my internal table in the program.
3) If I wanna convert the excel to .txt and then upload, How do I do upload the data into SAP as I have different length of rows. And When convert the excel into .txt it only gives me tab-delimited. Then How should I declare my internal table.
Thanks,
Smitha.
10-10-2007 4:07 AM
Hi,
U can dircetly upload the exel file using the FM :
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'.
Declare internal table which has fields equivalent to number of columns in Excel file.
Revert back if any issues,
Reward if helpful.
Regards,
Naveen
10-10-2007 5:44 AM
Hi Smitha,
To get multiple data from one excel worksheet you need to do these steps :
1. Standardize your excel ( i.e. prepare the column mapping and define delimiters )
Delimiter will differentiate each section of the Excel worksheet
2. Create the program . Use function to upload the excel to a raw format ,
in this sample I used customized version of
3. Using the imported internal table, use the delimiter(s) to divide your internal table,
and further to assign the values to corresponding internal table(s) .
4. Use the internal table to fill your BAPI structures , and then call the BAPI.
I assume you know step number 4 ( well, actually you must _ ) ,
therefore I will provide you sample of data and code required .
=== Excel Data as below =========
NAMA USER
tes1 user1
tes2 user2
&&01 ====================
NO NAMA USER
1 tes1 user1
2 tes2 user2
=== Program Code as below =======
&----
*& Report ZTSW_002
*&
&----
*&
*&
&----
REPORT ZTSW_002.
DATA :
Variables to upload Excel
xfilename LIKE rlgrap-filename,
vf_start_col TYPE i VALUE '1',
vf_start_row TYPE i VALUE '1',
vf_end_col TYPE i VALUE '256',
vf_end_row TYPE i VALUE '65536',
if_intern LIKE zkcde_cells OCCURS 0 WITH HEADER LINE,
t1 LIKE if_intern-value, t2 LIKE if_intern-value,
t3 LIKE if_intern-value, t4 LIKE if_intern-value.
ITab Definition
DATA : begin of it_1 occurs 0,
nama(15),
user(15),
end of it_1,
begin of it_2 occurs 0,
no(3),
nama(15),
user(15),
end of it_2.
PARAMETER p_name LIKE xfilename .
START-OF-SELECTION .
CALL FUNCTION 'ZKCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = p_name
i_begin_col = vf_start_col
i_begin_row = vf_start_row
i_end_col = vf_end_col
i_end_row = vf_end_row
TABLES
intern = if_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
DATA : low_limit like if_intern-row,
high_limit like if_intern-row.
Get row no of delimiters no 1
READ TABLE if_intern with key value = '&&01' .
IF SY-SUBRC = 0.
low_limit = 2. " row 1 is for excel header table 1
high_limit = if_intern-row - 1. " the delimiter row is excluded
ENDIF.
Get Data for Table 1
LOOP AT if_intern
WHERE row >= low_limit
and row <= high_limit.
CASE if_intern-col.
Assign values per column to temporary variables
WHEN 1. t1 = if_intern-value.
WHEN 2. t2 = if_intern-value.
WHEN 3. t3 = if_intern-value.
WHEN 4. t4 = if_intern-value.
ENDCASE.
AT END OF row.
it_1-nama = t1.
it_1-user = t2.
APPEND it_1.
CLEAR: t1, t2, t3, t4.
ENDAT. " of row
ENDLOOP. " of if_intern loop 1
Update the limit rows
low_limit = high_limit + 2. " skip delimiter & header table 2
clear high_limit. " in case there is another delimiter,
" read the delimiter and assign row no to this .
LOOP AT if_intern WHERE row > low_limit.
CASE if_intern-col.
WHEN 1. t1 = if_intern-value.
WHEN 2. t2 = if_intern-value.
WHEN 3. t3 = if_intern-value.
WHEN 4. t4 = if_intern-value.
ENDCASE.
AT END OF row.
it_2-no = t1.
it_2-nama = t2.
it_2-user = t3.
APPEND it_2.
CLEAR: t1, t2, t3, t4.
ENDAT. " of row
ENDLOOP. " of if_intern loop 1
Proof Of Concept - display those itab values
write : / 'IT_1 VALUES BELOW'.
loop at it_1.
write: /(20) it_1-nama , '|',
(20) it_1-user .
endloop.
skip.
write : / 'IT_2 VALUES BELOW'.
loop at it_2.
write: /(3) it_2-no , '|',
(20) it_2-nama , '|',
(20) it_2-user .
endloop.
=== Program Output as below ======
IT_1 VALUES BELOW
tes1 user1
tes2 user2
IT_2 VALUES BELOW
1 tes1 user1
2 tes2 user2
Regards,
Tuwuh Sih Winedya
Message was edited by:
TUWUHSIH WINEDYA