topic Re: Function module to get data into internal table from Excel file sheets in Application Development and Automation Discussions

Function module to get data into internal table from Excel file sheets

Noorie — Thu, 04 Mar 2010 10:23:17 GMT

Hi,

I have to upload customers from excel file.

we are donloading customer data excel file sheets.

Customer data in 1 sheet, tax data the other sheet of same excel file, Customer master-Credit data in other sheet of same excel file.

so i have 3-4 sheet in one excel file.

now my requirement is to get the data from excel file into internal table.

is there any function module.

Thanks & Regards

Re: Function module to get data into internal table from Excel file sheets

Former Member — Thu, 04 Mar 2010 10:26:55 GMT

Hi,

Try using the function module ALSM_EXCEL_TO_INTERNAL_TABLE.

Regards,

Sharin.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Thu, 04 Mar 2010 10:50:54 GMT

hey are you uploading the file from presentation or application server??

Re: Function module to get data into internal table from Excel file sheets

kesavadas_thekkillath — Thu, 04 Mar 2010 11:23:40 GMT

Check the class CL_UA_EXCEL_TOOLS

You can get a lot if you search in SCN wiki and article section

Re: Function module to get data into internal table from Excel file sheets

Former Member — Thu, 04 Mar 2010 13:43:57 GMT

I am sending you the idea with an example how you can upload data from an EXCEL file into an internal table. I am not sure if you can take data from different sheet in the same EXCEL file. I think that this is not possible (try it )

Upload the data into an internal table, like the way that I am describing in the above:

----

DATA: L_MAX_COL_NB TYPE I.

DATA: l_file_name LIKE RLGRAP-FILENAME.

Just to be sure that is the correct type for the FM.

l_file_name = P_FILE_NAME.

L_MAX_COL_NB = 58. "Maximum nb of colums that the FM can read.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = l_file_name

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = L_MAX_COL_NB

I_END_ROW = 9999

TABLES

INTERN = PT_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

ENDIF.

----

Now you should upload the data into your own itab. The Function Module will return to you all the an itab

from all fields and columns. Define the structure of the uploading file into SE11 - Data Dictionary. Then read the fieldcatalog of this structure. In the code that I am sending to you, I am insearting an empty line into the internal table and then I am assigning this line into a corresponding field-symbol. Then I am able to change the working area - so and the line of the itab. Propably you could you the statement APPEND INITIAL LINE TO (your_table_name) ASSIGNING <your_field_symbol>, but the example was written in an old SAP version.

FIELD-SYMBOLS:

<F_REC> LIKE WA_UPLOAD_FILE, "working are of the uploading file

<F_FIELD> TYPE ANY.

DATA: COLUMN_INT TYPE I,

C_FIELDNAME(30) TYPE C.

PERFORM GET_FIELDCATOLG TABLES FIELDCAT

USING 'ZECO_CHARALAMBOUS_FILE'.

LOOP AT PT_EXCEL.

AT NEW ROW.

ASSIGN WA_UPLOAD_FILE TO <F_REC>.

ENDAT.

COLUMN_INT = PT_EXCEL-COL.

READ TABLE FIELDCAT INTO WA_FIELDCAT INDEX COLUMN_INT.

CONCATENATE '<F_REC>-' WA_FIELDCAT-FIELDNAME INTO C_FIELDNAME.

ASSIGN (C_FIELDNAME) TO <F_FIELD>.

<F_FIELD> = PT_EXCEL-VALUE.

AT END OF ROW.

APPEND WA_UPLOAD_FILE TO GT_UPLOAD_FILE.

CLEAR WA_UPLOAD_FILE.

ENDAT.

ENDLOOP.

----

With Regards

George

Edited by: giorgos michaelaris on Mar 4, 2010 3:44 PM

Re: Function module to get data into internal table from Excel file sheets

Former Member — Fri, 05 Mar 2010 05:29:32 GMT

Hi..

Try with fm " GUI_UPLOAD".

Regards,

Lokeswari.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Fri, 05 Mar 2010 09:16:14 GMT

This message was moderated.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Fri, 05 Mar 2010 09:39:03 GMT

Hi Use one of these function modules

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = p_bcol "1

i_begin_row = p_brow

i_end_col = p_ecol "3

i_end_row = p_erow

TABLES

intern = it_exload

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.

ELSE.

LOOP AT it_exload INTO wa_exload.

CASE wa_exload-col.

WHEN '0001'.

wa_final-abc = wa_exload-value.

WHEN '0002'.

wa_final-xyz = wa_exload-value.

WHEN '0003'.

wa_final-pqh = wa_exload-value.

