topic Re: GUI_UPLOAD for excel? in Application Development and Automation Discussions

GUI_UPLOAD for excel?

Former Member — Wed, 20 Aug 2008 22:09:39 GMT

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,

Re: GUI_UPLOAD for excel?

Former Member — Wed, 20 Aug 2008 22:18:41 GMT

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.

Re: GUI_UPLOAD for excel?

Former Member — Wed, 20 Aug 2008 22:19:49 GMT

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

Re: GUI_UPLOAD for excel?

Former Member — Wed, 20 Aug 2008 22:24:55 GMT

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.

Re: GUI_UPLOAD for excel?

Former Member — Thu, 21 Aug 2008 05:47:01 GMT

Hi,

Check this links:

Regards

Adil

Re: GUI_UPLOAD for excel?

Former Member — Thu, 21 Aug 2008 05:47:46 GMT

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

Re: GUI_UPLOAD for excel?

Former Member — Thu, 21 Aug 2008 05:51:08 GMT

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

Re: GUI_UPLOAD for excel?

Former Member — Thu, 21 Aug 2008 06:33:51 GMT

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

Re: GUI_UPLOAD for excel?

Former Member — Thu, 19 May 2011 05:13:03 GMT

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

Re: GUI_UPLOAD for excel?

Former Member — Tue, 26 Jul 2011 13:07:56 GMT

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

Thanks,

Krishna.