topic Re: Upload file from excel sheet in Application Development and Automation Discussions

Upload file from excel sheet

naveen_inuganti2 — Fri, 10 Oct 2008 08:38:59 GMT

Hi...

data: begin of itab occurs 0,
      f1, f2, f3, f4, f5,
      end of itab.

call function 'GUI_UPLOAD'
  exporting
    filename                      = 'File Path'
   FILETYPE                      = 'ASC'
   HAS_FIELD_SEPARATOR           = 'X'
*   HEADER_LENGTH                 = 0
*   READ_BY_LINE                  = 'X'
*   DAT_MODE                      = ' '
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
*   CHECK_BOM                     = ' '
*   VIRUS_SCAN_PROFILE            =
*   NO_AUTH_CHECK                 = ' '
* 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.

loop at itab.
  write:/ itab-f1,
          itab-f2,
          itab-f3,
          itab-f4,
          itab-f5.
endloop.

My problem is..,

My excel sheet is having following entries..,

t w w g g

r e d h b

e w f b d

But my program giving output as..,

P ï ý

0.1. / #*

Ç t

Þ # é

0.1. ç í ® Ì*

0.1. u2122 # u0161*

Y Ô # #

= #

[

u2020 # Í

. $ u2014 § ý

What is the problem.?

Thanks,

Naveen.I

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:42:17 GMT

Hi,

Use function module ASLM_EXCEL_TO_INTERNAL_TABLE to upload excel to internal table. It uploads data according to row and column. Modify it as per your need.

Regards,

Dhanunjaya Reddy

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:42:35 GMT

Hi,

Save Excel as Text (Tab Delimited) file and then use GUI_Upload

Regards,

Vishal

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:43:54 GMT

filename = 'File Path'

are you passing the right file name

Regards

Madhan

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:43:59 GMT

Hi,

Try using this FM TEXT_CONVERT_XLS_TO_SAP.

in i_tab_raw_data pass rawdata

& in i_filename pass w_filenm(filename of excel thru path)

use FM CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = pr_file

mask = ',*.xls.'

mode = 'O'

title = 'Upload File'(i03)

IMPORTING

filename = pr_file

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

to get filename.

then

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

i_tab_raw_data = rawdata

i_filename = w_filenm

TABLES

i_tab_converted_data = itab1

EXCEPTIONS

conversion_failed = 1

OTHERS = 2

Regards,

Amit

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:44:47 GMT

Hi Naveen,

The FM GUI_UPLOAD does not works properly for excel sheets.

there are other FM

1. ALSM_EXCEL_TO_INTERNAL_TABLE

2.TEXT_CONVERT_XLS_TO_SAP

regards

Kumar M

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:53:29 GMT

change

FILETYPE = 'ASC'

with

FILETYPE = 'DAT'

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 08:54:09 GMT

Hi Naveen ,

copy that same text and give in notepad .txt file .

u declear data as a lenth 1 and type c .

copy in ur notepad by giving 1 space between each field or give tab space.

and give you file path name correctly in ur FM .

Re: Upload file from excel sheet

naveen_inuganti2 — Fri, 10 Oct 2008 08:56:11 GMT

Hi Amit..

If I change File type as 'DAT'... Giving error "Wrong value for file type'.

Hi Madhu/Vishal..

Here my requirement is to get file from excel type only.

Hi Madhan..,

I dont think it will not give error if i pass incorrect file path. Its correct file path, which iam passing.

Thanks,

Naveen.I

Re: Upload file from excel sheet

naveen_inuganti2 — Fri, 10 Oct 2008 09:04:26 GMT

Hi Danunjay/Mukesh..,

Following program is jumping into Dump, let me know if any issues about parameter passing...?

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  exporting
    filename                      = 'File Path\File name.xlsx'
    i_begin_col                   = 'A'
    i_begin_row                   = 1
    i_end_col                     = 'E'
    i_end_row                     = 3
  tables
    intern                        = itab
* 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 itab.
  write:/ itab-f1,
          itab-f2,
          itab-f3,
          itab-f4,
          itab-f5.
endloop.

Thanks,

Naveen.I

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 09:07:11 GMT

Hi,

No need of changing it to DAT.

Let it be saved as .xls only..

and try using the FM's as i said earlier..it has worked for me for all BDC's ..it will surely work for u as well..

tell mw if there's any more prob

Regards,

Amit

Re: Upload file from excel sheet

GauthamV — Fri, 10 Oct 2008 09:08:50 GMT

hi,

check this.


  DATA: BEGIN OF itab OCCURS 0.
   INCLUDE STRUCTURE  alsmex_tabline.
 DATA: END OF itab.

PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,
            begcol TYPE i DEFAULT 1 NO-DISPLAY,
            begrow TYPE i DEFAULT 1 NO-DISPLAY,
            endcol TYPE i DEFAULT 100 NO-DISPLAY,
            endrow TYPE i DEFAULT 32000 NO-DISPLAY.
 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
            filename                = 'UR Filename'
            i_begin_col             = begcol
            i_begin_row             = begrow
            i_end_col               = endcol
            i_end_row               = endrow
       TABLES
            intern                  = itab
       EXCEPTIONS
            inconsistent_parameters = 1
            upload_ole              = 2
            OTHERS                  = 3.

loop at itab.
  write:/ itab-value.
endloop.

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 09:09:23 GMT

Hi Naveen,

Example ur filename is a.xls

Here's the code , very simple

PARAMETER:pr_file LIKE rlgrap-filename OBLIGATORY.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pr_file.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = pr_file

mask = ',*.xls.'

mode = 'O'

title = 'Upload File'(i03)

IMPORTING

filename = pr_file

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

START-OF-SELECTION.

WRITE pr_file TO w_filenm.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

i_tab_raw_data = rawdata

i_filename = w_filenm

TABLES

i_tab_converted_data = itab1

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.

Re: Upload file from excel sheet

naveen_inuganti2 — Fri, 10 Oct 2008 09:10:54 GMT

Hi Amit Lyer..

What is the parameter i_tab_raw_data in the function module TEXT_CONVERT_XLS_TO_SAP given by you?

Thanks,

Naveen.I

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 09:13:27 GMT

Hi,

Its Amit Iyer.

btw ..no parameter for it..just hardcode it as rawdata in the Function module. declare it as

DATA: rawdata(4096) TYPE c OCCURS 0.

Regards,

Amit

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 09:24:54 GMT

Hi Try the following code:



data   it_dtab  type table of string.
"it_utab your upload file structure

data c_hdrln type i 2. " To skip the header in excel file

    call function 'FAA_FILE_UPLOAD_EXCEL'
      exporting
        i_filename                 = l_filename
*       I_TEST                     =
*       I_UCOMM                    =
*       I_MAXCOLS                  =
        i_delimiter                = ','
      tables
        et_filecontent             = it_dtab
     exceptions
       error_accessing_file       = 1
       others                     = 2
              .

  perform delimit_comma2utab tables it_dtab it_utab
                                 using  c_hdrln.

" Processing the table IT_DTAB( data with comma separation ) " to transfer the data to IT_UTAB

form delimit_comma2utab  tables pt_dtab
                                pt_utab structure wa_utab
                         using  p_hline type i.

  data: l_fld1 type string,
        l_fld2 like l_fld1,
        l_type,
        l_flg  type i,
        l_idx  type i.

  field-symbols: <fs> type any.

  refresh pt_utab.

  clear : wa_dtab, wa_utab,l_flg.

  loop at pt_dtab into wa_dtab.


* Skipping the Header Line of Excel sheet
    if sy-tabix <= p_hline or wa_dtab is initial.
      continue.
    endif.

* Storing the entire line data to local field

    l_fld2 = wa_dtab.

* Here we use Field symbols to store each field of structure wa_utab
* and splitting the data from local field l_fld2 to corresponding field in
* WA_UTAB

    do.

      l_idx = sy-index.

* Reading the Field Name for Utab based on the selection

      read table it_fnam index l_idx into wa_fnam.

      if sy-subrc = 0.

        assign component wa_fnam-name of structure wa_utab to <fs>.

        if sy-subrc = 0.

          find ',' in l_fld2.

          if sy-subrc = 0.

            split l_fld2 at ',' into l_fld1 l_fld2.

            if sy-subrc = 0.

* Catching Excepition while uploading wrong layout eXcel file

              catch system-exceptions  conversion_errors = 4
                                      others = 8.
                <fs> = l_fld1.

              endcatch.

              if sy-subrc <> 0.

                message e021.

              endif.

              unassign <fs>.

            else.

              exit.

            endif.

          else.

* Copying the Last Column of the Row

            if l_fld2 is not initial.

              <fs> = l_fld2.

            endif.

            exit.

          endif.

        else.

          exit.

        endif.

      else.

        exit.

      endif.

    enddo.

    if wa_utab is initial.

      exit.

    endif.

    if wa_utab is not initial." AND l_flg = 2.

      append wa_utab to pt_utab.
      clear  wa_utab.

    endif.

  endloop.

endform.                    " delimit_comma2utab

Regards*,

Jey

Re: Upload file from excel sheet

naveen_inuganti2 — Fri, 10 Oct 2008 09:26:52 GMT

Hi Amit..,

I am defining the rawdata as you said..

data: begin of itab occurs 0,
      f1, f2, f3, f4, f5,
      end of itab.
data: rawdata(4096) type c occurs 0.

call function 'TEXT_CONVERT_XLS_TO_SAP'
  exporting
*   I_FIELD_SEPERATOR          =
*   I_LINE_HEADER              =
    i_tab_raw_data             = rawdata
    i_filename                 = <file path>  
  tables
    i_tab_converted_data       = itab
* 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.



loop at itab.
  write:/ itab-f1,
          itab-f2,
          itab-f3,
          itab-f4,
          itab-f5.
endloop.

After excution of this code I am getting following microsoft windows message pop-up...

The file you are trying to open , 'dhfgjgfwqhgfh.tmp', is in a different format than specified by the file extension. verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

If I give YES. Its giving correct ouput.

Let me know if i can rectify this error with any other coding...

Also let me know why we are passing rawdata here...???

Thanks,

Naveen.I

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 09:29:42 GMT

'TEXT_CONVERT_XLS_TO_SAP' expects a file extension XLS

'dhfgjgfwqhgfh.tmp' has extension TMP

Re: Upload file from excel sheet

naveen_inuganti2 — Fri, 10 Oct 2008 09:37:11 GMT

Yeah Maen..,

My file on desktop Book1.xls

I am passing the parameter filename as 'path\Book1.xls'.

Even then I am getting this issue. Ok I will consult my basis people on this.

Let me know about that RAWDATA variable declaration and.,

Why ALSM_EXCEL_TO_INTERNAL_TABLE function module is not working in my case.. where I am doing mistae?

Hi Goutham Chakravarthi.. My Code is similer to your's But still its jumping into dump. Why?

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  exporting
    filename                      = 'File Path\File name.xlsx'
    i_begin_col                   = 'A'
    i_begin_row                   = 1
    i_end_col                     = 'E'
    i_end_row                     = 3
  tables
    intern                        = itab
* 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 itab.
  write:/ itab-f1,
          itab-f2,
          itab-f3,
          itab-f4,
          itab-f5.
endloop.

Thanks,

Naveen.I

Re: Upload file from excel sheet

Former Member — Fri, 10 Oct 2008 09:44:39 GMT

Hi Naveen,

Try Using the below program to get the data from the excel.


*&---------------------------------------------------------------------*
*&      Form  P_UPLOAD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM p_upload_data .
  DATA : gd_scol   TYPE i VALUE '1',
          gd_srow   TYPE i VALUE '2',
          gd_ecol   TYPE i VALUE '5',
          gd_erow   TYPE i VALUE '60000'.

  DATA: it_tab TYPE filetable,
        gd_subrc TYPE i.




  DATA : lt_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.

  DATA : ld_index TYPE i.
  FIELD-SYMBOLS : <fs> .

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = p_path
      i_begin_col             = gd_scol
      i_begin_row             = gd_srow
      i_end_col               = gd_ecol
      i_end_row               = gd_erow
    TABLES
      intern                  = lt_intern
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE:/ 'Error Uploading file'.
    EXIT.
  ENDIF.

  IF lt_intern[] IS INITIAL.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE:/ 'No Data Uploaded'.
    EXIT.
  ELSE.
    SORT lt_intern BY row col.
    LOOP AT lt_intern.
      MOVE lt_intern-col TO ld_index.
      ASSIGN COMPONENT ld_index OF STRUCTURE it_final TO <fs>.
      MOVE lt_intern-value TO <fs>.
      AT END OF row.
        APPEND it_final .
        CLEAR  it_final.
      ENDAT.
    ENDLOOP.

  ENDIF.
ENDFORM.                    " P_UPLOAD_DATA

Thanks,

Chidanand