reg transfer of data from excel to internal table

Former Member · ‎05-16-2007

hi

i need to upload datas from Excel sheet to Internal Table.For this i use d FM

ALSM_EXCEL_TO_INTERNAL_TABLE.but the problem is if a particular field has no value in excel sheet,then its not uploaded into internal table.then next field value has come to that blank place in d internal table. what i need is if the excel sheet has a blank value for a field then a blank value is added in d inetrnal table also.

Former Member · ‎05-16-2007

Hi Kumar,

Just have a look at the following example program . First upload the file using the fm then process the data using the required internal table.

REPORT ZEXL_UPLOAD.

tables: ZEMPLOY.

Internal table for holding data from the excel sheet

DATA: tb_intern TYPE STANDARD TABLE OF alsmex_tabline WITH HEADER LINE.

Internal table like database table to hold excel data.

data: begin of tb_data occurs 0,

field1(5),

field2(5),

end of tb_data.

DATA: BEGIN OF tb_data_tab OCCURS 0.

INCLUDE STRUCTURE ZEMPLOY.

DATA: END OF TB_DATA_TAB.

Constants

constants : c_1 type i value 1, "Constant value of 1

c_2 type i value 2, "Constant value of 2

c_6 type i value 6, "Constant value of 6

c_28 type i value 15, "Constant value of 15

c_9999 type i value 9999, "Constant value of 9999

c_x type c value 'X'. "Constant for value 'X'

Selection screen

selection-screen begin of block b1 with frame title text-001.

parameters: p_file like rlgrap-filename obligatory. " File name

parameters: p_colbeg type i default c_1 no-display. " Column beginning

parameters: p_colend type i default c_28 no-display. " Column end

parameters: p_rowbeg type i default c_2 no-display. " Row beginning

parameters: p_rowend type i default c_9999 no-display." Row end

selection-screen end of block b1.

----

AT S E L E C T I O N S C R E E N ON VALUE REQUEST

----

at selection-screen on value-request for p_file.

To get the F4 help for file

perform get_filename.

----

S T A R T O F S E L E C T I O N *

----

start-of-selection.

Load data from Excel file

perform upload_data.

perform process_data.

perform update_table.

&----

*& Form get_filename

&----

text

----

FORM get_filename .

Function module used for F4 help

call function 'F4_FILENAME'

exporting

program_name = syst-cprog

dynpro_number = syst-dynnr

importing

file_name = p_file.

ENDFORM. " get_filename

&----

*& Form upload_data

&----

text

----

FORM upload_data .

data: l_excel type rlgrap-filename. " Variable for filename

l_excel = p_file.

FM to upload data from excel sheet to internal table

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = l_excel

i_begin_col = p_colbeg

i_begin_row = p_rowbeg

i_end_col = p_colend

i_end_row = p_rowend

tables

intern = tb_intern

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc = 0.

endif.

ENDFORM. " upload_data

&----

*& Form process_data

&----

text

----

FORM process_data .

*--Processing the internal table generated from the function module

*--Passing data from Excel file to internal table

clear: tb_data.

loop at tb_intern.

AT NEW row.

CLEAR tb_data_tab.

ENDAT.

CASE tb_intern-col.

WHEN '0001'.

tb_data_tab-KUNNR = tb_intern-value.

WHEN '0002'.

tb_data_tab-name1 = tb_intern-value.

ENDCASE.

*--Appending the internal table tb_data

AT END OF row.

APPEND tb_data_tab.

CLEAR tb_data_tab.

ENDAT.

ENDLOOP.

ENDFORM. " process_data

&----

*& Form update_table

&----

text

----

FORM update_table .

modify ZEMPLOY FROM table tb_data_tab ."client specified.

ENDFORM. " update_table

Regards,

Jayaram...

Former Member · ‎05-16-2007

Use below program logic

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

Program : ZLWMI151_UPLOAD(Data load to ZBATCH_CROSS_REF Table)
Type : Upload program
Author : Seshu Maramreddy
Date : 05/16/2005
Transport : DV3K919574
Transaction: None
Description: This program will get the data from XLS File
and it upload to ZBATCH_CROSS_REF Table

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

REPORT ZLWMI151_UPLOAD no standard page heading

