Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to upload excel sheet in bdc

Former Member
0 Kudos

Hi

I am uploading text file using function module GUI_UPLOAD

but while uploading EXcel sheet i am not getting it.

can u reply.

7 REPLIES 7

Former Member
0 Kudos

hi,

use the FM ALSM_EXCEL_TO_INTERNAL_TABLE.

PARAMETERS:

P_INFL like RLGRAP-FILENAME.

DATA:

BEGIN OF T_DATA1 OCCURS 0,

RESOURCE(25) TYPE C,

DATE(10) TYPE C,

DURATION TYPE P DECIMALS 2,

ACTIVITY(25) TYPE C,

B_NBILL(1) TYPE C,

END OF T_DATA1,

T_DATA TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

BEGIN OF T_FINAL OCCURS 0,

RESOURCE(25) TYPE C,

DATE(10) TYPE C,

DURATION(15) TYPE C,

ACTIVITY(25) TYPE C,

B_NBILL(10) TYPE C,

END OF T_FINAL.

DATA : HEADER TYPE XSTRING.

  • Work Variables Declaration.

CONSTANTS:

W_Y TYPE C VALUE 'Y',

W_N TYPE C VALUE 'N'.

  • Work area.

DATA:

WA_DATA LIKE T_FINAL.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFL.

PERFORM GET_FILENAME CHANGING P_INFL.

----


START-OF-SELECTION.

----


PERFORM UPLOAD_DATA_FROMEXCEL.

FORM UPLOAD_DATA_FROMEXCEL.

  • Downloading the data from presentation server

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_infl

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 8

I_END_ROW = 1000

TABLES

INTERN = T_DATA

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.

ENDFORM. " upload_data_fromexcel

&----


*& Form process_data

&----


  • text

----


FORM PROCESS_DATA .

T_FINAL-RESOURCE = 'Resource'.

T_FINAL-DATE = 'Date'.

T_FINAL-DURATION = 'Duration'.

T_FINAL-ACTIVITY = 'Activity'.

T_FINAL-B_NBILL = 'Billable'.

APPEND T_FINAL.

SORT T_DATA BY ROW COL.

LOOP AT T_DATA.

CASE T_DATA-COL.

WHEN 3.

T_DATA1-RESOURCE = T_DATA-VALUE.

WHEN 4.

T_DATA1-DATE = T_DATA-VALUE.

WHEN 5.

T_DATA1-DURATION = T_DATA-VALUE.

WHEN 6.

  • t_data1-activity = t_data-value.

WHEN 7.

T_DATA1-B_NBILL = T_DATA-VALUE.

ENDCASE.

AT END OF ROW.

COLLECT T_DATA1.

ENDAT.

ENDLOOP.

LOOP AT T_DATA1.

T_FINAL-RESOURCE = T_DATA1-RESOURCE.

T_FINAL-DATE = T_DATA1-DATE.

T_FINAL-DURATION = T_DATA1-DURATION.

T_FINAL-ACTIVITY = T_DATA1-ACTIVITY.

T_FINAL-B_NBILL = T_DATA1-B_NBILL.

APPEND T_FINAL.

ENDLOOP.

ENDFORM. " process_data

&----


*& Form get_filename

&----


FORM GET_FILENAME CHANGING P_FILENAME.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

DEF_FILENAME = SPACE

DEF_PATH = P_FILENAME

MASK = ',. ,..'

MODE = 'O' " O = Open, S = Save

  • TITLE = BOX_TITLE

IMPORTING

FILENAME = P_FILENAME

EXCEPTIONS

INV_WINSYS = 1

NO_BATCH = 2

SELECTION_CANCEL = 3

SELECTION_ERROR = 4

OTHERS = 5.

*

  • CASE SY-SUBRC.

  • WHEN 1.

  • MESSAGE I999 WITH

  • 'File selector not available on this windows system'(046).

  • WHEN 2.

  • MESSAGE E999 WITH

  • 'Frontend function cannot be executed in background'(047).

  • WHEN 3.

  • MESSAGE I999 WITH 'Selection was cancelled'(048).

  • WHEN 4.

  • MESSAGE E999 WITH 'Communication error'(049).

  • WHEN 5.

  • MESSAGE E999 WITH 'Other error'(050).

  • ENDCASE.

ENDFORM. " get_filename

Do reward if it helps,