ENDCASE.

AT END OF row.

APPEND wa_final TO it_final.

CLEAR wa_final.

ENDAT.

ENDLOOP.

ENDIF.

Alternate

DATA: it_raw TYPE truxs_t_text_data.

FM : TEXT_CONVERT_XLS_TO_SAP

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 = p_file

TABLES

i_tab_converted_data = record "ACTUAL DATA

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Fri, 05 Mar 2010 09:42:10 GMT

hi,

try the below code.

DATA: itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE. 

*get the data into first internal table 
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' 
EXPORTING 
filename = p_excel "excel file name 
i_begin_col = '1' 
i_begin_row = '1' 
i_end_col = '9' 
i_end_row = '850' 
TABLES 
intern = itab "internal table where u need to load the 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. 

*put the data into proper columns 

CLEAR g_maxrow. 
CLEAR g_maxcol. 
CLEAR itab. 
CLEAR g_currow. 
CLEAR g_curcol. 

LOOP AT itab. 

DESCRIBE TABLE itab LINES g_n. 
g_maxrow = g_n / 9. 

IF itab-col GT g_maxcol. 
g_maxcol = itab-col. 
ENDIF. 

CLEAR itab. 

ENDLOOP. 

g_currow = 0001. 
g_curcol = 0001. 


LOOP AT itab. 
IF itab-row NE g_currow. 
APPEND wa_temp TO it_temp. 
CLEAR wa_temp. 
g_currow = g_currow + c_one. 
ENDIF. 

CASE itab-col. 
WHEN '0001'. 
wa_temp-follow_up = itab-value. 
WHEN '0002'. 
wa_temp-plant = itab-value. 
WHEN '0003'. 
wa_temp-iss_st_loc = itab-value. 
WHEN '0004'. 
wa_temp-mrp_type = itab-value. 
WHEN '0005'. 
wa_temp-minlotsize = itab-value. 
WHEN '0006'. 
wa_temp-maxlotsize = itab-value. 
WHEN '0007'. 
wa_temp-lotsizekey = itab-value. 
WHEN '0008'. 
wa_temp-reorder_pt = itab-value. 
WHEN '0009'. 
wa_temp-inhseprodt = itab-value. 
ENDCASE. 

ENDLOOP. 

IF itab-row EQ g_maxrow. 
APPEND wa_temp TO it_temp. 
ENDIF.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Fri, 05 Mar 2010 10:24:57 GMT

Function module FILE_READ_AND_CONVERT_SAP_DATA uploads into internal table.

or u can use the following function module:

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_infile

i_begin_col = 1

i_begin_row = 2

i_end_col = 10

i_end_row = 64000

TABLES

intern = i_excel

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.

Edited by: divya@ritam on Mar 5, 2010 11:30 AM

Re: Function module to get data into internal table from Excel file sheets

Former Member — Fri, 05 Mar 2010 11:17:26 GMT

Use this snippet according to your requirement ::



TYPE-POOLS : truxs.


DATA : w_filename(128).

DATA :  git_raw TYPE truxs_t_text_data.

 CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*      I_FIELD_SEPERATOR          =
*     i_line_header               = 'X'
      i_tab_raw_data             = git_raw
      i_filename                 = w_filename
    TABLES
      i_tab_converted_data       = gt_upldcoep
*    EXCEPTIONS
*      CONVERSION_FAILED          = 1
*      OTHERS                     = 2
            .
  IF sy-subrc <> 0.

    MESSAGE 'EXCEL was not read' TYPE 'E' DISPLAY LIKE 'I'.

  ENDIF.

Regards

Mishra

Re: Function module to get data into internal table from Excel file sheets

Noorie — Sat, 06 Mar 2010 10:42:09 GMT

Hi,

Dear Firends, Thanks a lot for you replies.

By all the function modules like TEXT_CONVERT_XLS_TO_SAP, FILE_READ_AND_CONVERT_SAP_DATA,GUI_UPLOAD etc

are to read data from excel file if you have one sheet. My requirement is to read multipul sheets in just one excel file

Is there any function module with which we can read just one excel file with multipul sheets init.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Sat, 06 Mar 2010 13:05:42 GMT

Hi,

Probably u can check a few of the below links

http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm

http://www.sap-img.com/abap/abap-object-oriented-spreadsheet-with-unlimited-power.htm

Let me know if this is the one that is required.

Re: Function module to get data into internal table from Excel file sheets

Former Member — Sat, 06 Mar 2010 13:13:05 GMT

Hi Noorie,

You need to use OLE technique to read multiple excelsheets.

Please check below link :

Regards,

Nisha Vengal.