topic Re: Excel Upload in Application Development and Automation Discussions

Excel Upload

Former Member — Tue, 27 Nov 2007 09:06:35 GMT

What are all the Function Modules available to upload the data from the excel sheet into the SAP System.

Please help.

Re: Excel Upload

former_member386202 — Tue, 27 Nov 2007 09:08:30 GMT

Hi,

USe FM ALSM_EXCEL_TO_INTERNAL_TABLE

Regards,

Prashant

Re: Excel Upload

former_member188829 — Tue, 27 Nov 2007 09:09:36 GMT

Hi,

The Function Module Used For Uploading the data from Excal to Internal Table is

ALSM_EXCEl_TO_INTERNAL_TABLE

Or You Can Use..

GUI_UPLOAD

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:10:07 GMT

U can use 'GUI_UPLOAD' or 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

Thanks

Vasudha

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:10:12 GMT

Hi Supriya,

this FM will help u for processing the XL file ALSM_EXCEL_TO_INTERNAL_TABLE

Regards.

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:10:18 GMT

if your excel is a CSV file, you could also use function module GUI_UPLOAD (or alternatively the method in cl_gui_frontend_services class).

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:10:57 GMT

You can use the ALSM_EXCEL_TO_INTERNAL_TABLE function module

Here is the example program

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:12:25 GMT

how will I know if the excel is a CSV file Priyank?

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:13:54 GMT

<b>'ALSM_EXCEL_TO_INTERNAL_TABLE'</b>FI-AA Legacy Data Transfer w/ Excel

REPORT ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,

f1(20),

f2(40),

f3(20),

end of it_tab1.

data : it_tab type table of ALSMEX_TABLINE with header line,

file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,

wa_tab2 type it_tab1,

w_message(100) TYPE c.

at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

STATIC = 'X'

MASK = ' '

CHANGING

file_name = file_nm

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.

start-of-selection.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '10'

i_end_row = '35'

tables

intern = it_tab

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.

loop at it_tab.

case it_tab-col.

when '002'.

wa_tab2-f1 = it_tab-value.

when '004'.

wa_tab2-f2 = it_tab-value.

when '008'.

wa_tab2-f3 = it_tab-value.

endcase.

at end of row.

append wa_tab2 to it_tab2.

clear wa_tab2.

endat.

endloop.

data : p_file TYPE rlgrap-filename value 'TEST3.txt'.

OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

*--- Display error messages if any.

IF sy-subrc NE 0.

MESSAGE e001(zsd_mes).

EXIT.

ELSE.

*---Data is downloaded to the application server file path

LOOP AT it_tab2 INTO wa_tab2.

TRANSFER wa_tab2 TO p_file.

ENDLOOP.

ENDIF.

*--Close the Application server file (Mandatory).

CLOSE DATASET p_file.

loop at it_tab2 into wa_tab2.

write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

endloop.

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 09:29:00 GMT

Hi Supriya,

This is kiran.G ( working in SAP).Better to use this function module to upload xls file into SAP.i will develop a small code for u.

FUNCTION MODULE : F4_FILENAME.

ACTUALLY I WILL DEVELOP SOME PROGRAMS.I WILL SEND A SMALL PRPGRAM ALONG WITH FLAT FILE.I WILL ATTACH FLATFILE CONTENT IN THE BELOW OF THE PROGRAM.PLZ FILL THAT DATA IN FLATFILE OK.

NOTE: WE HAVE TO SAVE THE FILE AS .XLS WITH TAB DELIMETED OPTION(AT THE TIME U HAVE TO SAVE THE XLS FILE THERE IS ANOTHER BOX PRESENT BELOW THE NAME U GIVEN IN THE SAVE BOX.FROM THAT U HAVE TO CHOOSE TAB DELIMITED OPTION)

IF U R SATISFY WITH THE CODE PLZ GIVE ME REWARD POINTS.

code:

----

Tables

----

TABLES : mara.

----

Global BDCDATA Structure

----

DATA: it_bdcdata LIKE bdcdata OCCURS 0 WITH HEADER LINE,

it_msgtab LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE.

----

Global Variables

----

DATA: gl_infile TYPE string,

gv_msg TYPE string,

v_update VALUE 'A'.

----

Internal Table

*----

DATA: BEGIN OF g_itab OCCURS 0,

matnr(20), "Material Number

mbrsh(20), "Account Group

mtart(20), "Material Type

maktx(20), "Material Description

meins(20), "Base Unit Of Measure

END OF g_itab.

----

Selection-screen

----

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

