GUI_UPLOAD for excel?

[Former Member]

Experts:

How to upload excel file directly into an internal table using GUI_UPLOAD without converting the excel file into a tab delimited file?

Thanks,

UV

[Former Member]

hope with this u can achieve wat ur exactly looking for

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 u2018F4_FILENAMEu2019

EXPORTING

field_name = u2018P_FILEu2019

IMPORTING

file_name = p_file.

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

*START-OF-SELECTION.

START-OF-SELECTION.

CALL FUNCTION u2018TEXT_CONVERT_XLS_TO_SAPu2019

EXPORTING

• I_FIELD_SEPERATOR =

i_line_header = u2018Xu2019

i_tab_raw_data = it_raw u201D WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] u201CACTUAL 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.

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

&u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014

*& Report UPLOAD_EXCEL *

*& *

&u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014

*& *

*& Upload and excel file into an internal table using the following *

*& function module: ALSM_EXCEL_TO_INTERNAL_TABLE *

&u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014

REPORT UPLOAD_EXCEL no standard page heading.

*Data Declaration

*u2014u2014u2014u2014u2014-

data: itab like alsmex_tabline occurs 0 with header line.

• Has the following format:

• Row number | Colum Number | Value

• u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014u2014

• i.e. 1 1 Name1

• 2 1 Joe

TYPES: Begin of t_record,

name1 like itab-value,

name2 like itab-value,

age like itab-value,

End of t_record.

DATA: it_record type standard table of t_record initial size 0,

wa_record type t_record.

DATA: gd_currentrow type i.

*Selection Screen Declaration

*u2014u2014u2014u2014u2014u2014u2014u2014u2014-

PARAMETER p_infile like rlgrap-filename.

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

*START OF SELECTION

call function u2018ALSM_EXCEL_TO_INTERNAL_TABLEu2019

exporting

filename = p_infile

i_begin_col = u20181u2032

i_begin_row = u20182u2032 u201CDo not require headings

i_end_col = u201814u2032

i_end_row = u201831u2032

tables

intern = itab

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc <> 0.

message e010(zz) with text-001. u201CProblem uploading Excel Spreadsheet

endif.

• Sort table by rows and colums

sort itab by row col.

• Get first row retrieved

read table itab index 1.

• Set first row retrieved to current row

gd_currentrow = itab-row.

loop at itab.

• Reset values for next row

if itab-row ne gd_currentrow.

append wa_record to it_record.

clear wa_record.

gd_currentrow = itab-row.

endif.

case itab-col.

when u20180001u2032. u201CFirst name

wa_record-name1 = itab-value.

when u20180002u2032. u201CSurname

wa_record-name2 = itab-value.

when u20180003u2032. u201CAge

wa_record-age = itab-value.

endcase.

endloop.

append wa_record to it_record.

*!! Excel data is now contained within the internal table IT_RECORD

• Display report data for illustration purposes

loop at it_record into wa_record.

write:/ sy-vline,

(10) wa_record-name1, sy-vline,

(10) wa_record-name2, sy-vline,

(10) wa_record-age, sy-vline.

endloop.

[Former Member]

you can use 'ALSM_EXCEL_TO_INTERNAL_TABLE' fm to upload the data rather than gui_upload.

Here is the piece of code.. which can be used to upload the data from excel sheet

form import_input_data.

*This function module uploads the excel file data into internal table it_exceldata.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = c_startcol

I_BEGIN_ROW = c_startrow

I_END_COL = c_endcol

I_END_ROW = c_endrow

TABLES

INTERN = it_exceldata

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

if sy-subrc eq 0.

read table it_exceldata index 1.

v_currentrow = it_exceldata-row.

• delete it_exceldata index 1.

loop at it_exceldata.

if it_exceldata-row ne v_currentrow.

append w_filedata to it_filedata.

clear w_filedata.

v_currentrow = it_exceldata-row.

endif.

case it_exceldata-col.

