topic Re: validate and upload data from excel sheet in Application Development and Automation Discussions

validate and upload data from excel sheet

Former Member — Wed, 31 Oct 2007 04:41:07 GMT

Hello all,

i have data in a excel sheet, and i need to validate the data and then upload the data into DB. The excel sheet consists of say 8 coloumns and 10 rows. While uploading, the first 2 rows are to be ignored and the remaining 8 rows are to be updated into an internal table.

can someone help me out with this. i am struggling since 2 days.

Regards,

Seenu

Re: validate and upload data from excel sheet

vaibhav_tiwari — Wed, 31 Oct 2007 04:48:46 GMT

Hi seenu,

<u> use the following code to upload data into internal table, after uploading is complete delete first two rows of internal table.:</u>

TYPES: BEGIN OF t_datatab,
         empid LIKE zvresult-empid,
         bitsid LIKE zvresult-bitsid,
         name LIKE zvresult-name,
         maths LIKE zvresult-maths,
         sp LIKE zvresult-sp,
         de LIKE zvresult-de,
         mgmt LIKE zvresult-mgmt,
         total LIKE zvresult-total,
         avrg LIKE zvresult-avrg,
       END OF t_datatab.

DATA: itab TYPE STANDARD TABLE OF t_datatab WITH HEADER LINE,
      seltab TYPE STANDARD TABLE OF t_datatab WITH HEADER LINE.


DATA: it_raw TYPE truxs_t_text_data.

* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'P_FILE'
    IMPORTING
      file_name  = filename.

***********************************************************************
*START-OF-SELECTION.
START-OF-SELECTION.
  IF filename IS NOT INITIAL.
    CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
      EXPORTING
*     I_FIELD_SEPERATOR        =
        i_line_header            =  'X'
        i_tab_raw_data           =  it_raw       " WORK TABLE
        i_filename               =  filename
      TABLES
        i_tab_converted_data     = itab[]    "ACTUAL 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.
  ELSE.
    MESSAGE s001(zmsgclass).
  ENDIF.
***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.

Re: validate and upload data from excel sheet

Former Member — Wed, 31 Oct 2007 04:54:29 GMT

need some more ideas, my dear friends.

Seenu

Re: validate and upload data from excel sheet

Former Member — Wed, 31 Oct 2007 05:01:24 GMT

Hi seenu,

first you upload the entire data into internal table, then you can delete 2 rows from there. declare the internal table of 8 fields.

us this class method to open "file open dialog" and upload data

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = 'Select file for upload'

default_extension = '.xls'

default_file_name = lv_path

initial_directory = lc_c

CHANGING

filename = lv_path

path = lc_c

fullpath = lv_fullpath

EXCEPTIONS

cntl_error = 1

error_no_gui = 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.

ELSE.

v_fnam = lv_fullpath.

ENDIF.

IF v_fnam IS INITIAL.

RETURN.

ENDIF.

IF i_finalclear[] IS NOT INITIAL.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = v_fnam

filetype = 'DAT'

HEADER = header

append = 'X'

write_field_separator = 'X'

CHANGING

data_tab = lt "My internal table

EXCEPTIONS

OTHERS = 8.

regards,

sheron

Re: validate and upload data from excel sheet

Former Member — Wed, 31 Oct 2007 05:03:13 GMT

then you modify the databse with the internal table

regards,

sheron

Re: validate and upload data from excel sheet

Former Member — Wed, 31 Oct 2007 09:43:40 GMT

anymore???

regards,

seenu