How to Upload Excel file to Application Server

[Former Member]

Hi Friends,

ALSM_EXCEL_TO_INTERNAL_TABLE this F.M is to upload the excel format into internal table , The problem here is after uploading the excel file the format has been changed according to the F.M ... so Im not able to compare the value with my final internal table because the structure is different ... even if I could match with the values Im not able to upload it to my application server ... is there any F.M which doesn't change the excel format and upload it to the internal table ? ... Or is there any other way of doing it other than using at new , case endcase. ? ...

Thanks in advance ...

Cheers

[Former Member]

Hi,

Check this code to upload the data from the excel sheet to internal table...after this using OPEN DATASET to upload the data to application server.

DATA l_count TYPE sy-tabix.

   CONSTANTS: lc_begin_col TYPE i VALUE '1',
              lc_begin_row TYPE i VALUE '2',
              lc_end_col   TYPE i VALUE '2',
              lc_end_row   TYPE i VALUE '3000'.

* Begin of CALK912848 - Carlos Werberich - 16Sep08
  CLEAR p_i_excel_data. REFRESH p_i_excel_data.
* End   of CALK912848 - Carlos Werberich - 16Sep08

* Function module to read excel file and convert it into internal table
   CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
     EXPORTING
       filename                = p_p_file
       i_begin_col             = lc_begin_col
       i_begin_row             = lc_begin_row
       i_end_col               = lc_end_col
       i_end_row               = lc_end_row
     TABLES
       intern                  = i_data
     EXCEPTIONS
       inconsistent_parameters = 1
       upload_ole              = 2
       OTHERS                  = 3.
* Error in file upload
   IF sy-subrc NE 0 .
     MESSAGE text-006 TYPE 'E'.
     EXIT.
   ENDIF.
   IF i_data[] IS INITIAL .
     MESSAGE text-007 TYPE 'E'.
     EXIT.
   ELSE.
     SORT i_data BY row col .
* Loop to fill data in Internal Table
     LOOP AT i_data .
       MOVE i_data-col TO l_count .
       ASSIGN COMPONENT l_count OF STRUCTURE p_i_excel_data TO <fs_source> .
       MOVE i_data-value TO <fs_source> .
       AT END OF row .
* Append data into internal table
         APPEND p_i_excel_data.
         CLEAR p_i_excel_data.
       ENDAT .
     ENDLOOP .
   ENDIF .

[Former Member]

Use function TEXT_CONVERT_XLS_TO_SAP to get the same row/column order as in the excel file.

Credit for the below code goes to Jayanta. The below code assumes there is no header line, if there is a header line set i_line_header parameter = 'X'.

REPORT ZTV_EXCEL_TO_TABLE_DOWNLOAD .
*..............................................................
*: Description                                                :
*: -----------                                                :
*: This is a simple example program to get data from an excel :
*: file and store it in an internal table.                    :
*:                                                            :
*: Author : <a href="www.sapdev.co.uk" TARGET="test_blank">www.sapdev.co.uk</a>, based on code from Jayanta      :
*:                                                            :
*: SAP Version : 4.7                                          :
*:............................................................:

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.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR        =
      i_line_header            =  ' '
      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.

[Former Member]

Hi Vishnu,

Could you please tell me is the t_datatab the final internal table ?

TYPES: BEGIN OF t_datatab,

      col1(30)    TYPE c,

      col2(30)    TYPE c,

      col3(30)    TYPE c,

      END OF t_datatab.

[shishupalreddy]

Hi,

Use CG3Z tcode to upload the Local fileto the requird App server file path.

Regards

[P561888]

Hi,

What ever internal table structure you are using for the excel to internal table . it will follow the same structure.

1. check weather the excel stru. and your internal table stru are same.

2. compare the data with the application server.

3. if you want to upload the data to application server use the OPEN dataset and transfer and Close dataset.

Regards,

Bharani

[I355602]

Hi,

Check these links:-

Hope they help you.

Thanks & Regards,

Tarun

[Former Member]

Hi,

One of the FM used for this is 'TEXT_CONVERT_XLS_TO_SAP' which takes the internal table of type

"truxs_t_text_data.",inlcude "truxs" in TYPE-POOLand most importsant declare the internal table where data to be saved from the excel file having same format as the table itself,and data type should be Char type having length saem as defined in the Data Element.

Pooja

[Former Member]

Hi:

Check out this simple code

report  ztn_upload_file_in_db.
tables ztn_record.

data : begin of itab occurs 100,

 row(200) type c,

end of itab.
data : mfilename(128) type c , count type i.

selection-screen begin of block abc with frame title text-001.
parameters fl_name(128) default 'C:\Documents and Settings\tahir.naqqash\Desktop\22.csv'.
  selection-screen end of block abc.

  at selection-screen on value-request for fl_name.
    perform get_file.

start-of-selection.

perform upload_itab.

clear ztn_record.
 loop at itab.
 split itab-row at ',' into ztn_record-mandt
                            ztn_record-empid
                            ztn_record-empname
                            ztn_record-empphno
                            ztn_record-newfield.

 insert ztn_record.

    if sy-subrc = 0 .
      commit work.
      count = count + 1.
    else.
      write: / , 'Insert Failed for',ztn_record-empid ,ztn_record-empname ,ztn_record-empphno , ztn_record-newfield .
    endif.

  endloop.

  write: count , 'Records were inserted'.

[Former Member]

This message was moderated.

[Pranil1]

Hi SK,

Use FM TEXT_CONVERT_XLS_TO_SAP.

For further queries on how to use it pls reply.

Regards,

Pranil Shinde.

[awin_prabhu]

Hi friend,

