topic Re: Problem in excel sheet upload and creating dynamic internal table in Application Development and Automation Discussions

Problem in excel sheet upload and creating dynamic internal table

Former Member — Thu, 30 Oct 2008 11:04:46 GMT

Hi,

I am using a excel file to upload and because the number of fields and its order will change in future, i am creating a dynamic internal table to process the data.

for Upload I am using TEXT_CONVERT_XLS_TO_SAP.

the problem i am facing is : if there is no data in any one of the field , that particular field is not seen in the internal table.

for eg :

A B C D E F

1 2 4 5 6

The internal table doesnt get the data for C. Even a empty space will nnot be populated.

Because of this , i am getting problem in creating dynamic internal table.

Kindly let me know :

Is there any other way to capture all the fields even if it is not having any data.

Using TEXT_CONVERT_XLS_TO_SAP. fm is useful in creating a dynamic internal table.

Is there any other FM which will help in creating a dynamic interna table.

Thanks .

Vikki.

Re: Problem in excel sheet upload and creating dynamic internal table

Former Member — Thu, 30 Oct 2008 11:07:39 GMT

Hi,

Have tried with the function module ALSM_EXCEL_TO_INTERNAL_TABLE

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = P_FNAME

i_begin_col = 1

i_begin_row = 1

i_end_col = 44

i_end_row = 60000

TABLES

intern = IT_FILE_UPLOAD

IF sy-subrc <> 0.

ENDIF.

Check this.

Cheers!!

VEnk@

Re: Problem in excel sheet upload and creating dynamic internal table

Former Member — Thu, 30 Oct 2008 11:11:34 GMT

Hi Vikki,

Try it out the below FM:


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

Re: Problem in excel sheet upload and creating dynamic internal table

Former Member — Thu, 30 Oct 2008 11:13:08 GMT

u have to use following way ,

i hope it will helpfull to u

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

I_TAB_RAW_DATA = T_RECORD

I_FILENAME = P_FILE

TABLES

I_TAB_CONVERTED_DATA = T_TAB1

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

MESSAGE I000(0K) WITH TEXT-001.

ENDIF.

DESCRIBE TABLE T_TAB1 LINES V_COUNT.

WRITE: 'Total number of records in flat file:', V_COUNT.

SKIP.

ENDFORM. " DATA_UPLOAD

Regards

Saimedha

Re: Problem in excel sheet upload and creating dynamic internal table

Former Member — Thu, 30 Oct 2008 11:13:52 GMT

So why dont u use Header in Excel file....It will always contain data.

Build ur Dynamic internal table using the Header information....

Re: Problem in excel sheet upload and creating dynamic internal table

Former Member — Fri, 31 Oct 2008 07:32:43 GMT

Hi ,

I populated space if there is not data in the internal table.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 60

I_END_ROW = 100

TABLES

INTERN = I_DATA

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 I_DATA-COL is not having value, i have populated it with a space and the problem is solved.

Thanks .

vikki.