Upload Excel data from Openoffice Calc to SAP

[former_member210857]

Hi ,

My client is using Openoffice Calc >My requirement is to upload the data from openoffice calc to SAP. I have tried with all the function modules available to upload .Is there any way to upload data from openoffice .

I have searched in forum ,Couldnt find any correct answer .

Regards,

Subeesh Kannottil

[former_member197696]

Hi Subheesh,

Good morning.

Just go through the code and check if it helps you.

Processing logic for Uploading Excel File

1. Fetch file name from presentation server using FM F4_FILENAME.

                             CALL FUNCTION 'F4_FILENAME'                                           EXPORTING                                    field_name = 'P_FILE'                                           IMPORTING                                    file_name  = p_file.

2. Upload file from presentation server using path fetched earlier using FM               ALSM_EXCEL_TO_INTERNAL_TABLE .

            It has the following parameters-

Export:

            i_begin_col     -   Begin column of excel

            i_begin_row    -   Begin row of excel

            i_end_col        -   End column in excel

            i_end_row       -   End Row in excel

                         Import:

            intern              - Data collected in an internal table after upload.

            Declare internal table in which data from excel is uploaded.

        FIELD-SYMBOLS: <gfs>.   

        Data: gt_exceldata           TYPE  alsmex_tabline OCCURS 0 WITH HEADER LINE.

            Declare another internal table to store values in row/column format.

          Data: gt_final type table of gty_final,

                    gwa_final type gty_final.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

           EXPORTING

            filename                = p_file

            i_begin_col             = 1

            i_begin_row             = 2

            i_end_col               = 250

            i_end_row               = 9999

            TABLES

                                    intern                  = gt_intern

Data fetched in an internal table will in column format and we need to change it to row.

2.1 For example: Data in the excel sheet

                        

2.2 After using ALSM_EXCEL_TO_INTERNAL_TABLE data is stored in internal table as shown below

                                    row  col             value

                                     1      0001         00000086

                                     1      0002         20131121

                                     1      0003         01

                                     1      0004         02

In Debug mode you will find the data as shown below.

                             

2.2.1 Change the format in itab to excel format .

Loop at internal table,Now the data is collected in header.

                      LOOP AT gt_intern.

Work area/Header contains the following data.

                             

2.2.2 Header contains N number of fields so that we assign each field to a field symbol at a time using assign component .

It assigns each column component of the internal table to a field symbol.

            ASSIGN COMPONENT gt_intern-col OF STRUCTURE gwa_pa40  TO <gfs>. 

now component 0001 of structure gwa_pa40 is PERNR.This is assigned to field symbol <GFS>

Similarly, in second loop component 0002 of structure is BEGDA.

                in third     loop  component 0003 of structure is MASSN.

                in fourth  loop   component 0004 of structure is MASSG.

2.2.3 Move the value component of the internal table to field symbol.   

                      MOVE gt_intern-value TO <gfs>.

                      value 86 is moved to field symbol <GFS>.

Now, the work area/header  looks like this after the first loop.

                         

Second loop.

                         

2.2.4 Append work area to internal table at end of row.

                      APPEND gwa_pa40 TO gt_pa40.

After appending the table has one row for one row in excel sheet.

                        

CODE:

FIELD-SYMBOLS: <gfs>.  

TYPES : BEGIN    OF gty_final,

         pernr     TYPE persno,

         begda    TYPE begda,

         massn   TYPE massn,

         massg   TYPE massg,

         END OF  gty_final.

Data: gt_exceldata           type  alsmex_tabline OCCURS 0 WITH HEADER LINE,

           gt_final                    type  table of gty_final,

           gwa_final                type gty_final.

SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TEXT-012.

PARAMETERS:p_file TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN: end OF BLOCK b2.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  TYPES: gt_t_pa40 TYPE pa0001.

  CALL FUNCTION 'F4_FILENAME'

    EXPORTING

      field_name = 'P_FILE'

    IMPORTING

      file_name  = p_file.

START-OF-SELECTION.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

    EXPORTING

      filename                            = p_file

      i_begin_col                       = 1

      i_begin_row                      = 2

      i_end_col                          = 250

      i_end_row                         = 9999

    TABLES

      intern                                 = gt_exceldata

    EXCEPTIONS

      inconsistent_parameters  = 1

      upload_ole                        = 2

      OTHERS                          = 3.

LOOP AT gt_exceldata .

    ASSIGN COMPONENT gt_exceldata -col OF STRUCTURE gwa_final   TO <gfs>.

    MOVE gt_exceldata -value TO <gfs>.

    AT END OF row.

      APPEND gwa_final TOgt_final .

    ENDAT.

ENDLOOP.

CLEAR gwa_final .

OUTPUT

                           

Try the above process and let me know your comments.

SSK

[Former Member]

Hi,

export data from OpenOffice to any kind of CSV file and import it using CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD as text, then parse it, e.g. by function TEXT_CONVERT_TEX_TO_SAP..

If you need to do it without exporting, there is an open source tool to read .ods directly but unfortunately I cannot find right link to it now.

Regards,

  Pavel