topic Re: Problems with uploading data from Excel sheet in Application Development and Automation Discussions

Problems with uploading data from Excel sheet

Former Member — Mon, 20 Nov 2006 07:40:41 GMT

Hi,

I'm using function module ALSM_EXCEL_TO_INTERNAL_TABLE to upload data from an excel sheet.

I'm giving the value 100 to 'end column' as well as 'end row'. But its reading only first 7 rows. The output table has just 10 entries from these 7 rows. Could you please advice on this?

Also, does the excel sheet needs to be in open state for this FM to upload the data?

Thanks

Re: Problems with uploading data from Excel sheet

Former Member — Mon, 20 Nov 2006 07:41:45 GMT

hi,

Check

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

Regards,

Santosh

Re: Problems with uploading data from Excel sheet

Former Member — Mon, 20 Nov 2006 10:52:23 GMT

hi munish how r u? see this code munish u will get one idea how to download data into excel sheet...........

REPORT zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_datatab,

col1(30) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

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

*START-OF-SELECTION.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "ACTUAL DATA

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.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP.

plz review the points if it is usefull.......

Re: Problems with uploading data from Excel sheet

anversha_s — Mon, 20 Nov 2006 10:57:32 GMT

Hi,

Also, check the following links:

<u>http://www.sapdevelopment.co.uk/file/file_upexcel.htm

http://www.sapdevelopment.co.uk/file/file_upexcelalt1.htm

http://www.sapdevelopment.co.uk/file/file_upexcelalt2.htm</u>;

Regards

Anver

Re: Problems with uploading data from Excel sheet

Former Member — Mon, 20 Nov 2006 10:59:34 GMT

hi,

this program of mine is working fine. Check it .

REPORT Z_CUSTOMER_UPLOAD .

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

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

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

DATA:BEGIN OF IT_CUSTOMER OCCURS 0,

KUNNR LIKE MV10A-KUNNR,

VKORG LIKE MV10A-VKORG,

VTWEG LIKE MV10A-VTWEG,

MATNR LIKE MV10A-MATNR,

KDMAT LIKE MV10A-KDMAT,

MEGRU LIKE MV10A-MEGRU,

SELKZ TYPE C value 'X',

LPRIO LIKE MV10A-LPRIO,

ANTLF LIKE MV10A-ANTLF,

END OF IT_CUSTOMER.

DATA:BEGIN OF IT_success OCCURS 0,

KUNNR LIKE MV10A-KUNNR,

VKORG LIKE MV10A-VKORG,

VTWEG LIKE MV10A-VTWEG,

MATNR LIKE MV10A-MATNR,

KDMAT LIKE MV10A-KDMAT,

MEGRU LIKE MV10A-MEGRU,

SELKZ TYPE C value 'X',

LPRIO LIKE MV10A-LPRIO,

ANTLF LIKE MV10A-ANTLF,

END OF IT_success.

DATA:BEGIN OF IT_error OCCURS 0,

KUNNR LIKE MV10A-KUNNR,

VKORG LIKE MV10A-VKORG,

VTWEG LIKE MV10A-VTWEG,

MATNR LIKE MV10A-MATNR,

KDMAT LIKE MV10A-KDMAT,

MEGRU LIKE MV10A-MEGRU,

SELKZ TYPE C value 'X',

LPRIO LIKE MV10A-LPRIO,

ANTLF LIKE MV10A-ANTLF,

END OF IT_error.

DATA: L_INDEX TYPE SY-TABIX.

DATA:IT_BDC LIKE BDCDATA OCCURS 0 WITH HEADER LINE,

IT_DATA TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

ERROR MESSAGE TABLE

DATA:IT_MESSAGES LIKE BDCMSGCOLL OCCURS 0 WITH HEADER LINE.

DATA : C_S TYPE C VALUE 'S',

C_E TYPE C VALUE 'E'.

*DATA: IT_SUCCESS LIKE IT_CUSTOMER OCCURS 0,

