topic Re: Upload excel data to custom DDIC table in Application Development and Automation Discussions

Upload excel data to custom DDIC table

Former Member — Tue, 27 Jan 2009 05:37:03 GMT

Hi,

I have 5 different custom data dictionary tables and 5 excel sheets containing data in the same format as the DDIC tables. Each excel sheet has 500,000 - 700,000 records. I need to transfer the data, as is, from the excel sheets directly to the corresponding DDIC tables.

Any idea how to go about this? Help is appreciated, thanks.

Regards

Re: Upload excel data to custom DDIC table

Former Member — Tue, 27 Jan 2009 05:47:04 GMT

hi.

try this code.

Sample Code gives a clear picture

&----

*& Report ZPAD_P27

&----

REPORT zpad_p27 MESSAGE-ID zvalid.

DATA : BEGIN OF itab1 OCCURS 0,

record(132),

END OF itab1.

DATA : BEGIN OF itab2 OCCURS 0,

ecode(4),

ename(40),

esal(6),

END OF itab2.

DATA: v_fname LIKE rlgrap-filename.

data: variuabl1 type string.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING
PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '
STATIC = ' '
MASK = ' '

CHANGING

file_name = v_fname

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.

variuabl1 = v_fname.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = variuabl1

FILETYPE = 'ASC'

tables

data_tab = ITAB1

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 i000 WITH ' Read Failed'.

ELSE.

LOOP AT itab1.

SPLIT ITAB1 AT ',' INTO ITAB2-ECODE ITAB2-ENAME ITAB2-ESAL.

APPEND ITAB2.

WRITE: / itab1-record.

ENDLOOP.

ENDIF.

SKIP 4.

LOOP AT ITAB2.

WRITE 😕 ITAB2-ECODE,ITAB2-ENAME,ITAB2-ESAL.

ENDLOOP.

Re: Upload excel data to custom DDIC table

Former Member — Tue, 27 Jan 2009 05:52:59 GMT

hi,

upload data from excel sheet to internal table.

then loop the internal table and use insert statement to upload to z tables.

Re: Upload excel data to custom DDIC table

kamesh_g — Tue, 27 Jan 2009 11:12:16 GMT

up load your data into internal table using GUI_upload (SAP above 4 ) or upload ( 4.0 B ) and update yuor DDIC table with that internal table

Edited by: KAMESH G on Jan 27, 2009 12:19 PM

Re: Upload excel data to custom DDIC table

Former Member — Wed, 28 Jan 2009 07:16:22 GMT

use bdc data transfer session method for this

Re: Upload excel data to custom DDIC table

Former Member — Wed, 28 Jan 2009 07:59:08 GMT

Hi Khan ,

Here I have given given the code .Just execute this code. Before that convert user excel sheet into a tablimited text document.

data : it like ztestmigo occurs 0 with header line,

FILETABLE type table of FILE_TABLE,

wa_filetable like line of filetable,

wa_filename type string,

rc type i.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG

CHANGING

FILE_TABLE = filetable

RC = rc.

IF SY-SUBRC = 0.

read table filetable into wa_filetable index 1.

move wa_filetable-FILENAME to wa_filename.

Else.

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

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

ENDIF.

start-of-selection.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = wa_filename

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

TABLES

DATA_TAB = it.

IF SY-SUBRC <> 0.

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

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

ENDIF.

insert ztestmigo from table it.

if sy-subrc = 0.

commit work.

else.

rollback work.

endif.

I hope it will work..If u have any doubt just let me know.

Rgds.

siva

Re: Upload excel data to custom DDIC table

Former Member — Tue, 03 Feb 2009 12:29:47 GMT

hi,

1)Create a internal table of similar structure as the DDIC/EXCEL sheet

2)Upload the data to this internal table using FM

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

I_LINE_HEADER =

i_tab_raw_data = it_text

i_filename = w_file " Excel file name(complete path)

TABLES

i_tab_converted_data = t_tab " Internal table

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

3)Transfer the data from internal table to the DB table

using INSERT dbtab FROM TABLE t_tab

Re: Upload excel data to custom DDIC table

Former Member — Tue, 03 Feb 2009 13:01:42 GMT

Hi Khan,

Below link shows you complete process and procedure step by step even though it was

shown with standard tables follow the same with your customized tables you can achieve the task.

Hope my answer will definitely helps you.

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/309cb157-738d-2910-7691-b74c4ddba3c7