priya.

Former Member
0 Kudos

Hi!

This is, because SAP cannot process excel shhets during uploading, only text based files.

When you are using GUI_UPLOAD, you have to upload only text based files.

If you want to upload an excel file, first, you have to convert it into a CSF file, within the excel, using the Save as... menu command.

The CSV is a text file, and the columns are separated with a ; charater in it.

Regards

Tamá

Former Member
0 Kudos

Hi gurus,

By using this function module we can upload data from excelsheet to sap.

'TEXT_CONVERT_XLS_TO_SAP'.

Directly we can uplaod the data from flat to sap.

Rgds,

P.Naganjana Reddy

Former Member
0 Kudos

hi Ravi

try to use function modules WS_UPLOAD or UPLOAD.

regards

ravish

<b>plz reward poinrs if helpful</b>

Former Member
0 Kudos

Hi Ravi,

There are two aspects here.

Using GUI_UPLOAD, you cannot upload an excel the was ccreated manually on the PC. The reason is that SAP only understands WK1 file format of excel. Either you create the file from GUI_DOWNLOAD or save the xl as an appropriate WK1 format.

Secondly, the GUI_UPLOAD should have the import parameter 'HAS_FIELD_SEPERATOR' passed as 'X'.

NAveen.

Former Member
0 Kudos

See the following ex:

*Read the data from the locally held spreadsheet

*Once read using this FM, the data will be held like:-

*ROW |COL |VALUE

*---|-|---

*0001 |0001 |2

*0001 |0002 |00000000001

*0001 |0003 |1

*0001 |0004 |

*0001 |0005 |2

*0001 |00010|SHORT TEXT for a/c

*0001 |00011|LONG TEXT a/c 0000000001

*.

*.

*0002 |0001 |2

*0002 |0002 |00000000002

*0002 |0003 |1

*0002 |0004 |

*0002 |0005 |2

*0002 |00010|SHORT TEXT for a/c

*0001 |00011|LONG TEXT a/c 0000000002

*

*etc........

data: begin of excel_tab occurs 0.

include structure alsmex_tabline.

data: end of excel_tab .

data: begin of itab occurs 0,

bldat like bkpf-bldat,

blart like bkpf-blart,

bukrs like bkpf-bukrs,

budat like bkpf-budat,

waers like bkpf-waers,

bschl like bseg-bschl,

hkont like bseg-hkont,

sgtxt like bseg-sgtxt,

end of itab.

data: process_tab_struct_tmp like itab.

field-symbols: <fs1>.

selection-screen: begin of block blk.

parameters: fname type rlgrap-filename.

selection-screen: end of block blk.

at selection-screen on value-request for fname.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = 'ZFI_EXCEL'

DYNPRO_NUMBER = '1000'

FIELD_NAME = 'FNAME'

IMPORTING

FILE_NAME = FNAME.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = FNAME

i_begin_col = 1

i_begin_row = 1

i_end_col = 24

i_end_row = 60000

tables

intern = EXCEL_TAB

LOOP AT EXCEL_TAB.

assign component excel_tab-col of structure

process_tab_struct_tmp to <fs1>.

<fs1> = excel_tab-value.

at end of row.

move-corresponding: process_tab_struct_tmp to itab. “Move values to itab

append itab.

endat.

ENDLOOP.

LOOP AT ITAB.

WRITE:/2 ITAB-BLDAT,14(8) ITAB-BLART,20(10) ITAB-BUDAT.

ENDLOOP.

Former Member
0 Kudos

if you have a .csv file follow this example

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = p_file

filetype = 'DAT'

TABLES

data_tab = tb_file

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 ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

LOOP AT tb_file.

if sy-tabix eq '1'.

continue.

endif.

ADD 1 TO v_count_tot.

SPLIT tb_file-rec AT ';' INTO

ztml_asid07_stoc-werks

ztml_asid07_stoc-lgort

ztml_asid07_stoc-charg

ztml_asid07_stoc-matnr

lv_menge

ztml_asid07_stoc-bstar

ztml_asid07_stoc-umlgo

ztml_asid07_stoc-charg

ztml_asid07_stoc-zzconv

ztml_asid07_stoc-zzelab

ztml_asid07_stoc-zzlog

ztml_asid07_stoc-zzinv

.

REPLACE ',' WITH '.' INTO lv_menge.