topic Re: how to upload Excell file data into database table in Application Development and Automation Discussions

how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 09:30:58 GMT

Hi,

can you tell me the procedure for upload excell file data into database table. Here i am sending my code, if i am wrong ket me know the actual process.

&----

*& Report ZTESTREPORT

&----

REPORT ZTESTREPORT.

*TABLES: ZCRM_DM_VBRK.

TYPES: BEGIN OF TS_ZCRM_DM_VBRK,

VBELN TYPE ZCRM_DM_VBRK-VBELN,

VKORG TYPE ZCRM_DM_VBRK-VKORG,

BUKRS TYPE ZCRM_DM_VBRK-BUKRS,

KUNAG TYPE ZCRM_DM_VBRK-KUNAG,

FKDAT TYPE ZCRM_DM_VBRK-FKDAT,

ERDAT TYPE ZCRM_DM_VBRK-ERDAT,

END OF TS_ZCRM_DM_VBRK,

TT_ZCRM_DM_VBRK TYPE STANDARD TABLE OF TS_ZCRM_DM_VBRK.

DATA: WA_ZCRM_DM_VBRK TYPE TS_ZCRM_DM_VBRK,

ITAB TYPE TT_ZCRM_DM_VBRK.

CALL FUNCTION 'WS_UPLOAD'

EXPORTING

CODEPAGE = ' '

FILENAME = 'C:\Documents and Settings\admin\Desktop\VBRK_DATA.XLS '

FILETYPE = 'ASC'

HEADLEN = ' '
LINE_EXIT = ' '
TRUNCLEN = ' '
USER_FORM = ' '
USER_PROG = ' '
DAT_D_FORMAT = ' '
IMPORTING
FILELENGTH =

TABLES

DATA_TAB = ITAB

EXCEPTIONS

CONVERSION_ERROR = 1

FILE_OPEN_ERROR = 2

FILE_READ_ERROR = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

NO_AUTHORITY = 10

OTHERS = 11 .

IF SY-SUBRC <> 0.

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

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

ENDIF.

LOOP AT ITAB INTO WA_ZCRM_DM_VBRK.

INSERT INTO ZCRM_DM_VBRK FROM WA_ZCRM_DM_VBRK.

ENDLOOP.

I am getting errors in loop stmt (INSERT INTO ZCRM_DM_VBRK FROM WA_ZCRM_DM_VBRK.) and if i commented the loop stmt and i chked up to the F.M part in the debugging mode values are comming like #### ##### #####.. So, can u correct the error.

Re: how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 09:35:42 GMT

Hi,

You should change the FM ALSM_EXCEL_TO_INTERNAL_TABLE

Then it will come into Rows and Columns, then rearrange it and take it into your required format and Internal table and then Loop it and INSERT it.

Reward if useful!

Re: how to upload Excell file data into database table

former_member189059 — Mon, 17 Sep 2007 09:37:09 GMT

WS_UPLOAD would only work properly for flat / csv files

for excel, SAP has a function module ALSM_EXCEL_TO_INTERNAL_TABLE

once you get the data into your internal table properly, you can use the same insert statement

Re: how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 09:41:53 GMT

HI,

CHECK THIS CODE

REPORT zmat_no message-id zebg.

TYPE-POOLS truxs.

TABLES:zmatnr.

DATA : itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

DATA row LIKE alsmex_tabline-row.

data : g_matnr like mara-matnr.

data : count type i.

data : itab_count type i.

data : gi_final like zmatnr occurs 0 with header line.

*data : begin of gi_final occurs 0,

mat_old like mara-matnr,
mat_new like mara-matnr,
end of gi_final.

***********************Selection Screen*************************

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETER : pfname LIKE rlgrap-filename OBLIGATORY.

select-options : records for count.

SELECTION-SCREEN END OF BLOCK b1.

*******************************************************************

*********************At Selection Screen*************************

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pfname.

PERFORM search.

*******************************************************************

START-OF-SELECTION.

perform process.

form process.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = pfname

i_begin_col = 1

i_begin_row = 2

i_end_col = 12

i_end_row = 65000

TABLES

intern = itab

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

describe table itab lines itab_count.

row = 1.

loop at itab.

if itab-row <> row.

append gi_final.

clear gi_final.

endif.

case itab-col.

when '1'.

CLEAR G_MATNR.

gi_final-OLD_MATNR = itab-value.

CONCATENATE 'NEW' gi_final-old_matnr INTO itab-value.

gi_final-new_MATNR = itab-value.

endcase.

row = itab-row.

append gi_final.

clear gi_final.

endloop.

