Technical Articles
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:
- To create a code that can have different validations for different programs, without changing the core logic of the upload function.
- To create a code that is reusable for every project.
- To minimize the errors happen in data uploading.
- 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:
- 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.
- 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!
Thank you. That would be nice if you could clean up your blog post, by storing your code in a Git public project (github for instance). And a good occasion to learn abapGit.
Hi Sandra,
Thank you for your advice. I am planning to do that in the future.
I would also advocate investigating ABAP2XLSX.
Whilst that is mostly used for exporting ABAP data to Excel, it also works the other way around, and I find it a much better way to upload Excel files into ABAP as I get meta information about the spreadsheet such as some power user "improved" the spreadsheet by adding an unexpected column or putting some blank rows at the top.
Cheersy Cheers
Paul
Hi Paul,
Thank you for your suggestion!