topic Re: Excel Upload in Application Development and Automation Discussions

Excel Upload

Private_Member_50145 — Tue, 19 May 2009 08:57:10 GMT

Hello,

I have an excel work book with some data. I need to read this excel and fill the contents to a internal table. I try to use function module GUI_UPLOAD. But the result is very tough to interpret. Is there any other best way to handle this ?

Regards,

Sandeep

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:02:22 GMT

hi,

Use this coding.It will work.

TYPE-POOLS truxs.

TABLES : zecs7.

PARAMETER p_file TYPE rlgrap-filename." DEFAULT 'd:\TEST1.xls'.

TYPES:
  BEGIN OF t_tab,
     ITEM type zecs7-item,
     PERIOD type zecs7-period,
   
  END OF t_tab.

  DATA :
  t_upload  TYPE STANDARD TABLE OF t_tab,
  wa_upload TYPE t_tab,
  it_type   TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
  EXPORTING
*   PROGRAM_NAME  = SYST-CPROG
*   DYNPRO_NUMBER = SYST-DYNNR
    field_name    = 'P_FILE'
  IMPORTING
    file_name     = p_file.

START-OF-SELECTION.

* Uploading the data in the file into internal table
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
  EXPORTING
*   I_FIELD_SEPERATOR =
*   I_LINE_HEADER  = 'X'
    i_tab_raw_data = it_type
    i_filename     = p_file
  TABLES
    i_tab_converted_data = t_upload[]
  EXCEPTIONS
    conversion_failed = 1
    OTHERS            = 2.

* IF sy-subrc NE  0.
*    MESSAGE ID sy-msgid
*            TYPE sy-msgty
*            NUMBER sy-msgno
*            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
*  ENDIF.

END-OF-SELECTION.
* Uploading the data into the database table
  LOOP AT T_UPLOAD INTO WA_UPLOAD.
    zecs7-item = WA_UPLOAD-item.
    zecs7-period = WA_UPLOAD-period.
  
    MODIFY zecs7.
  ENDLOOP.

Regards,

Bathri.

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:02:39 GMT

use Fm ALSM_EXCEL_TO_INTERNAL_TABLE

Re: Excel Upload

former_member229729 — Tue, 19 May 2009 09:16:30 GMT

Hi Sandeep,

Below is the sample code for converting Excel into Internal Table Records:

Data: i_raw      TYPE truxs_t_text_data,
      i_xldata   TYPE STANDARD TABLE OF t_xldata.

SELECTION-SCREEN BEGIN OF BLOCK bk1 WITH FRAME TITLE text-000.
    PARAMETERS:p_fname TYPE ibipparms-path OBLIGATORY.
SELECTION-SCREEN END OF BLOCK bk1.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
  PERFORM f_get_file_name.

Start-of-selection.
   PERFORM f_get_data.
End-of-selection.


FORM f_get_file_name .
  CALL FUNCTION 'F4_FILENAME'
    IMPORTING
      file_name = p_fname.
ENDFORM.                    " F_GET_FILE_NAME

FORM f_get_data .

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
     EXPORTING
*     I_FIELD_SEPERATOR        =
       i_line_header            =  'X'
       i_tab_raw_data           =  i_raw       " WORK TABLE
       i_filename               =  p_fname
     TABLES
       i_tab_converted_data     = i_xldata    "ACTUAL DATA
    EXCEPTIONS
       conversion_failed        = 1
       OTHERS                   = 2.
  IF sy-subrc <> 0.
    WRITE: 'Error in opening the file. Please close the excel file if opened and try again'.
  ENDIF.
ENDFORM.                    " F_GET_DATA

Thanks & regards,

RamaniN

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:21:07 GMT

Hi,

Call this fm

ALSM_EXCEL_TO_INTERNAL_TABLE

Regards,

Jyothi CH.

Re: Excel Upload

Private_Member_50145 — Tue, 19 May 2009 09:25:33 GMT

Hi All,

I do not have the FMs 'TEXT_CONVERT_XLS_TO_SAP' and ALSM_EXCEL_TO_INTERNAL_TABLE in the system I am coding. This is an Application Platform system. I also could not find both these in a BASIS system.

Regards,

Sandeep

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:29:50 GMT

Hi,

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE to upload the Excel file.

Regards,

Raju.

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:34:30 GMT

use this program

DATA: program(255) TYPE c

VALUE 'C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE'.

*check the location of your Excel application

DATA: parameter(255) TYPE c VALUE 'c:\dinesh\sapdata.xls',

retcode TYPE i.

DATA: BEGIN OF i_emp OCCURS 0,

empid LIKE zregtab-empid,

empname LIKE zregtab-empname,

technology LIKE zregtab-technology,

desgination LIKE zregtab-desgination,

mobile LIKE zregtab-mobile,

boardingpoint LIKE zregtab-boardingpoint,

END OF i_emp.

PARAMETERS: p_fname LIKE rlgrap-filename