CALL FUNCTION 'PROGRESS_INDICATOR'
EXPORTING
I_TEXT = 'File Has Been Successfully Uploaded from Workstation ' .

if not gi_final[] is initial.

if not records-low is initial .

if not records-high is initial.

records-high = records-high + 1.

DESCRIBE TABLE gi_final LINES count.

IF records-high < count.

DELETE gi_final FROM records-high TO count.

ENDIF.

IF records-low <> 1.

IF records-low <> 0.

DELETE gi_final FROM 1 TO records-low.

ENDIF.

endif.

IF NOT GI_FINAL[] IS INITIAL.

CALL FUNCTION 'PROGRESS_INDICATOR'

EXPORTING

I_TEXT = 'Processing zmatnr table'

I_OUTPUT_IMMEDIATELY = 'X'.

if itab_count <> count.

message i000 with 'records are not matching'.

exit.

else.

modify zmatnr from table gi_final.

message i000 with 'data base table modified successfully'.

endif.

endif.

endform.

&----

*& Form search

&----

text

----

--> p1 text
<-- p2 text

----

FORM search .

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = pfname.

ENDFORM. " search

Re: how to upload Excell file data into database table

paruchuri_nagesh — Mon, 17 Sep 2007 09:42:20 GMT

first use fm ALSM_EXCEL_TO_INTERNAL_TABLE

after that u have to update database tabse

update table name from itab

no need of upload fm

rewrad for use ful answers

regards

Nagesh.Paruchuri

Re: how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 09:42:56 GMT

Hi Siddu,

Try out the code below:

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE

TYPES:

BEGIN OF ty_upload,

field1 TYPE c length 12,

field2 TYPE c length 12,

field3 TYPE c length 12,

END OF ty_upload.

DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.

DATA wa_upload TYPE ty_upload.

DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.

FIELD-SYMBOLS: <wa> type alsmex_tabline.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = itab.

LOOP AT itab ASSIGNING <wa>.

CASE <wa>-col.

WHEN '0001'.

wa_upload-field1 = <wa>-value.

WHEN '0002'.

wa_upload-field2 = <wa>-value.

WHEN '0003'.

wa_upload-field3 = <wa>-value.

ENDCASE.

APPEND wa_upload TO it_upload.

CLEAR wa_upload.

ENDLOOP.

<b>**********another way*******</b>

TYPE-POOLS truxs.

tables : ztable.

types: begin of t_tab,

col1(5) type c,

col2(5) type c,

col3(5) type c,

end of t_tab.

data : itab type standard table of t_tab,

wa type t_tab.

data it_type type truxs_t_text_data.

parameter p_file type rlgrap-filename.

data ttab type tabname.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = p_file

start-of-selection.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

tables

i_tab_converted_data = itab[]

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.

end-of-selection.

loop at itab into wa.

ztable-col1 = wa-col1.

ztable-col2 = wa-col2.

ztable-col3 = wa-col3.

modify ztable.

endloop.

<b>Reward points if this Helps.

Manish</b>

Re: how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 09:44:22 GMT

HI,

here in your code you are using WS_UPLOAD this will not work in cas eof excel sheet properly.

use FM FM ALSM_EXCEL_TO_INTERNAL_TABLE.

in this fm you will get the entire excel in your itab with row column format.

now when you get this,.just put them in your dbtab.

rewrad if helpful

regards

vivekanand

Re: how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 09:53:24 GMT

Hi Prasad,

thnx for replay, But in that fun Mod. there is exporting parameters like

I_BEGIN_COL =

I_BEGIN_ROW =

I_END_COL =

I_END_ROW =

canu plz help me what should i give there. infact i have 45000 records are there in Excell file. I suppose to upload those record in Z TABLE. And help me how to write insert stmt. Bcoz my insert stmt is not worling it is giving an error. if u anwered this my prblem will solve and u will get reward points. Thnx in advance...

Re: how to upload Excell file data into database table

former_member189059 — Mon, 17 Sep 2007 09:55:46 GMT

begin row and col will both be 1 (unless you have a header)

end row will be 45000

end col is the number of columns in your excel file

Re: how to upload Excell file data into database table

Former Member — Mon, 17 Sep 2007 10:02:52 GMT

hi siddu,

begin of colum represents the column no in ur excel sheet. give 1,

start of row represents it reads records from tha row, suppose u r giving recors from first row keep it first, else if ur giving first row as names(headings) for that colums, then start with 2 nd row, same as in case of end column, end row, it will take up to maximum of 63000 records, u keep hoe many records u want to upload in last row.

in my code i used modify staement it works no need of insert. it inserts all the records in to ztable

regards

siva