topic Upload excel sheet in Application Development and Automation Discussions

Upload excel sheet

Former Member — Thu, 26 Jul 2007 19:08:08 GMT

Hi All!!

I would like to update data from an excel sheet to a custom table. I am using the function ws_upload, I would like to know what is the "file type" I need to give in that.

Thanks!

Re: Upload excel sheet

Former Member — Thu, 26 Jul 2007 19:12:41 GMT

I dont think you can use WS_UPLOAD..

use this code

types : begin of ty_filestructure,

row(500) type c,

end of ty_filestructure.

data : i_filedata type standard table of ty_filestructure

with header line.

data: l_fnam like rlgrap-filename,

l_filedata type kcde_intern,

l_fileline like line of l_filedata,

l_col type kcd_ex_col_n.

l_fnam = p_fnam.

pick up the sheet of the workbook that has the focus

call function 'KCD_EXCEL_OLE_TO_INT_CONVERT'

exporting

filename = l_fnam

i_begin_col = 1

i_begin_row = 1

i_end_col = 50

i_end_row = 2500

tables

intern = l_filedata

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc <> 0.

message i000(38)

with 'Error loading data - return code'

sy-subrc.

exit.

endif.

create real table rows from l_filedata; pull back empty cells as

well as populated ones

loop at l_filedata into l_fileline.

l_col = l_col + 1.

if l_fileline-col = l_col.

concatenate i_filedata

l_fileline-value

into i_filedata

separated by c_comma.

else.

while l_col < l_fileline-col.

concatenate i_filedata

space

into i_filedata

separated by c_comma.

l_col = l_col + 1.

endwhile.

concatenate i_filedata

l_fileline-value

into i_filedata

separated by c_comma.

endif.

at end of row.

shift i_filedata left deleting leading c_comma.

append i_filedata.

clear i_filedata.

clear l_col.

endat.

endloop.

Thansk

Mahesh

Re: Upload excel sheet

former_member194669 — Thu, 26 Jul 2007 19:31:05 GMT

Hi,

Check this


  data : lt_raw             type truxs_t_text_data.
  call function 'TEXT_CONVERT_XLS_TO_SAP'
    exporting
*      i_line_header        = 'X'
      i_tab_raw_data       = it_raw       " WORK TABLE always blank
      i_filename           = p_lname
    tables
      i_tab_converted_data = i_tab      "ACTUAL DATA
    exceptions
      conversion_failed    = 1
      others               = 2.

aRs

Re: Upload excel sheet

Former Member — Thu, 26 Jul 2007 19:37:46 GMT

Better to use other FM instead of WS_UPLOAD

Check the below program and i used FM 'ALSM_EXCEL_TO_INTERNAL_TABLE'

i uploaded the xls file data to custom 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

Thanks

Seshu

Re: Upload excel sheet

Former Member — Thu, 26 Jul 2007 20:05:09 GMT

Simple use FM : <b>TEXT_CONVERT_XLS_TO_SAP</b>

It is used to upload data from XLS sheet..

check :

type-pools: TRUXS.

data: t_tab_raw_data type TRUXS_T_TEXT_DATA.

data: begin of itab occurs 0,

fld1(10) type c,

fld2(10) type c,

end of itab.

start-of-selection.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

I_TAB_RAW_DATA = t_tab_raw_data

I_FILENAME = 'C:\temp\file1.XLS'

TABLES

I_TAB_CONVERTED_DATA = itab

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2

IF SY-SUBRC eq 0.

loop at itab.

write:/ itab-fld1, itab-fld2.

endloop.

endif.

Reward if useful

Regards

Prax