PARAMETERS: p_file LIKE rlgrap-filename,

p_mode.

SELECTION-SCREEN : END OF BLOCK b1.

----

Fetch A File

----

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

PERFORM fetch_file.

START-OF-SELECTION.

----

Fetch Data From XLS File

----

PERFORM fetch_data.

----

Fetch Data From XLS File

----

PERFORM place_data.

&----

*& Form fetch_file

&----

text

----

--> p1 text
<-- p2 text

----

FORM fetch_file .

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = syst-cprog

dynpro_number = syst-dynnr

FIELD_NAME = ' '

IMPORTING

file_name = p_file

gl_infile = p_file.

ENDFORM. " fetch_file

&----

*& Form fetch_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM fetch_data .

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = gl_infile

filetype = 'ASC'

has_field_separator = 'X'

HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
IMPORTING
FILELENGTH =
HEADER =

TABLES

data_tab = g_itab

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.

ENDFORM. " fetch_data

&----

*& Form place_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM place_data .

LOOP AT g_itab.

perform bdc_dynpro using 'SAPLMGMM' '0060'.

perform bdc_field using 'BDC_CURSOR'

'RMMG1-MATNR'.

perform bdc_field using 'BDC_OKCODE'

'=AUSW'.

perform bdc_field using 'RMMG1-MATNR'

g_itab-matnr."record-MATNR_001.

perform bdc_dynpro using 'SAPLMGMM' '0070'.

perform bdc_field using 'BDC_CURSOR'

'MSICHTAUSW-DYTXT(01)'.

perform bdc_field using 'BDC_OKCODE'

'=ENTR'.

perform bdc_field using 'MSICHTAUSW-KZSEL(01)'

'X'."record-KZSEL_01_002.

perform bdc_dynpro using 'SAPLMGMM' '4004'.

perform bdc_field using 'BDC_OKCODE'

'=BU'.

perform bdc_field using 'BDC_CURSOR'

'MAKT-MAKTX'.

perform bdc_field using 'MAKT-MAKTX'

g_itab-maktx. "record-MAKTX_003.

perform bdc_field using 'MARA-MEINS'
g_itab-meins.record-MEINS_004.

perform bdc_field using 'MARA-MTPOS_MARA'

'NORM'."record-MTPOS_MARA_005.

CALL TRANSACTION 'MM02' USING it_bdcdata MODE p_mode

update v_update

MESSAGES INTO it_msgtab.

LOOP AT it_msgtab.

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

id = sy-msgid

lang = sy-langu

no = sy-msgno

v1 = sy-msgv1

v2 = sy-msgv2

v3 = sy-msgv3

v4 = sy-msgv4

IMPORTING

msg = gv_msg

EXCEPTIONS

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

IF sy-tabix = 1.

WRITE: / 'Process Log' COLOR 3.

ENDIF.

WRITE:/ gv_msg.

ENDLOOP.

REFRESH it_msgtab.

REFRESH it_bdcdata.

ENDLOOP.

ENDFORM. " place_data

----

Start new screen *

----

FORM bdc_dynpro USING program dynpro.

CLEAR it_bdcdata.

it_bdcdata-program = program.

it_bdcdata-dynpro = dynpro.

it_bdcdata-dynbegin = 'X'.

APPEND it_bdcdata.

ENDFORM. "BDC_DYNPRO

----

Insert field *

----

FORM bdc_field USING fnam fval.

IF FVAL <> NODATA.

CLEAR it_bdcdata.

it_bdcdata-fnam = fnam.

it_bdcdata-fval = fval.

APPEND it_bdcdata.

ENDIF.

ENDFORM. "BDC_FIELD

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

FLAT FILE

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

M FERT STEEL KG

M ROH IRON KG

Re: Excel Upload

Former Member — Tue, 27 Nov 2007 10:16:12 GMT

Hi,

Use this code

&----

*& Report ZE0232_EXCEL_SHEET *

*& *

&----

*& *

&----

REPORT ZE0232_EXCEL_SHEET LINE-SIZE 700.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA : B1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

B2 TYPE I VALUE 100,

C2 TYPE I VALUE 9999.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\Documents and Settings\uday\Desktop\Excel files\EXCEL_TRAIN.XLS'

I_BEGIN_COL = B1

I_BEGIN_ROW = C1

I_END_COL = B2

I_END_ROW = C2

TABLES

INTERN = itab1

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.

loop at itab1.

ON CHANGE OF ITAB1-ROW.

WRITE: /.

ENDON.

write:itab1-VALUE.

Endloop.

IF HELPFULL REWARD