CHeers!!

VENk@

Re: Upload excel data to custom DDIC table

Former Member — Tue, 03 Feb 2009 21:36:09 GMT

Hi Khan,

Apart from all the solution provided above, I have another FM which might be interesting and helpful for you.

The solution for your problem is :

Firstly upload the data from Excel file to the internal table using the function module

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = <Filename>.xls

i_begin_col = <Beginning_col>

i_begin_row = <Beginning_row>

i_end_col = <Number_of_columns>

i_end_row = <Number_of_rows>

tables

intern = <internal_table>

EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3

then from the internal table update the database table by using insert/modify/update statement.

Re: Upload excel data to custom DDIC table

Former Member — Wed, 04 Feb 2009 05:18:07 GMT

Hi,

this is very simple,as mentioned in all of the above post you simply need to

1.Upload the data from an excel sheet to an internal table(same structure as of db table,but data type shuold be char with length same as defined in data element)

FM used for this is TEXT_CONVERT_XLS_TO_SAP , for which include TRUXS as TYPE-POOLS and pass the internal table declared above,xls file name and an internal table of type truxs_t_text_data as the exporting parametrs.

2. Once data is uploaded in the internal table now simply put a loop on the internal table adn modify the ztable subsequently.

Feel free to ask in case of doubt.

Hope this will help you.

Pooja

Re: Upload excel data to custom DDIC table

awin_prabhu — Wed, 04 Feb 2009 09:32:19 GMT

Hi friend,

Step: 1. Upload data from excel to intenal table.

Use Function Module 'GUI_UPLOAD' as such

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = 'C:\Testing.xls'

filetype = 'ASC'

has_field_separator = 'X'

TABLES

data_tab = g_t_mara1.

Use simply T-code CG3Z for uploading data into presentation server.

Step 2. Now use insert statement for adding data from intenal table into DDIC table.

Ex: Loop at g_t_mara1 into wa.

INSERT db_table FROM wa.

Endloop.

Make sure that wa has same structure as internal table.

Thanks........

Re: Upload excel data to custom DDIC table

Former Member — Wed, 04 Feb 2009 10:13:41 GMT

Hi,

1...

Create local datatype as

TYPES type_s_raw(4096) TYPE c.

Declare a work area as

data fs_tab TYPE type_s_raw.

Declare internal table as

t_tab LIKE STANDARD TABLE OF fs_tab.

The above procedure would take XL contents in a clubbed manner.

Declare another internal for holding the contents of XL sheet as seperate columns .

Then call the function module

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

i_tab_raw_data = t_tab

i_filename = ( file path )

TABLES

i_tab_converted_data = ( Internal table )

EXCEPTIONS

conversion_failed = 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.

This would load Xl contents into internal table .

Then loop at this internal table and use insert/modify/update statements to upload into custom tables.

2...

There is another function module

alsm_excel_to_internal_table ---

this would take excel contents in row,col and filed values(structure),

then you need to write logic for assigning field values into internal table which I feel could be time taking.

So, better make use of 1st method .

Thanks.

Edited by: Sreesudha Gullapalli on Feb 4, 2009 11:13 AM

Re: Upload excel data to custom DDIC table

Former Member — Wed, 04 Feb 2009 10:30:52 GMT

Hi Khan,

U can use the following function module

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

and upload the data from your excel file into SAP internal Table

and update the data using UPDATE statement.

Thanks

Ravi Aswani

Re: Upload excel data to custom DDIC table

Former Member — Fri, 06 Feb 2009 10:23:04 GMT

HI.

FM : 'F4_FILENAME' FUNCTION MODULE TO BE USED PATH READING. (KEY IS F4).

FM :' ALSM_EXCEL_TO_INTERNAL_TABLE' FOR UPLOADING EXCEL SHEET

write the code as

at selection-screen on value request p_file.

perform browse_file.

with regards,

sumanth reddy

Edited by: sumanth reddy on Feb 6, 2009 11:23 AM

Re: Upload excel data to custom DDIC table

Former Member — Tue, 24 Feb 2009 09:52:26 GMT

HI.

FM : 'F4_FILENAME' FUNCTION MODULE TO BE USED PATH READING. (KEY IS F4).

FM :' ALSM_EXCEL_TO_INTERNAL_TABLE' FOR UPLOADING EXCEL SHEET

write the code as

at selection-screen on value request p_file.

perform browse_file.