when '0001'.

w_FILEDATA-posky = it_exceldata-value.

when '0002'.

w_FILEDATA-accnt = it_exceldata-value.

when '0003'.

w_FILEDATA-crosc = it_exceldata-value.

when '0004'.

w_FILEDATA-busar = it_exceldata-value.

when '0005'.

w_FILEDATA-csctr = it_exceldata-value.

when '0006'.

w_FILEDATA-intor = it_exceldata-value.

when '0007'.

w_FILEDATA-prfct = it_exceldata-value.

when '0008'.

w_FILEDATA-plant = it_exceldata-value.

when '0009'.

w_FILEDATA-taxcd = it_exceldata-value.

when '0010'.

w_FILEDATA-linds = it_exceldata-value.

when '0011'.

w_FILEDATA-amount = it_exceldata-value.

endcase.

endloop.

append w_filedata to it_filedata.

CLEAR: SY-TABIX.

endif.

it_source[] = it_filedata[].

if sy-subrc ne 0.

message E000(01) with TEXT-003.

endif.

endform. "IMPORT_INPUT_DATA

[Former Member]

Thanks Naveen and Naveed.

I understand from your messages that GUI_UPLOAD cannot be used for direct excel upload, right?

Also, 'ALSM_EXCEL_TO_INTERNAL_TABLE' fails if excel has more than 2500 lines.

UV

[former_member705122]

Hi,

Check this links:

Regards

Adil

[Former Member]

hi ,

TYPE-POOLS truxs.

data it_type type truxs_t_text_data.

parameter p_file type rlgrap-filename.

data ttab type tabname.

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 = itab[]

OR Even U can use Gui upload.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = 'C:\test.xls'

WRITE_FIELD_SEPARATOR = 'X'

TABLES

data_tab = itab

Edited by: A kumar on Aug 21, 2008 7:48 AM

Edited by: A kumar on Aug 21, 2008 7:49 AM

[Former Member]

how can upload excel file into internal table using gui-upload.

[Former Member]

Hi You have to use the FM alsm_excel_to_internal_table. GUI_UPLOAD will not work for Excel uploading.

Thanks,

Krishna.

[Former Member]

Hi,

Use this function module ALSM_EXCEL_TO_INTERNAL_TABLE

instead of GUI_UPLOAD.

Check this sample code

REPORT  z_file3.

DATA: fname(40),
      w_line TYPE i VALUE 1,
      w_file TYPE rlgrap-filename.

DATA:
  t_tab LIKE
  TABLE OF ALSMEX_TABLINE
  WITH HEADER LINE.

DATA: fs_tab LIKE LINE OF t_tab.


w_file = 'D:\Book1.xls'.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = w_file
    i_begin_col                   = 1
    i_begin_row                   = 1
    i_end_col                     = 10
    i_end_row                     = 100
  tables
    intern                        = t_tab
 EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3
          .
IF sy-subrc NE 0.
  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
  WRITE: 'UPLOAD SUCCESSFUL'.
ENDIF.

fname = '.\z_file.xls'.
OPEN DATASET fname FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
LOOP AT t_tab INTO fs_tab.
  TRANSFER fs_tab TO fname.
ENDLOOP.

IF sy-subrc EQ 0.
  WRITE: / 'FILE OPENED ON APPS SERVER'.
ELSE.
  WRITE: / 'FILE COULD NOT BE OPENED'.
ENDIF.

Regards

Abhijeet

[Former Member]

Hi,

You can go with field symbols that would be dynamic. Check the following code

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 5
      i_end_row               = 4
    TABLES
      intern                  = t_tab
    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.

  LOOP AT t_tab.
    ASSIGN COMPONENT t_tab-col OF STRUCTURE t_mara TO <fs_mara>.
    WRITE t_tab-value TO <fs_mara>.
    AT END OF row.
     APPEND t_mara.
    ENDAT.

  ENDLOOP.

regards

padma