topic Re: Problem with upload excel from PC to internal table in Application Development and Automation Discussions

Problem with upload excel from PC to internal table

Former Member — Thu, 27 Sep 2007 07:25:40 GMT

Hello Experts,

I need to upload excel file from presentation server to an internal table.

I have used FM GUI_UPLOAD...by that I get all the the data in # only...I am not able to read file with data in correct format.

Could you please help me out?

I this this FM is not right one. then plz let me know the way to read excel file fron PC to internal table.

Thanks in advance for quick reply

Anutosh

Re: Problem with upload excel from PC to internal table

gopi_narendra — Thu, 27 Sep 2007 07:28:06 GMT

You can use the fm: ALSM_EXCEL_TO_INTERNAL_TABLE to upload data from excel to internal table.

Check this sample code of how to use it


data : it_data type table of alsmex_tabline initial size 0,
       is_data type alsmex_tabline.

* Flatfile internal table.
types : begin of ty_tab,
          kunnr type bsid-kunnr, " added on 31-07-2007
          zuonr type bsid-zuonr,
        end of ty_tab.

data : it_tab type table of ty_tab initial size 0,
       is_tab type ty_tab.

* If Input file name is not initial.
  if not p_ifname is initial.
* Upload EXCEL data into internal table
    call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
         exporting
              filename                = p_ifname
              i_begin_col             = 1
              i_begin_row             = 1
              i_end_col               = 256
              i_end_row               = 65356
         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.
  endif.

* Append EXCEL Data into a internal table
  loop at it_data into is_data.
    at new row.
      clear is_tab.
    endat.
    if is_data-col = '001'.
      move is_data-value to is_tab-kunnr.
    endif.
    if is_data-col = '002'.
      move is_data-value to is_tab-zuonr.
    endif.
    at end of row.
      append is_tab to it_tab.
    endat.
    clear : is_data.
  endloop.

Regards

Gopi

Re: Problem with upload excel from PC to internal table

Former Member — Thu, 27 Sep 2007 07:31:31 GMT

Hi,

For uploading data from excel file to internal table you need to use the folllwing func. module.:

ALSM_EXCEL_TO_INTERNAL_TABLE

If you are able to upload the data & it is coming n '#' then you can try by setting follwing in Import parameter also in GU_uplaod:

HAS_FIELD_SEPARATOR = 'X'.

Reward ponts if helpful.

Ashvender

Re: Problem with upload excel from PC to internal table

Former Member — Thu, 27 Sep 2007 09:15:20 GMT

Dear Saxena,

Please go though the following lines of code:

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

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

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

TABLES: ANEP,

BKPF.

TYPES: BEGIN OF TY_TABDATA,

MANDT LIKE SY-MANDT, " Client

ZSLNUM LIKE ZSHIFTDEPN-ZSLNUM, " Serial Number

ZASSET LIKE ZSHIFTDEPN-ZASSET, " Original asset that was transferred

ZYEAR LIKE ZSHIFTDEPN-ZYEAR, " Fiscal Year

ZPERIOD LIKE ZSHIFTDEPN-ZPERIOD, " Fiscal Period

ZSHIFT1 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 1

ZSHIFT2 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 2

ZSHIFT3 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 3

END OF TY_TABDATA.

*----

Declaration of the Internal Table with Header Line comprising of the uploaded data.

*----

DATA: BEGIN OF IT_FILE_UPLOAD OCCURS 0.

INCLUDE STRUCTURE ALSMEX_TABLINE. " Rows for Table with Excel Data

DATA: END OF IT_FILE_UPLOAD.

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

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

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

SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME,

BEGIN OF BLOCK B2 WITH FRAME.

PARAMETERS: P_FNAME LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN: END OF BLOCK B2,

END OF BLOCK B1.

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

E V E N T : AT S E L E C T I O N - S C R E E N *

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FNAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

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

STATIC = 'X'

MASK = '.'

CHANGING

FILE_NAME = P_FNAME

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.

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

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

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

START-OF-SELECTION.

--------------------------------------
Upload Excel file into Internal Table.
--------------------------------------

PERFORM UPLOAD_EXCEL_FILE.

-------------------------------------------------------
Organize the uploaded data into another Internal Table.
-------------------------------------------------------

PERFORM ORGANIZE_UPLOADED_DATA.

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

E V E N T : E N D - O F - S E L E C T I O N *

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

END-OF-SELECTION.

&----

*& Form UPLOAD_EXCEL_FILE

&----

text

----

--> p1 text
<-- p2 text

----

FORM UPLOAD_EXCEL_FILE .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FNAME

I_BEGIN_COL = 1

I_BEGIN_ROW = 3

I_END_COL = 7

I_END_ROW = 32000

TABLES

INTERN = IT_FILE_UPLOAD

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_EXCEL_FILE

&----

*& Form ORGANIZE_UPLOADED_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM ORGANIZE_UPLOADED_DATA .

SORT IT_FILE_UPLOAD BY ROW

COL.

LOOP AT IT_FILE_UPLOAD.

CASE IT_FILE_UPLOAD-COL.

....................................................

WHEN 1.

WA_TABDATA-ZSLNUM = IT_FILE_UPLOAD-VALUE.

WHEN 2.

WA_TABDATA-ZASSET = IT_FILE_UPLOAD-VALUE.

WHEN 3.

WA_TABDATA-ZYEAR = IT_FILE_UPLOAD-VALUE.

WHEN 4.

WA_TABDATA-ZPERIOD = IT_FILE_UPLOAD-VALUE.

WHEN 5.

WA_TABDATA-ZSHIFT1 = IT_FILE_UPLOAD-VALUE.

WHEN 6.

WA_TABDATA-ZSHIFT2 = IT_FILE_UPLOAD-VALUE.

WHEN 7.

WA_TABDATA-ZSHIFT3 = IT_FILE_UPLOAD-VALUE.

....................................................

ENDCASE.

AT END OF ROW.

WA_TABDATA-MANDT = SY-MANDT.

APPEND WA_TABDATA TO IT_TABDATA.

CLEAR: WA_TABDATA.

ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA

In the subroutine --> ORGANIZE_UPLOADED_DATA, data are organized as per the structure declared above.

Regards,

Abir

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

Don't forget to award points *

Re: Problem with upload excel from PC to internal table

varma_narayana — Thu, 27 Sep 2007 09:31:01 GMT

Hi Anutosh..

This may be bcoz the File has TAB delimiter between the fields.

in that case while calling the GUI_UPLOAD set the parameter

HAS_FIELD_SEPARATOR = 'X'.

Then it will upload properly.

<b>reward if Helpful.</b>