Excel Upload with Custom Validations

Hi community! This blog post is going to demo how to upload excel files from server(AL11) and local pc. This code is meant to be reused and modified to fit your own requirement in every project.

Purpose:

1. To create a code that can have different validations for different programs, without changing the core logic of the upload function.
2. To create a code that is reusable for every project.
3. To minimize the errors happen in data uploading.
4. To make it easy to use excel upload function.

Introduction:

This part will explain the entire concept of the upload part and validation part. In short, this code take advantage of the object oriented overriding(redefinition) concept to do custom validation.

Flow chart:

Figure 1: Flow chart

The above flow chart shows the concept of the entire upload process.

Step 1: Determine source of data.

Step 2: Read the excel data and convert to xString.

Step 3: Extract the data and stored in internal table for further processing.

Step 4: Validate data in internal table. Validation is very important in this process. There are two types of validations:

1. Data type/format validation: This is to ensure the data uploaded from excel file is compatible with the file structure in the program. Methods such as CHECK_TYPE_P, CHECK_TYPE_I and so on are the methods to validate the data is in correct format. For example, if user uploads a non numeric value to type P variable, then method CHECK_TYPE_P will handle the exception and conversion dump, then return error.
2. Data accuracy validation: This validation needs to be implemented in the caller program. This is to ensure the data uploaded from excel file is correct in term of business requirement. For instance, if there is no company code 1100 in the system, but user uploads this value to the program, then it will return error.

The validation refers to SAP Documentation-conversion rules.

How to use:

Step 1 : This is a class program. Create a local class to inherit from parent class, then create an instance to call the methods.

Figure 2: Sample code to use the upload method

Step 2 : Redefine method to implement custom logic in validation. Otherwise, it will follow the standard validation.

Figure 3: Redefined method and custom validation method

Step 3 : Handle the output of the upload method. There are three object returned from the method: Successful upload internal table, Error object, Message. Handle the data returned and do custom logic after it.

Source Code:

The source code is on GitHub. Please refer to this link.

Sample Excel File:

Summary:

This code is meant to be reused at any other project, and thanks to the concept of object oriented redefinition concept, it brings a flexibility to add any custom logic to the validation and at the same time it does not change the core logic of the upload function. Error handling is another key point here, minimize the error by handling exception.

This is the end of the blog post. Let me know if you have any Enjoy coding!