Upload excel data to custom DDIC table

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

hi,

upload data from excel sheet to internal table.

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

[kamesh_g]

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

[Former Member]

use bdc data transfer session method for this

[Former Member]

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

[Former Member]

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

[Former Member]

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-b74c4ddb...

CHeers!!

VENk@

[Former Member]

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.

[Former Member]

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

[awin_prabhu]

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.

OR

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.