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

Upload data from excel

Former Member — Sat, 25 Jul 2009 10:31:33 GMT

Hi,

I have to upload data from excel to internal table.I had checked in forum.But none of them worked.

I am using the version ECC6.0,can i use gui_upload for this?.

I tried with it,but i am getting error as 'Cannot interpret data in file'.

Please suggest me.

Thanks,

Sri

Re: Upload data from excel

Former Member — Sat, 25 Jul 2009 10:34:33 GMT

Hi,

Check this link for upload the date from excel file into internal table..it is not possible to upload the excel file using the GUI_UPLOAD

Check this link for FM's

[KCD_EXCEL_OLE_TO_INT_CONVERT|http://docs.google.com/Doc?docid=0AZqwnQpzX9wBZGZ2MmhtZ3NfMTFjZzRqZmRw&hl=en]

[ALSM_EXCEL_TO_INTERNAL_TABLE|]

Re: Upload data from excel

Former Member — Mon, 27 Jul 2009 10:14:24 GMT

/people/alvaro.tejadagalindo/blog/2009/02/05/excel-ole-and-abap--create-fancy-reports

Re: Upload data from excel

Former Member — Tue, 28 Jul 2009 11:15:47 GMT

Try using this function module ALSM_EXCEL_TO_INTERNAL_TABLE

Re: Upload data from excel

Former Member — Tue, 28 Jul 2009 11:20:52 GMT

Hi Sri,

Save your excel file as CSV .

and then use

GUI_UPLOAD

Regards,

Vijay

Re: Upload data from excel

Former Member — Tue, 28 Jul 2009 11:21:14 GMT

Hi Friend,

try to use TEXT_CONVERT_XLS_TO_SAP with the below parameters

data: BEGIN OF it_file_data occurs 0,

lifnr TYPE lifnr,

bldat TYPE bldat,

budat TYPE budat,

xblnr TYPE xblnr1,

wrbtr TYPE wrbtr,

sgtxt1 TYPE sgtxt,

sgtxt2 TYPE sgtxt,

END OF it_file_data.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_filenm

TABLES

i_tab_converted_data = it_file_data

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

Cheers,

Murali

Re: Upload data from excel

Former Member — Tue, 28 Jul 2009 12:29:16 GMT

Hi ,

Try Gui_upload...

a) If your data file is in Excel sheet then save Excel sheet as Text(tab delimited). This you can use for upload.

It will work.

call gui_upload as--

call method cl_gui_frontend_services=>gui_upload

exporting

filename = 'C:\test\file..txt' <----

your data file saved as tab delimited

filetype = 'ASC'

has_field_separator = 'X' <----

uncomment this

header_length = 0
read_by_line = 'X'
dat_mode = SPACE
codepage = SPACE
ignore_cerr = ABAP_TRUE
replacement = '#'
virus_scan_profile = virus_scan_profile
IMPORTING
filelength = filelength
header = header

changing

data_tab = custom_final[] <----

your internal table

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

not_supported_by_gui = 17

error_no_gui = 18

others = 19.

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 data from excel

Former Member — Tue, 28 Jul 2009 12:36:01 GMT

GUI_UPLOAD will definitely work.

Re: Upload data from excel

Former Member — Tue, 28 Jul 2009 12:41:45 GMT

Hi,

Try using Function module:

TEXT_CONVERT_XLS_TO_SAP

For that declare data like:



DATA: it_raw TYPE truxs_t_text_data.


*&---------------------------------------------------------------------*
*&Function module called to upload xls data into an internal table
*&---------------------------------------------------------------------*

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_field_seperator    = 'X'
      i_line_header        = 'X'
      i_tab_raw_data       = it_raw
      i_filename           = p_file                 "file path browsed
    TABLES
      i_tab_converted_data = it_upload[]            "int table populated
    EXCEPTIONS
      conversion_failed    = 1                      "browsed file's data
      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.

Hope it helps

Regards

Mansi

Re: Upload data from excel

former_member206377 — Wed, 29 Jul 2009 04:44:31 GMT

Hi Sri,

parameter p_file type localfile default 'c:\text' obligatory.

DATA: TXT_FILENAME LIKE RLGRAP-FILENAME,                 " Create structure equivalent to Excel file
                 GT_FILE TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER line.

   start-of-selection.
    TXT_FILENAME = p_file.