IT_ERROR LIKE IT_CUSTOMER OCCURS 0.

DATA : V_RECTOT TYPE I,

V_RECERR TYPE I,

V_RECSUC TYPE I.

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

S E L E C T I O N - S C R E E N *

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

SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

PARAMETER : P_FILE LIKE RLGRAP-FILENAME .

SELECTION-SCREEN : END OF BLOCK B1.

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

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

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

to get F4 help for p_file

PERFORM F4_FILENAME USING P_FILE.

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

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

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

START-OF-SELECTION.

Uploading data from flat file into it_tab

PERFORM BDC_UPLOAD USING P_FILE.

PERFORM PROCESS_DATA.

PERFORM POPULATE_BDC.

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

E N D O F S E L E C T I O N *

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

*END-OF-SELECTION.

PERFORM DISPLAY_REPORT.

&----

*& Form F4_FILENAME

&----

text

----

-->P_P_FILE text

----

FORM F4_FILENAME USING P_P_FILE.

DATA:L_FILE TYPE IBIPPARMS-PATH.

CALL FUNCTION 'F4_FILENAME'

EXPORTING
PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

IMPORTING

FILE_NAME = L_FILE .

P_P_FILE = L_FILE.

ENDFORM. " F4_FILENAME

&----

*& Form BDC_UPLOAD

&----

text

----

-->P_P_FILE text

----

FORM BDC_UPLOAD USING P_P_FILE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_P_FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 8

I_END_ROW = 1000

TABLES

INTERN = IT_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. " BDC_UPLOAD

&----

*& Form PROCESS_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM PROCESS_DATA.

SORT IT_DATA BY ROW COL.

LOOP AT IT_DATA.

CASE IT_DATA-COL.

WHEN 1.

IT_CUSTOMER-KUNNR = IT_DATA-VALUE.

WHEN 2.

IT_CUSTOMER-VKORG = IT_DATA-VALUE.

WHEN 3.

IT_CUSTOMER-VTWEG = IT_DATA-VALUE.

WHEN 4.

IT_CUSTOMER-MATNR = IT_DATA-VALUE.

WHEN 5.

IT_CUSTOMER-KDMAT = IT_DATA-VALUE.

WHEN 6.

IT_CUSTOMER-MEGRU = IT_DATA-VALUE.

WHEN 7.

IT_CUSTOMER-LPRIO = IT_DATA-VALUE.

WHEN 8.

IT_CUSTOMER-ANTLF = IT_DATA-VALUE.

APPEND IT_CUSTOMER.

ENDCASE.

ENDLOOP.

ENDFORM. " PROCESS_DATA

&----

*& Form POPULATE_BDC

&----

text

----

--> p1 text
<-- p2 text

----

FORM POPULATE_BDC.

DATA:L_COUNTER TYPE N,

L_STRING TYPE STRING.

LOOP AT IT_CUSTOMER.

AT NEW KUNNR.

CLEAR L_COUNTER.

L_INDEX = SY-TABIX.

READ TABLE IT_CUSTOMER INDEX L_INDEX.

perform bdc_dynpro using 'SAPMV10A' '0100'.

perform bdc_field using 'MV10A-KUNNR'

IT_CUSTOMER-KUNNR.

perform bdc_field using 'MV10A-VKORG'

IT_CUSTOMER-VKORG.

perform bdc_field using 'MV10A-VTWEG'

IT_CUSTOMER-VTWEG.

perform bdc_field using 'BDC_OKCODE'

'/00'.

ENDAT.

L_COUNTER = L_COUNTER + 1.

CLEAR L_STRING.

perform bdc_dynpro using 'SAPMV10A' '0200'.

CONCATENATE 'MV10A-MATNR(' L_COUNTER ')' INTO L_STRING.

perform bdc_field using L_STRING

IT_CUSTOMER-MATNR.

