- SAP Managed Tags:
I have developed an FM 'Y_EXCEL_TO_ITAB ', which takes an excel file name and a structure/table name as inputs. And parses the excel content into an internal table with the same structure given as input.
Background:
SAP has provided us the means to create data at run-time. We can use various RTTS (Run Time Type Services), Data references, Field-Symbols to achieve this.
So, we utilize this to develop a general purpose FM, which can read any excel file and put it in an internal table of the specified structure.
Applications:
Potential Enhancements:
Pre-requisites:
Others' code used:
The FM 'Y_EXCEL_TO_ITAB'
Import/Export parameters:
Exceptions:
Questions:
Hope you find this post useful. If you have any questions do let me know. You may utilize the below URL for this.
https://answers.sap.com/index.html
Code:
Below report shows how the FM could be triggered and used:
Sample Excel File:
Sample Report Input:
Sample FM Output:
Updated FM and the caller program to consider the possibility MANDT may not be part of the excel.
Background:
SAP has provided us the means to create data at run-time. We can use various RTTS (Run Time Type Services), Data references, Field-Symbols to achieve this.
So, we utilize this to develop a general purpose FM, which can read any excel file and put it in an internal table of the specified structure.
Applications:
- Having a general/common FM to retrieve data from an excel file into any internal table following the specified structure.
- With just the path of the file and the target structure, this FM can be re-used in any place where the data from excel needs to be dumped into an internal table.
Potential Enhancements:
- The functionality can be extended to actually update the excel content to DB tables (with just a few lines of code. Example provided.)
- Clearer error messages with message variables
- More validations and messages
Pre-requisites:
- The format of data in the excel should match the format settings of the SAP user executing the FM. e.g., if the date format is dd.mm.yyyy in SU01 for the user, the excel should have date values in the same format.
- If just part of the fields of a given structure are available in the excel, make sure to label the columns with the correct field names
- If no header is specified with the field names, make sure the ordering of fields in the excel are the same as the ordering of the fields in the table/structure definition
Others' code used:
- The code used in conversion exit FM 'Y_CONVERSION_EXIT_DECS_INPUT', is the exact same as the one in the below URL, created by Matthew Billingham (Thanks)
- https://wiki.scn.sap.com/wiki/display/ABAP/Conversion+from+external+to+internal+number+format
The FM 'Y_EXCEL_TO_ITAB'
Import/Export parameters:
- iv_filename:
Path of the excel file.
- iv_structure:
The structure of the target internal table
- iv_hdr_avl:
Does the excel sheet have field names as headers.
'X' - Yes
'' - No
This could be used when the actual structure has N fields, but the excel has <N fields/columns.
- iv_mandt_avl:
Does the excel sheet have MANDT passed, in case headers are not provided.
'X' - Yes, MANDT is part of the excel
'' - No, MANDT is not part of the excel
- ct_return_table:
Generic internal table. Will contain the output, in an internal table, with same structure as iv_structure
Exceptions:
- structure_not_found - The structure '<iv_structure>' could not be found
- field_not_found - There is no field in the structure for a field name in the header. Eg., If i mention field name 'CARRY_FORWARD' in header, and pass 'EABL' as structure. But there is no field called CARRY_FORWARD in structure EABL.
Questions:
Hope you find this post useful. If you have any questions do let me know. You may utilize the below URL for this.
https://answers.sap.com/index.html
Code:
FUNCTION y_excel_to_itab.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" REFERENCE(IV_FILENAME) TYPE LOCALFILE
*" REFERENCE(IV_STRUCTURE) TYPE TABNAME
*" REFERENCE(IV_MANDT_AVL) TYPE CHAR01 DEFAULT ''
*" REFERENCE(IV_HDR_AVL) TYPE CHAR01 DEFAULT 'X'
*" CHANGING
*" REFERENCE(CT_RETURN_TABLE) TYPE TABLE
*" EXCEPTIONS
*" STRUCTURE_NOT_FOUND
*" FIELD_NOT_FOUND
*"----------------------------------------------------------------------
DATA: lt_dd_field_list TYPE STANDARD TABLE OF dfies,
lt_dyn_tab TYPE REF TO data,
lt_excel_itab_pre_conv TYPE STANDARD TABLE OF alsmex_tabline,
ls_excel_cell TYPE alsmex_tabline,
ls_excel_cell_hdr TYPE alsmex_tabline,
ls_dd_field TYPE dfies,
ls_dyn_line TYPE REF TO data,
lv_start_row TYPE i,
lv_conv_exit_fm_name TYPE string,
lv_min_col TYPE i,
lv_min_row TYPE i,
lv_max_col TYPE i,
lv_max_row TYPE i,
lv_file_name TYPE dbmsgora-filename,
lv_file_type TYPE sdbad-funct,
lv_col_num_inc TYPE tabfdpos.
FIELD-SYMBOLS: <lfs_dyn_tab> TYPE table,
<lfs_dyn_line> TYPE any,
<lfs_dyn_cell> TYPE any.
CONSTANTS: lc_min_col_xlsx TYPE i VALUE 1,
lc_min_row_xlsx TYPE i VALUE 1,
lc_max_col_xlsx TYPE i VALUE 16834,
lc_max_row_xlsx TYPE i VALUE 1048576,
lc_min_col_xls TYPE i VALUE 1,
lc_min_row_xls TYPE i VALUE 1,
lc_max_col_xls TYPE i VALUE 256,
lc_max_row_xls TYPE i VALUE 65536,
lc_fil_typ_xls TYPE string VALUE 'XLS',
lc_fil_typ_xlsx TYPE string VALUE 'XLSX'.
IF iv_filename IS NOT INITIAL.
lv_file_name = iv_filename.
"Find the file extenstion
CALL FUNCTION 'SPLIT_FILENAME'
EXPORTING
long_filename = lv_file_name
IMPORTING
* PURE_FILENAME =
pure_extension = lv_file_type.
TRANSLATE lv_file_type TO UPPER CASE.
"Row, Column limits depends on file type
IF lv_file_type = lc_fil_typ_xls.
lv_min_col = lc_min_col_xls.
lv_min_row = lc_min_row_xls.
lv_max_col = lc_max_col_xls.
lv_max_row = lc_max_row_xls.
ELSEIF lv_file_type = lc_fil_typ_xlsx.
lv_min_col = lc_min_col_xlsx.
lv_min_row = lc_min_row_xlsx.
lv_max_col = lc_max_col_xlsx.
lv_max_row = lc_max_row_xlsx.
ELSE.
ENDIF.
"Retrieve the excel file content
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = iv_filename
i_begin_col = lv_min_col
i_begin_row = lv_min_row
i_end_col = lv_max_col
i_end_row = lv_max_row
TABLES
intern = lt_excel_itab_pre_conv
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
* Implement suitable error handling here
ELSE.
ENDIF.
ENDIF.
"Get the list of fields in the structure
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname = iv_structure
TABLES
dfies_tab = lt_dd_field_list
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
* Implement suitable error handling here
RAISE structure_not_found.
ENDIF.
SORT lt_excel_itab_pre_conv
BY row ASCENDING
col ASCENDING.
"If there are field names in headers, they should match the field names in the structure
IF iv_hdr_avl = abap_true.
"For each Field name in header
LOOP AT lt_excel_itab_pre_conv INTO ls_excel_cell
WHERE
row = 1.
CONDENSE ls_excel_cell-value.
TRANSLATE ls_excel_cell-value TO UPPER CASE.
"Check if it is valid
READ TABLE lt_dd_field_list TRANSPORTING NO FIELDS
WITH KEY
fieldname = ls_excel_cell-value.
IF sy-subrc <> 0.
RAISE field_not_found.
ENDIF.
ENDLOOP.
"If there is a header, data is available from second line
lv_start_row = 2.
ELSE.
IF iv_mandt_avl = abap_false.
"If MANDT is part of the structure, but not passed in the excel
"Do not attempt to read it
DELETE lt_dd_field_list WHERE fieldname = 'MANDT'.
IF sy-subrc = 0.
"If MANDT field was available in the structure, and deleted
"Make sure, we increment the column counters
lv_col_num_inc = 1.
ENDIF.
ENDIF.
"If there is no header, data is available from first line
lv_start_row = 1.
ENDIF.
"Generate work area
CREATE DATA ls_dyn_line TYPE (iv_structure).
"Generate table
CREATE DATA lt_dyn_tab TYPE TABLE OF (iv_structure).
ASSIGN lt_dyn_tab->* TO <lfs_dyn_tab>.
"Loop through the list of cells
LOOP AT lt_excel_itab_pre_conv INTO ls_excel_cell
WHERE
row >= lv_start_row.
"Assign the Work area to Field symbol
ASSIGN ls_dyn_line->* TO <lfs_dyn_line>.
CONDENSE ls_excel_cell-value. "Strip leading and trailing spaces from value
"If field names are available in headers
IF iv_hdr_avl = abap_true.
"Find the name of the field
READ TABLE lt_excel_itab_pre_conv INTO ls_excel_cell_hdr
WITH KEY
row = 1
col = ls_excel_cell-col.
IF sy-subrc = 0.
CONDENSE ls_excel_cell_hdr-value."Strip leading and trailing spaces from field name
TRANSLATE ls_excel_cell_hdr-value TO UPPER CASE.
ASSIGN COMPONENT ls_excel_cell_hdr-value OF STRUCTURE <lfs_dyn_line> TO <lfs_dyn_cell>."Assign the destination field of the work area
"Get the field details, based on field name
READ TABLE lt_dd_field_list INTO ls_dd_field
WITH KEY
fieldname = ls_excel_cell_hdr-value.
ENDIF.
ELSE.
"In case there is MANDT in structure, but not in excel, offset the column number by 1
ADD lv_col_num_inc TO ls_excel_cell-col.
"Get the field details, based on position
READ TABLE lt_dd_field_list INTO ls_dd_field
WITH KEY
position = ls_excel_cell-col.
ASSIGN COMPONENT ls_excel_cell-col OF STRUCTURE <lfs_dyn_line> TO <lfs_dyn_cell>."Assign the destination field of the work area
ENDIF.
IF ls_dd_field IS INITIAL.
ELSE.
"If a conversion exit is specified, use it
IF ls_dd_field-convexit IS NOT INITIAL.
CONCATENATE 'CONVERSION_EXIT_' ls_dd_field-convexit '_INPUT' INTO lv_conv_exit_fm_name.
CALL FUNCTION lv_conv_exit_fm_name
EXPORTING
input = ls_excel_cell-value
IMPORTING
output = <lfs_dyn_cell>.
ELSE.
"Writing separate logic for DATE and Decimal fields with no conversion exits
"If needed add other fields with their respective conversion logic in this branch
IF ls_dd_field-datatype = 'DATS'.
CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = ls_excel_cell-value
IMPORTING
date_internal = <lfs_dyn_cell>
EXCEPTIONS
date_external_is_invalid = 1
OTHERS = 2.
ELSEIF ls_dd_field-datatype = 'DEC'.
"Below FM Code Courtesy:Created by Matthew Billingham, last modified on Sep 17, 2010
"URL:https://wiki.scn.sap.com/wiki/display/ABAP/Conversion+from+external+to+internal+number+format
CALL FUNCTION 'Y_CONVERSION_EXIT_DECS_INPUT'
EXPORTING
input = ls_excel_cell-value
IMPORTING
output = <lfs_dyn_cell>
EXCEPTIONS
date_external_is_invalid = 1
OTHERS = 2.
ELSE.
<lfs_dyn_cell> = ls_excel_cell-value.
ENDIF.
ENDIF.
ENDIF.
AT END OF row.
APPEND <lfs_dyn_line> TO <lfs_dyn_tab>.
ENDAT.
ENDLOOP.
ct_return_table[] = <lfs_dyn_tab>.
ENDFUNCTION.Below report shows how the FM could be triggered and used:
*&---------------------------------------------------------------------*
*& Report Y_EXCEL_TO_ITAB
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT y_excel_to_itab.
PARAMETERS: p_file LIKE rlgrap-filename,
p_str TYPE tabname,
p_hdr as CHECKBOX,
p_cli as CHECKBOX.
START-OF-SELECTION.
DATA: lt_dyn_tab TYPE REF TO data.
FIELD-SYMBOLS : <lfs_dyn_tab> TYPE ANY TABLE.
IF p_file IS NOT INITIAL.
"Generate table
CREATE DATA lt_dyn_tab TYPE TABLE OF (p_str).
ASSIGN lt_dyn_tab->* TO <lfs_dyn_tab>.
CALL FUNCTION 'Y_EXCEL_TO_ITAB'
EXPORTING
iv_filename = p_file
iv_structure = p_str
IV_MANDT_AVL = p_cli
IV_HDR_AVL = p_hdr
CHANGING
ct_return_table = <lfs_dyn_tab>
EXCEPTIONS
structure_not_found = 1
field_not_found = 2
OTHERS = 3.
IF sy-subrc <> 0.
* Implement suitable error handling here
ELSE.
"Do what you must with the converted data
***** ">>>If needed the below line could be used to update the data to DB
***** MODIFY (p_str) FROM TABLE <lfs_dyn_tab>.
***** "<<<
ENDIF.
ENDIF.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = syst-cprog
dynpro_number = syst-dynnr
field_name = ' '
IMPORTING
file_name = p_file.Sample Excel File:
Sample Report Input:
Sample FM Output:
- Update 1-4th February 2020
Updated FM and the caller program to consider the possibility MANDT may not be part of the excel.
10 Comments