DEFAULT 'c:\dinesh\sapdata.xls' OBLIGATORY.

SELECT * FROM zregtab INTO CORRESPONDING FIELDS OF TABLE i_emp.

SORT i_emp.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = p_fname

TABLES

i_tab_sap_data = i_emp

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc EQ 0.

WRITE:/ 'Download to Excel complete'.

ELSE.

WRITE:/ 'Error with download'.

ENDIF.

CALL FUNCTION 'GUI_EXEC'

EXPORTING

command = program

parameter = parameter

IMPORTING

returncode = retcode.

IF retcode NE 0.

WRITE:/ 'PROGRAM', program, 'NOT FOUND OR COULD NOT BE STARTED'.

ENDIF.

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:34:35 GMT

Hi,

Try this way:

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                = P_FNAME1
      I_BEGIN_COL             = 1
      I_BEGIN_ROW             = 1
      I_END_COL               = 5
      I_END_ROW               = 9999
    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.

* Sort table by rows and columns
  SORT ITAB1 BY ROW COL.

* Get first row retrieved
  READ TABLE ITAB1 INDEX 1.

  CURR_ROW = ITAB1-ROW.

  CLEAR WA_LDATA.
  LOOP AT ITAB1.

    IF ITAB1-ROW NE CURR_ROW.
      APPEND WA_LDATA TO I_LDATA.
      CLEAR WA_LDATA.
      CURR_ROW = ITAB1-ROW.
    ENDIF.

* Transferring of values to user-defined Internal Table
    CASE ITAB1-COL .
      WHEN C_0001.
        WA_LDATA-ASSOCIATE_NO = ITAB1-VALUE.
      WHEN C_0002.
        WA_LDATA-DATE = ITAB1-VALUE.
      WHEN C_0003.
        WA_LDATA-HOURS = ITAB1-VALUE.
      WHEN C_0004.
        WA_LDATA-WORK_TYPE = ITAB1-VALUE.
      WHEN C_0005.
        WA_LDATA-PAY_RATE = ITAB1-VALUE.
    ENDCASE.

  ENDLOOP.

Get back to me if you face any difficulties

Regards.

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:49:34 GMT

Hi,

Check this code it works fine.

Define the types as for the table. If table has 5 fields define type declaration with all five fields, and ensure that excel should have the same fields as in internal table with mandt field.

TYPE-POOLS truxs.

TYPES:
BEGIN OF type_s_upload,
  <field1> TYPE <table-field1>,
<field2> TYPE <table-field2>, 
END OF type_s_upload.

DATA:
      fs_s TYPE type_s_upload,
      t_upload LIKE STANDARD TABLE OF fs_s.


DATA:
      fs_truxs TYPE truxs_t_text_data.
.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
   EXPORTING
*     I_FIELD_SEPERATOR          =
*    I_LINE_HEADER              =
  i_tab_raw_data             = fs_truxs
 i_filename                 = '<File-path>'
  TABLES
 i_tab_converted_data       = t_upload
*    EXCEPTIONS
*   CONVERSION_FAILED          = 1.
.
SORT t_upload.
DELETE ADJACENT DUPLICATES FROM T_UPLOAD.

LOOP AT t_upload INTO fs_s.
  INSERT <table-name> FROM <field-string>.
ENDLOOP.

.
IF sy-subrc = 0.
  MESSAGE 'SUCCESS' TYPE 'S'.
ELSE.
  MESSAGE 'ERROR' TYPE 'E'.
ENDIF.

Hope this solves the issue.

Regards,

Rajani

Re: Excel Upload

Former Member — Tue, 19 May 2009 09:52:07 GMT

otherwise, use this code

CALL FUNCTION 'WS_UPLOAD'

EXPORTING

FILENAME = FILENAME

FILETYPE = 'DAT'

TABLES

data_tab = ITAB

EXCEPTIONS

CONVERSION_ERROR = 1

FILE_OPEN_ERROR = 2

FILE_READ_ERROR = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

NO_AUTHORITY = 10

OTHERS = 11.

Re: Excel Upload

prince_isaac — Tue, 19 May 2009 10:22:38 GMT

hie

Use the function module to upload from an excel worksheet to an internal table


  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col               = begrow  "Do not require headings
      i_end_col               = '147'
      i_end_row               = endrow
    TABLES
      intern                  = itab
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    MESSAGE e010(ZPRI) WITH text-001. "Problem uploading Excel Spreadsheet
  ENDIF.

regrads

Isaac Prince

Re: Excel Upload

faisalatsap — Tue, 19 May 2009 10:40:22 GMT

Hi, Sandeep

Did you use the following too. for Field Separator for GUI_UPLOAD ?

HAS_FIELD_SEPARATOR           = 'X'

Hope after using this you will solve your problem.

Best Regards,

Faisal

Re: Excel Upload

faisalatsap — Tue, 19 May 2009 10:43:29 GMT

Hi, Anup

Please don't use Function Module WS_UPLOAD because it is obsolete.

Thanks and Regards,

Faisal