Mass Flat FIle Load Automation

[Former Member]

Applies to: SAP BW/BI

Summary : This Article demonstrates the step by step process to upload multiple flat files into a Infoprovider. This reduces the manual effort.

Author: Mayuri Swarna

Created on: 23 Apr 2014

Author Bio : Mayuri Swarna has experience in SAP BI Implementation and Production Support projects

Introduction

Some times in an implementation project or in the production Support environment, we would have come across a situation where we need to load/reload multiple flat files (ex: daily historical data) received from other source system in a sequence into a data target. This loading process involves manual effort and is cumbersome. So I thought of automation of this process so that it will reduce the manual effort.

Solution Approach

Assuming that all the flat files to be loaded are residing in a predefined folder path in the SAP BW application Server, An event triggered process chain with and infopackage and DTP and at the end an ABAP will be created. In the infopackage file name routine, write the code to sort the flat files with the given mask and will take the first file and identified dynamically. In the last step of the process chain, ABAP program will move already processed flat file from the main folder to the predefined back up folder so that remaining flat files will be available for the infopackage and will trigger the same process chain by raising the same event which is used in the start variant of the same process chain.

Step by Step Approach

Infopackage

Create an infopackage and write a routine for a file name so that it will sort all the flat files with the given mask in the predefined main folder where all the flat files are residing and dynamically picks the first one and process.

The sample code as follows:

DATA: v_dir   TYPE epsf-epsdirnam VALUE '/sapdownload/FL/main/',
v_mask TYPE epsf-epsfilnam VALUE 'FLA_201403*',
it_dir_list TYPE STANDARD TABLE OF zeps2file,
wa_dir_list TYPE zeps2file.

CALL FUNCTION 'Z_GET_FILE_NAME_FOR_DIRECTORI'
EXPORTING
dir_name               = v_dir
file_mask              = v_mask
TABLES
dir_list               = it_dir_list
EXCEPTIONS
invalid_eps_subdir     = 1
sapgparam_failed       = 2
build_directory_failed = 3
no_authorization       = 4
read_directory_failed  = 5
too_many_read_errors   = 6
empty_directory_list   = 7
OTHERS                 = 8.
IF sy-subrc <> 0 .

ELSE.
SORT it_dir_list by name.
LOOP AT it_dir_list INTO wa_dir_list WHERE name CS 'FLA_201403'.
CONCATENATE v_dir wa_dir_list-name INTO p_filename.
exit.
ENDLOOP.
ENDIF.

DTP

Create a normal DTP which loads the data from PSA which is loaded by previous infopackage to the data target. Here is nothing new.

ABAP Program

Create and ABAP program which will sort all the flat files with the given mask (which is defined in the program variant) in the predefined main folder and moves the already processed flat file to another predefined backup folder. After this program will  trigger the same event which is used in the start process of the same process chain if the flat files exist in the main folder.

Sample code as follows:

  REPORT  ZBW_FILE_UP.

TYPE-POOLS : ABAP.

TYPES:TT_RESULTS          TYPE STANDARD TABLE OF BTCXPM,
TT_DIR_LIST         TYPE STANDARD TABLE OF ZEPS2FILE.

DATA: IT_DIR_LIST         TYPE STANDARD TABLE OF ZEPS2FILE,
IT_RESULTS          TYPE STANDARD TABLE OF BTCXPM,
WA_DIR_LIST         TYPE ZEPS2FILE,
V_COMMAND           TYPE SXPGCOLIST-NAME,
V_PARAMETER         TYPE SXPGCOLIST-PARAMETERS,
V_STATUS            TYPE EXTCMDEXEX-STATUS,
V_EXITCODE          TYPE EXTCMDEXEX-EXITCODE,
V_FILE_NAME         TYPE CHAR100,
V_BFILE             TYPE CHAR100,
V_MFILE             TYPE CHAR100,
V_DIR               TYPE EPSF-EPSDIRNAM,
V_MASK              TYPE EPSF-EPSFILNAM,
V_DEL_MASK          TYPE EPSF-EPSFILNAM,
lv_event TYPE string VALUE 'BW_FU',
lv_para  TYPE string VALUE 'BW_FU',
lv_inst  TYPE msxxlist-name.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-000.
PARAMETERS:P_FILE    TYPE EPSF-EPSFILNAM.
SELECTION-SCREEN END OF BLOCK B1.

DATA:C_INMAIN_FILE_PATH TYPE  CHAR1024          VALUE '/sapdownload/FL/main/',
C_INMAIN_FILE_BPATH TYPE  CHAR1024          VALUE '/sapdownload/FL/backup/',
C_FILE_MOVE_CMD    TYPE  SXPGCOLIST-NAME   VALUE 'Z_FILE_MOVE',
C_STAR             TYPE  CHAR1             VALUE '*'.

START-OF-SELECTION.

V_DIR = C_INMAIN_FILE_PATH.
V_MASK = P_FILE.

