topic Re: Fill Internal table from excel sheet dynamically in Application Development and Automation Discussions

Fill Internal table from excel sheet dynamically

Former Member — Wed, 10 Mar 2010 13:49:56 GMT

Hi,

I will get excel file with different number of colums i.e it may contain 2 colums or 3 or......100 colums.

now I have to fill the excel sheet data to internal table regradless of the number of coloums available in the excel sheet.

Thanks in advnce......

ShaiK Shadulla.

Re: Fill Internal table from excel sheet dynamically

rainer_hbenthal — Wed, 10 Mar 2010 13:57:53 GMT

Are you getting an excel sheet or a csv file?

Re: Fill Internal table from excel sheet dynamically

venkat_o — Wed, 10 Mar 2010 14:06:35 GMT

Hi, <li>Try this way.


REPORT ztest_notepad.
DATA: BEGIN OF it_data OCCURS 0,
       bukrs TYPE t001-bukrs,
       butxt TYPE t001-butxt,
       ort01 TYPE t001-ort01,
     END OF it_data.
TYPE-POOLS:truxs.
DATA:it_tab_raw_data TYPE  truxs_t_text_data.
"Call TEXT_CONVERT_XLS_TO_SAP fm
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
 EXPORTING
   i_tab_raw_data       = it_tab_raw_data
   i_filename           = 'C:\test.xls'
 TABLES
   i_tab_converted_data = it_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.
ELSE.
 "Loop the data table  
 LOOP AT it_data.
   WRITE:/ it_data-bukrs,it_data-butxt, it_data-ort01.
 ENDLOOP.
ENDIF.

Thanks Venkat.O

Re: Fill Internal table from excel sheet dynamically

Former Member — Wed, 10 Mar 2010 14:10:55 GMT

Hi,

This can be acheveid by creating a final IT with line type string where you can place all the columns separated by comma in to internal tabel. Doesnt matter how many columns in excel.

i have given the code for the same.

DATA : IT_FILE LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA FILE_PATH LIKE RLGRAP-FILENAME.

TYPES : BEGIN OF SBU_UPLOAD ,

BU TYPE c,

BU_DESC TYPE c,

DELETE TYPE C,

END OF SBU_UPLOAD .

***Declare a variable with sring.

data: begin of it,

a type string.

end of it.

DATA TBU_UPLOAD TYPE STANDARD TABLE OF SBU_UPLOAD WITH HEADER LINE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'FILE_LOC' "Here file_LOC is the text Element name on Screen.

IMPORTING

file_name = FILE_PATH.

"In FILE_PATH you get the excel file path , ehich you want to upload in Internal table ex: E:\Prem\Premral_details.xls' and we pass this file name to the following function Module which Return back the Internal table of its own Format ."

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = FILE_PATH

I_BEGIN_COL = '1'

I_BEGIN_ROW = '3'

I_END_COL = '3'

I_END_ROW = '256'

TABLES

INTERN = IT_FILE

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC NE SPACE.

*leave PROGRAM.

ENDIF.

" Now I use the following Logic to convert that IT_FILE table into our own table format."

LOOP AT IT_FILE.

at new IT_FILE-row.

string avriable will be cleared whenever new row comes

CLEAR it-a.

endat.

here all the columns will be placed in the IT separted by comma

concatenate IT_FILE-value it-a into it-a seprated by ','.

AT END OF ROW.

"At end of row it is updated with the desired value i.e

When Row Column in table one converted from 1 to 2 , the first column in Table 2 is inserted. Which is the required format of us . "

APPEND it.

ENDAT.

endloop.

Regards,

shanmugavel Chandrasekaran.

Re: Fill Internal table from excel sheet dynamically

Former Member — Wed, 10 Mar 2010 18:08:30 GMT

Hi Shaik,

Refer to the below link:

http://divulgesap.com/blog.php?p=MjE=

This article has a sample report/code which exactly matches your requirement. You can specify the number of columns in your excel sheet in the selection screen, upload the file and get the data into an internal table.

Let me know if you have any issues.

Best Regards,

Ravikiran