Upload Excel sheet

[Former Member]

Hi ALL,

Im new to ABAP and have a problem in uploading an excel file from the PC.After the data comes in internal table the data is in the ascii format. Pls help

my code looks as follows.

REPORT ZTESTING1 .

data : begin of t_table occurs 0,

one(9),

two(8),

thr(7),

four(6),

five(5),

six(4),

sev(3),

eig(2),

nin(1),

end of t_table .

data : w_file type string."Variable for File name

selection-screen begin of block param1 with frame title text-001.

selection-screen skip 1.

• File Name for Upload

parameters : p_file like ibipparms-path obligatory.

selection-screen end of block param1.

at selection-screen on value-request for p_file.

call function 'F4_FILENAME'

exporting

field_name = 'PATH'

importing

file_name = p_file.

start-of-selection .

move : p_file to w_file.

perform upload_data .

FORM upload_data .

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = w_file

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

TABLES

DATA_TAB = t_table

.

IF SY-SUBRC <> 0.

write : 'error ' .

ENDIF.

ENDFORM. " upload_data

[Former Member · Accepted solution]

HI ,

I suggest you use ALSM_EXCEL_TO_INTERNAL_TABLE fm

you'll have to declare the itab using the following structure....

data: itab like alsmex_tabline occurs 0 with header line.

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
*----------------------------
PARAMETER p_infile like rlgrap-filename.


************************************************************************
*START OF SELECTION
 call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_infile
            i_begin_col             = '1'
            i_begin_row             = '2'  "Do not require headings
            i_end_col               = '14'
            i_end_row               = '31'
       tables
            intern                  = itab
       exceptions
            inconsistent_parameters = 1
            upload_ole              = 2
            others                  = 3.
  if sy-subrc <> 0.
    message e010(zz) with text-001. "Problem 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 '0001'.                              "First name
        wa_record-name1 = itab-value.
      when '0002'.                              "Surname
        wa_record-name2 = itab-value.
      when '0003'.                              "Age
        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.

regards

satesh

[Former Member]

Hi,

Refer this code:

SELECTION-SCREEN BEGIN OF  BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE ibipparms-path OBLIGATORY. " For file selection
SELECTION-SCREEN END OF BLOCK b1.
*****************************************************************
*AT SELECTION SCREEN                                            *
*****************************************************************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = ' '
    IMPORTING
      file_name     = p_file.
*****************************************************************
*START OF SELECTION                                             *
*****************************************************************
START-OF-SELECTION.
* P_FILE is not compatible with the FM GUI_UPLOAD, so pass it to
* GV_FILE.
  gv_file = p_file.
Perform gui_upload_lina.
form gui_upload_lina.


* Call the function module GUI_UPLOAD to upload the file from the
* presentation server.
      CALL FUNCTION 'GUI_UPLOAD'
        EXPORTING
          filename                = gv_file
          filetype                = 'ASC'
          has_field_separator     = 'X'
        TABLES
          data_tab                = gt_lin
        EXCEPTIONS
          file_open_error         = 1
          file_read_error         = 2
          no_batch                = 3
          gui_refuse_filetransfer = 4
          invalid_type            = 5
          no_authority            = 6
          unknown_error           = 7
          bad_data_format         = 8
          header_not_allowed      = 9
          separator_not_allowed   = 10
          header_too_long         = 11
          unknown_dp_error        = 12
          access_denied           = 13
          dp_out_of_memory        = 14
          disk_full               = 15
          dp_timeout              = 16
          OTHERS                  = 17.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.

endform.                    " gui_upload

Regards,

Gayathri

[Former Member]

Hi Sudha/Santosh,

I changed the value of the parameter filetype to "DAT', it gives no errors but while executing it throws an error saying invalid value of the parameter filetype.

I have been told to use only GUI_UPLOAD function module so i cannot use any others to upload this file.

Any other idea?

[Former Member]

Hi,

better you use ws_upload FM. and you should give filetype as DAT.

Thanks,

Neptune.M

[Former Member]

hi,

we can use <b>WS_UPLOAD</b> and give filetype as <b>'DAT'</b> also and check it out

Reward Points if helpful..

Regards,

Santosh

[Former Member]

Hi,

I think you need to save the excel file as 'tab delimited text file'. Now select the file from the dialog box and it will work .

Your program is working fine for me.

Regards,

SP.

[Former Member]

Pass value DAT to the FILETYPE parameter in FM GUI_UPLOAD. The ASC that you have passed stands for ASCII.

Hope this helps.

Sudha

[Former Member]

HI,

WELCOME TO SDN

Check my post in this link...

i.e,

data : filename type string,

fl_type type char10 value <b>'DAT</b>'.

FILENAME = 'C:\TEST.xls'.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = filename

FILETYPE = fl_type

<b>HAS_FIELD_SEPARATOR = 'X'</b>

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• CHECK_BOM = ' '

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

DATA_TAB = ITAB

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_READ_ERROR = 2

NO_BATCH = 3

GUI_REFUSE_FILETRANSFER = 4

INVALID_TYPE = 5

NO_AUTHORITY = 6

UNKNOWN_ERROR = 7

BAD_DATA_FORMAT = 8

HEADER_NOT_ALLOWED = 9

SEPARATOR_NOT_ALLOWED = 10

HEADER_TOO_LONG = 11

UNKNOWN_DP_ERROR = 12

ACCESS_DENIED = 13

DP_OUT_OF_MEMORY = 14

DISK_FULL = 15

DP_TIMEOUT = 16

OTHERS = 17

.

IF SY-SUBRC <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Regards,

Santosh