Simply use GUI_UPLOAD function to get data from excel, txt etc.., into internal table in program.

Try this program.. It performs downloading and uploading functions in both excel and .txt format.

&----

---

*& Report ZAWI_DEMODOWNLOAD *

REPORT zawi_demodownload .

*Types

TYPES: BEGIN OF g_r_mara,

matnr LIKE mara-matnr,

ersda LIKE mara-ersda,

laeda LIKE mara-laeda,

mtart LIKE mara-mtart,

mbrsh LIKE mara-mbrsh,

END OF g_r_mara.

TYPES: BEGIN OF g_r_mara1,

matnr TYPE string,

ersda TYPE string,

laeda TYPE string,

mtart TYPE string,

mbrsh TYPE string,

END OF g_r_mara1.

*Data

DATA: g_t_mara TYPE TABLE OF g_r_mara,

g_t_mara1 TYPE TABLE OF g_r_mara,

g_t_mara2 TYPE TABLE OF g_r_mara1,

filename TYPE string,

f1 TYPE string,

f2 TYPE string,

x TYPE string,

x1 TYPE string,

x2 TYPE string,

x3 TYPE strng,

c TYPE string,

g_r_wa TYPE g_r_mara,

g_r_wa1 TYPE g_r_mara1,

g_r_wa2 TYPE g_r_mara1,

• g_t_mara2 TYPE TABLE OF g_r_mara1,

str TYPE string.

*Tables

TABLES: mara.

*Selection Screen

SELECT-OPTIONS: s_matnr FOR mara-matnr.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 10(20) text-001 FOR FIELD p1.

PARAMETERS:p1 RADIOBUTTON GROUP g1.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 10(20) text-002 FOR FIELD p2.

PARAMETERS p2 RADIOBUTTON GROUP g1.

SELECTION-SCREEN END OF LINE.

*Input validation.

DATA: s_high TYPE mara-matnr,

s_low TYPE mara-matnr.

AT SELECTION-SCREEN ON s_matnr.

IF NOT s_matnr-high IS INITIAL.

s_high = s_matnr-high.

SELECT SINGLE * FROM mara WHERE matnr = s_high.

IF sy-subrc <> 0.

• IF NOT s_matnr-low IS INITIAL.

s_low = s_matnr-low.

SELECT SINGLE * FROM mara WHERE matnr = s_low.

IF sy-subrc <> 0.

MESSAGE e012(zawi_demo).

ELSE.

MESSAGE e011(zawi_demo).

ENDIF.

ENDIF.

ENDIF.

AT SELECTION-SCREEN.

SELECT SINGLE * FROM mara WHERE matnr IN s_matnr.

IF sy-subrc <> 0.

IF s_matnr-low IS INITIAL.

str = s_matnr-high.

ELSE.

str = s_matnr-low.

ENDIF.

MESSAGE e010(zawi_demo) WITH str..

ENDIF.

START-OF-SELECTION.

*Data retrival

SELECT matnr ersda laeda mtart mbrsh

INTO CORRESPONDING FIELDS OF TABLE g_t_mara

FROM mara

WHERE matnr IN s_matnr.

IF p1 = 'X'.

filename = 'C:\Testing.xls'.

• Downloading data from internal table to excel or txt

f1 = filename.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

• append = 'X'

write_field_separator = 'X'

• col_select = 'X'

TABLES

data_tab = g_t_mara.

ELSE.

filename = 'C:\Testing.txt'.

f2 = filename.

LOOP AT g_t_mara INTO g_r_wa.

CONCATENATE g_r_wa-matnr ';' INTO g_r_wa1-matnr.

CONCATENATE g_r_wa-ersda ';' INTO g_r_wa1-ersda.

CONCATENATE g_r_wa-laeda ';' INTO g_r_wa1-laeda.

CONCATENATE g_r_wa-mtart ';' INTO g_r_wa1-mtart.

CONCATENATE g_r_wa-mbrsh ';' INTO g_r_wa1-mbrsh.

APPEND g_r_wa1 TO g_t_mara2.

ENDLOOP.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

• append = 'X'

• write_field_separator = 'X'

• col_select = 'X'

TABLES

data_tab = g_t_mara2.

ENDIF.

• Uploading data from excel to internal table 1

IF filename = f1.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

has_field_separator = 'X'

TABLES

data_tab = g_t_mara1.

WRITE: / 'Uploaded data' COLOR = 1.

WRITE:/.

CLEAR g_r_wa.

LOOP AT g_t_mara1 INTO g_r_wa.

WRITE:/ g_r_wa-matnr, g_r_wa-ersda, g_r_wa-laeda, g_r_wa-mtart, g_r_wa-mbrsh.

ENDLOOP.

ELSE.

IF filename = f2.

IF sy-subrc <> 0.

ENDIF.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

has_field_separator = 'X'

replacement = ''

TABLES

data_tab = g_t_mara2.

WRITE: / 'Uploaded data' COLOR = 1.

WRITE:/.

CLEAR g_r_wa1.

LOOP AT g_t_mara2 INTO g_r_wa1.

g_r_wa2-matnr = g_r_wa1-matnr.

TRANSLATE g_r_wa2-matnr USING '; ' .

WRITE:/ g_r_wa2-matnr, g_r_wa2-ersda, g_r_wa2-laeda, g_r_wa2-mtart, g_r_wa2-mbrsh.

CLEAR g_r_wa2.

ENDLOOP.

ENDIF.

ENDIF.

OR

Use T-codes:

CG3Y - Download file - Download file from Application server

CG3Z - Upload file - Upload file to Application server

[Former Member]

Thank u so much friends.