* Function Module call to Upload Excel file Data in an Internal table GT_FILE  FORM UPLOAD_LGL_ACC_DATA .
  REFRESH GT_FILE.
  CLEAR GT_FILE.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                      = TXT_FILENAME
      i_begin_col                   = '1'
      i_begin_row                   = '1'
      i_end_col                     = '200'
      i_end_row                     = '5000'
    tables
      intern                        = GT_FILE
   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.
 IF GT_FILE[] IS NOT INITIAL.
    LOOP AT GT_FILE.
      CASE GT_FILE-COL.
        WHEN '0001'.
"fill ur structure field here
         WHEN '0002'.
 "fill ur structure field here
        WHEN '0003'.
    "fill ur structure field here
      ENDCASE.

gt_file will have the contents of excel .

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 04:56:35 GMT

Hi,

Refer to the given code:

DATA: lv_filetype(10) TYPE c,

lv_gui_sep TYPE c,

lv_file_name TYPE string.

lv_filetype = 'ASC'.

lv_gui_sep = 'X'.

lv_file_name = pa_dfile.

FM call to upload file

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = lv_file_name

filetype = lv_filetype

has_field_separator = lv_gui_sep

TABLES

data_tab = gi_Table

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

Hope it helps.

Regards

Rajesh Kumar

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 06:11:23 GMT

type-pools : truxs.

Internal table declaartion for excel file

data : it_raw type truxs_t_text_data,

Calling the function module to upload Excelsheet data to Internal table

call function 'TEXT_CONVERT_XLS_TO_SAP'

exporting

I_FIELD_SEPERATOR = 'X'

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_flname "" Excel file

tables

i_tab_converted_data = it_upload " our Internal table

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.

These will help you to upload the excel sheet data to our internal table

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 07:07:05 GMT

Hi,

Check out this wiki, explains every step by step.

https://www.sdn.sap.com/irj/scn/wiki?path=/display/abap/prog%252bto%252bupload%252bfrom%252bxl%252bto%252bddic%252btable

Thanks,

Krishna

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 07:21:49 GMT

Hi,

Use gui_upload

Thanks

Ashu

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 10:52:32 GMT



DATA: IT_INTERN TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE

PARAMETERS: P_BEGCOL TYPE I DEFAULT 1 NO-DISPLAY,
P_BEGROW TYPE I DEFAULT 1 NO-DISPLAY,
P_ENDCOL TYPE I DEFAULT 100 NO-DISPLAY,
P_ENDROW TYPE I DEFAULT 32000 NO-DISPLAY.


PARAMETER : P_FILE TYPE RLGRAP-FILENAME OBLIGATORY DEFAULT 'C:\Documents and Settings\Administrator\Desktop\BOOK2.XLS'.


  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                      = P_FILE
      I_BEGIN_COL                   = P_BEGCOL
      I_BEGIN_ROW                   = P_BEGROW
      I_END_COL                     = P_ENDCOL
      I_END_ROW                     = P_ENDROW
    TABLES
      INTERN                        = IT_INTERN
* 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.

DATA: LV_INDEX TYPE I.

  field-symbols <FS>.

  SORT IT_INTERN BY ROW COL.
  CLEAR IT_INTERN.
  LOOP AT IT_INTERN.

    MOVE IT_INTERN-COL TO LV_INDEX.

    ASSIGN COMPONENT LV_INDEX OF STRUCTURE IT_DATA TO <FS>.

    MOVE IT_INTERN-VALUE TO <FS>.
    AT END OF ROW.

      APPEND IT_DATA.
      CLEAR IT_DATA.
    ENDAT.


  ENDLOOP.

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 11:48:26 GMT

hii Sri,

Go through below link.

there i hv posted complete code which is successfully running

[|]

Re: Upload data from excel

Former Member — Wed, 29 Jul 2009 12:47:11 GMT

Hi Sri,

don't use GUI_UPLOAD.

Instead of that use FM : ALSM_EXCEL_TO_INTERNAL_TABLE.

here you need to pass the file path, starting row no & ending row no & starting & ending column no.

this will work fine.

otherwise ,you can check FM: TEXT_CONVERT_XLS_TO_SAP.

Regards,

Tutun