*-- Get file list from directory using the mask entered in the selection screen
PERFORM GET_DIR USING V_DIR
V_MASK
CHANGING IT_DIR_LIST.

*-- Remove the wildcard to get the file name from the list of files
REPLACE ALL OCCURRENCES OF c_star IN V_MASK WITH space.

SORT IT_DIR_LIST BY NAME.

LOOP AT IT_DIR_LIST INTO WA_DIR_LIST WHERE NAME CS V_MASK.
V_FILE_NAME = WA_DIR_LIST-NAME.
EXIT.
ENDLOOP.

CONCATENATE C_INMAIN_FILE_PATH
V_FILE_NAME INTO V_MFILE.
CONCATENATE C_INMAIN_FILE_BPATH
V_FILE_NAME INTO V_BFILE.

V_COMMAND = C_FILE_MOVE_CMD.
CONCATENATE V_MFILE V_BFILE INTO V_PARAMETER SEPARATED BY SPACE.

*Move file from main folder to backup folder
PERFORM CHNG_FILE USING V_COMMAND
V_PARAMETER
CHANGING V_STATUS
V_EXITCODE
IT_RESULTS.

*Raise the event
CALL FUNCTION 'BP_EVENT_RAISE'
EXPORTING
eventid                = lv_event
eventparm              = lv_para
target_instance        = lv_inst
EXCEPTIONS
bad_eventid            = 1
eventid_does_not_exist = 2
eventid_missing        = 3
raise_failed           = 4
OTHERS                 = 5.
IF sy-subrc <> 0.
MESSAGE s000(z1) WITH 'Event does not exist' DISPLAY LIKE 'E'.
ENDIF.

*----------------------------------------------------------------------*
FORM GET_DIR  USING    IM_DIR  TYPE EPSF-EPSDIRNAM
IM_MASK TYPE EPSF-EPSFILNAM
CHANGING CH_IT_DIR_LIST TYPE TT_DIR_LIST.

CALL FUNCTION 'Z_GET_FILE_NAME_FOR_DIRECTORI'
EXPORTING
DIR_NAME               = IM_DIR
FILE_MASK              = IM_MASK
TABLES
DIR_LIST               = CH_IT_DIR_LIST
EXCEPTIONS
INVALID_EPS_SUBDIR     = 1
SAPGPARAM_FAILED       = 2
BUILD_DIRECTORY_FAILED = 3
NO_AUTHORIZATION       = 4
READ_DIRECTORY_FAILED  = 5
TOO_MANY_READ_ERRORS   = 6
EMPTY_DIRECTORY_LIST   = 7
OTHERS                 = 8.
IF SY-SUBRC NE 0 .
MESSAGE E001(Z1).
ENDIF.

ENDFORM.                    " GET_DIR

*&---------------------------------------------------------------------*
*&      FORM  CHNG_FILE

*&---------------------------------------------------------------------*
FORM CHNG_FILE  USING    IM_COMMAND   TYPE SXPGCOLIST-NAME
IM_PARAMETER TYPE SXPGCOLIST-PARAMETERS
CHANGING CH_STATUS    TYPE EXTCMDEXEX-STATUS
CH_EXITCODE  TYPE EXTCMDEXEX-EXITCODE
CH_IT_RESULTS TYPE TT_RESULTS.

CALL FUNCTION 'SXPG_CALL_SYSTEM'
EXPORTING
COMMANDNAME                = IM_COMMAND
ADDITIONAL_PARAMETERS      = IM_PARAMETER
IMPORTING
STATUS                     = CH_STATUS
EXITCODE                   = CH_EXITCODE
TABLES
EXEC_PROTOCOL              = CH_IT_RESULTS
EXCEPTIONS
NO_PERMISSION              = 1
COMMAND_NOT_FOUND          = 2
PARAMETERS_TOO_LONG        = 3
SECURITY_RISK              = 4
WRONG_CHECK_CALL_INTERFACE = 5
PROGRAM_START_ERROR        = 6
PROGRAM_TERMINATION_ERROR  = 7
X_ERROR                    = 8
PARAMETER_EXPECTED         = 9
TOO_MANY_PARAMETERS        = 10
ILLEGAL_COMMAND            = 11
OTHERS                     = 12.
IF SY-SUBRC <> 0.
MESSAGE E002(Z1).
ENDIF.

ENDFORM. 

Process Chain

Create an event and event triggered triggered process chain as follows.

Execution Process

Just trigger the process chain by executing the event which is defined in the start variant of the process chain. It will automatically take the first file in the main folder and process it till data target and moves to the backup folder and repeats until no more files exist in the folder.

Before Execution:

Main Folder

Backup folder

After Execution:

Main Folder

Backup Folder

Advantages

• Reduces the manual effort when we have huge no of files to load

Assumptions/Limitations

• All the flat files data is already validated. We can also include a new data validation step just before infopackage execution.