line-size 100 line-count 60.

*tables : zbatch_cross_ref.

data : begin of t_text occurs 0,

werks(4) type c,

cmatnr(15) type c,

srlno(12) type n,

matnr(7) type n,

charg(10) type n,

end of t_text.

data: begin of t_zbatch occurs 0,

werks like zbatch_cross_ref-werks,

cmatnr like zbatch_cross_ref-cmatnr,

srlno like zbatch_cross_ref-srlno,

matnr like zbatch_cross_ref-matnr,

charg like zbatch_cross_ref-charg,

end of t_zbatch.

data : g_repid like sy-repid,

g_line like sy-index,

g_line1 like sy-index,

$v_start_col type i value '1',

$v_start_row type i value '2',

$v_end_col type i value '256',

$v_end_row type i value '65536',

gd_currentrow type i.

data: itab like alsmex_tabline occurs 0 with header line.

data : t_final like zbatch_cross_ref occurs 0 with header line.

selection-screen : begin of block blk with frame title text.

parameters : p_file like rlgrap-filename obligatory.

selection-screen : end of block blk.

initialization.

g_repid = sy-repid.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = g_repid

IMPORTING

FILE_NAME = p_file.

start-of-selection.

Uploading the data into Internal Table

perform upload_data.

perform modify_table.

top-of-page.

CALL FUNCTION 'Z_HEADER'

EXPORTING
FLEX_TEXT1 =
FLEX_TEXT2 =
FLEX_TEXT3 =

.

&----

*& Form upload_data

&----

text

----

FORM upload_data.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = $v_start_col

I_BEGIN_ROW = $v_start_row

I_END_COL = $v_end_col

I_END_ROW = $v_end_row

TABLES

INTERN = itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

write:/10 'File '.

ENDIF.

if sy-subrc eq 0.

read table itab index 1.

gd_currentrow = itab-row.

loop at itab.

if itab-row ne gd_currentrow.

append t_text.

clear t_text.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'.

t_text-werks = itab-value.

when '0002'.

t_text-cmatnr = itab-value.

when '0003'.

t_text-srlno = itab-value.

when '0004'.

t_text-matnr = itab-value.

when '0005'.

t_text-charg = itab-value.

endcase.

endloop.

endif.

append t_text.

ENDFORM. " upload_data

&----

*& Form modify_table

&----

Modify the table ZBATCH_CROSS_REF

----

FORM modify_table.

loop at t_text.

t_final-werks = t_text-werks.

t_final-cmatnr = t_text-cmatnr.

t_final-srlno = t_text-srlno.

t_final-matnr = t_text-matnr.

t_final-charg = t_text-charg.

t_final-erdat = sy-datum.

t_final-erzet = sy-uzeit.

t_final-ernam = sy-uname.

t_final-rstat = 'U'.

append t_final.

clear t_final.

endloop.

delete t_final where werks = ''.

describe table t_final lines g_line.

sort t_final by werks cmatnr srlno.

Deleting the Duplicate Records

perform select_data.

describe table t_final lines g_line1.

modify zbatch_cross_ref from table t_final.

if sy-subrc ne 0.

write:/ 'Updation failed'.

else.

Skip 1.

Write:/12 'Updation has been Completed Sucessfully'.

skip 1.

Write:/12 'Records in file ',42 g_line .

write:/12 'Updated records in Table',42 g_line1.

endif.

delete from zbatch_cross_ref where werks = ''.

ENDFORM. " modify_table

&----

*& Form select_data

&----

Deleting the duplicate records

----

FORM select_data.

select werks

cmatnr

srlno from zbatch_cross_ref

into table t_zbatch for all entries in t_final

where werks = t_final-werks

and cmatnr = t_final-cmatnr

and srlno = t_final-srlno.

sort t_zbatch by werks cmatnr srlno.

loop at t_zbatch.

read table t_final with key werks = t_zbatch-werks

cmatnr = t_zbatch-cmatnr

srlno = t_zbatch-srlno.

if sy-subrc eq 0.

delete table t_final .

endif.

clear: t_zbatch,

t_final.

endloop.

ENDFORM. " select_data

Reward points if it is helpful

Thanks

Seshu