CONCATENATE 'MV10A-KDMAT(' L_COUNTER ')' INTO L_STRING.

perform bdc_field using L_STRING

IT_CUSTOMER-KDMAT.

CONCATENATE 'MV10A-MEGRU(' L_COUNTER ')' INTO L_STRING.

perform bdc_field using L_STRING

IT_CUSTOMER-MEGRU.

perform bdc_field using 'BDC_OKCODE'

'/00'.

*-- For Page down in Call Transaction Mode

IF L_COUNTER = 14.

CLEAR L_COUNTER.

PERFORM BDC_DYNPRO USING 'SAPMV45A' '4001'.

PERFORM BDC_FIELD USING 'BDC_OKCODE'

'=P+'.

ENDIF.

perform bdc_dynpro using 'SAPMV10A' '0200'.

CONCATENATE 'MV10A-SELKZ(' L_COUNTER ')' INTO L_STRING.

perform bdc_field using L_STRING

IT_CUSTOMER-SELKZ.

perform bdc_field using 'BDC_OKCODE'

'=SELE'.

perform bdc_dynpro using 'SAPMV10A' '0300'.

perform bdc_field using 'MV10A-KDMAT'

IT_CUSTOMER-KDMAT.

perform bdc_field using 'MV10A-LPRIO'

IT_CUSTOMER-LPRIO.

perform bdc_field using 'MV10A-ANTLF'

IT_CUSTOMER-ANTLF.

PERFORM BDC_FIELD USING 'BDC_OKCODE'

'/EBACK'.

at end of kunnr.

READ TABLE IT_CUSTOMER INDEX L_INDEX.

perform bdc_field using 'BDC_OKCODE'

'=SICH'.

CALL TRANSACTION 'VD51' USING IT_BDC MODE 'A' UPDATE 'S'

MESSAGES INTO IT_MESSAGES.

CLEAR IT_BDC.

REFRESH IT_BDC.

ENDAT.

IF NOT IT_MESSAGES[] IS INITIAL.

PERFORM FORMAT_MESSAGE.

ENDIF.

ENDLOOP.

ENDFORM. " POPULATE_BDC

&----

*& Form bdc_dynpro

&----

text

----

-->P_0273 text
-->P_0274 text

----

FORM bdc_dynpro USING VALUE(P_0273)

VALUE(P_0274).

IT_BDC-PROGRAM = P_0273.

IT_BDC-DYNPRO = P_0274.

IT_BDC-DYNBEGIN = 'X'.

APPEND IT_BDC.

CLEAR IT_BDC.

ENDFORM. " bdc_dynpro

&----

*& Form bdc_field

&----

text

----

-->P_0278 text
-->P_RECORD_KUNNR_001 text

----

FORM bdc_field USING VALUE(P_0278)

VALUE(P_0279).

IT_BDC-FNAM = P_0278.

IT_BDC-FVAL = P_0279.

APPEND IT_BDC.

CLEAR IT_BDC.

ENDFORM. " bdc_field

&----

*& Form FORMAT_MESSAGE

&----

text

----

--> p1 text
<-- p2 text

----

FORM FORMAT_MESSAGE.

DATA: L_MSG(100).

LOOP AT IT_MESSAGES.

READ TABLE IT_CUSTOMER INDEX L_INDEX.

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

ID = IT_MESSAGES-MSGID

LANG = SY-LANGU

NO = IT_MESSAGES-MSGNR

V1 = IT_MESSAGES-MSGV1

V2 = IT_MESSAGES-MSGV2

V3 = IT_MESSAGES-MSGV3

V4 = IT_MESSAGES-MSGV4

IMPORTING

MSG = L_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.

write:/ l_msg.

ENDLOOP.

ENDFORM. " FORMAT_MESSAGE

regards,

keerthi

Re: Problems with uploading data from Excel sheet

Former Member — Fri, 30 Nov 2007 10:05:55 GMT

There was some problem with the formatting in the Excel sheet.

Thanks