topic Re: Need help in processing an excel into SAP in Application Development and Automation Discussions

Need help in processing an excel into SAP

Former Member — Mon, 20 Dec 2010 06:40:24 GMT

Hi All..

Im uploading an excel file from presentation server to SAP...

Now the problem is hw to know how many records are there in the excel file..

Have coded like below...

w_max = '65536'.

  w_counter_lo = '2'.    "Start on row 1 because of header row
  w_increment  = '9999'. "Increment by 9999 each loop

  w_counter_hi = w_counter_lo + w_increment.

WHILE w_counter_hi <= w_max AND w_counter_lo <= w_max.
    " Clear the work area and the internal table to prevent duplicate rows
    CLEAR it_import_table.
    CALL FUNCTION 'ZPM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = p_file
        i_begin_col             = '1'
        i_begin_row             = w_counter_lo "we have a header row
        i_end_col               = '5'
        i_end_row               = w_counter_hi
      TABLES
        intern                  = it_import_table
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.
    "Increment our range
    w_counter_lo = w_counter_hi + 1.
    w_counter_hi = w_counter_lo + w_increment.

    "We SHOULD NOT scan any more than our max, so set our top end of the range
    "to the total number of rows to scan if the w_Counter_hi exceeds the max.
    IF w_counter_hi > w_max.
      w_counter_hi = w_max.
    ENDIF.

  ENDWHILE.

Considering the excel file would contain not more than 65536 records have coded like above...

But the problem is sometimes the file may contain lesser records say 10000 records even then also the while ..endwhile executes to check the last record( until 65536 records)..

pls help me to handle this..

Thanks,

Priya

Re: Need help in processing an excel into SAP

former_member242255 — Mon, 20 Dec 2010 06:44:19 GMT

you can use the std fun modules given by SAP.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

'GUI_UPLOAD'

what is the reason for you to go for Z func modl and also so much coding.

Re: Need help in processing an excel into SAP

Former Member — Mon, 20 Dec 2010 06:54:48 GMT

Hi,

Use the FM --> TEXT_CONVERT_XLS_TO_SAP

This will give you the excel into an internal table , then delete the rows that are greater then ur requirement.

Regards,

Madhukar Shetty

Re: Need help in processing an excel into SAP

Former Member — Mon, 20 Dec 2010 07:03:12 GMT

Hi..

Suppose If I use the standard Fm ALSM_EXCEL_TO_INTERNAL_TABLE only...

Tell me what value to pass to " i_end_row "

My question is how to trace the no: of records of the exceel file..sothat I can pass the value to I_END_ROW...

Thanks,

Priya

Re: Need help in processing an excel into SAP

Former Member — Mon, 20 Dec 2010 07:35:35 GMT

Hi Priya,

We did had the requirement quite similar as of your's. Hope this code snippet helps you.

  DATA: STR_LEN TYPE I.
  STR_LEN = STRLEN( P_FILENM ).
  STR_LEN = STR_LEN - 4.
  IF P_FILENM+STR_LEN(4) = '.XLS' or P_FILENM+STR_LEN(4) = '.xls'.
    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        FILENAME                = P_FILENM
        I_BEGIN_COL             = 1
        I_BEGIN_ROW             = 1
        I_END_COL               = 2
        I_END_ROW               = 10000
      TABLES
        INTERN                  = IT_EXCEL
      EXCEPTIONS
        INCONSISTENT_PARAMETERS = 1
        UPLOAD_OLE              = 2
        OTHERS                  = 3.
    

*-- Sagregate Excel Data into the Internal Table
    LOOP AT it_excel.
      CHECK it_excel-row NE '0001'.
      IF it_excel-col = '0001'.
        MOVE it_excel-value TO IT_INPUT-POSID.
      ELSEIF it_excel-col = '0002'.
        MOVE it_excel-value TO IT_INPUT-PSPRI.
      ENDIF.

      AT END OF row.

        APPEND  IT_INPUT.
        CLEAR   IT_INPUT.
*      v_excel = v_excel + 1.
      ENDAT.
    ENDLOOP

Edited by: K.Manas on Dec 20, 2010 8:35 AM

Re: Need help in processing an excel into SAP

Former Member — Mon, 20 Dec 2010 10:41:11 GMT

Whenever there is SY-SUBRC fail the EXIT the While Loop.

WHILE <condition>.
    CALL FUNCTION 'ZPM_EXCEL_TO_INTERNAL_TABLE'.
      IF SY-SUBRC NE 0.
        EXIT.  "to exit the WHILE loop
      ENDIF.
ENDWHILE.

Re: Need help in processing an excel into SAP

Former Member — Mon, 20 Dec 2010 12:23:29 GMT

w_max = '65536'.

w_counter_lo = '2'. "Start on row 1 because of header row

w_increment = '9999'. "Increment by 9999 each loop

w_counter_hi = w_counter_lo + w_increment.

before reading your file you want to know how many rows are there in excel file. put this code here.

*******************************************

TYPES:

truxs_t_text_data(4096) TYPE c OCCURS 0.

DATA:

lv_var TYPE truxs_t_text_data.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

i_tab_raw_data = lv_var

i_filename = p_file * path of file

TABLES

i_tab_converted_data = t_record

EXCEPTIONS

conversion_failed = 1

OTHERS = 2 .

here you will get all records in t_record internal table.

IF sy-subrc <> 0.

MESSAGE 'Error in Uploading the specified file.' TYPE 'I'.

ELSE.

DESCRIBE TABLE t_record LINES w_counter_hi

ENDIF.

refresh t_record.

So, now you have no of lines in w_counter_hi variable.

*******************************************

WHILE w_counter_hi <= w_max AND w_counter_lo <= w_max.

" Clear the work area and the internal table to prevent duplicate rows

CLEAR it_import_table.

CALL FUNCTION 'ZPM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = '1'

i_begin_row = w_counter_lo "we have a header row

i_end_col = '5'

i_end_row = w_counter_hi

TABLES

intern = it_import_table

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

"Increment our range

w_counter_lo = w_counter_hi + 1.

w_counter_hi = w_counter_lo + w_increment.

"We SHOULD NOT scan any more than our max, so set our top end of the range

"to the total number of rows to scan if the w_Counter_hi exceeds the max.

IF w_counter_hi > w_max.

w_counter_hi = w_max.

ENDIF.

ENDWHILE.

Regards,