Dear community,
I believe we have all been there. We want to read an excel file with some data in a table, and each time we look around for an example (or for the last report where we needed this), and each time we are faced with the same tedious tasks, which include but are not limited to:
- You need to create the dialog to the user and limit it to the extension that you are able to read, making it very limited
- You need to create a char like structure because the excel data will be imported in external format.
- You need to take care of the conversion of each field (unless if it's just text), and many times you make assumptions in this conversion that are not always true (like the decimal separator, the date format...)
Now, what if it was possible to have this all done for you automatically??
Sounds like a dream? Well, now it's reality.
You can find it here:
Project Object - File Reader
Please check the Readme file for installation instructions.
Sounds interesting, how does it work?
Well, I'm glad you ask. This file reader uses the abap runtime type description functionalities extensively.
All you need to do is call the READ_FILE method of the class and provide it with the internal table where you want the data to be imported to. If you want to skip the dialog prompting for a file, you can use the importing parameter for the file path. There's also the classic optional "skip first line" parameter, if the user insists on uploading the file with the header line.
The "reader" is split into the next steps:
- Create a char like table automatically from the provided internal table. All fields will be type char with a length of 50 characters. If for some reason this is not sufficient for you, I have put a BAdI in place for you to override this.
- The "reader" will get the extension from the file path provided and will call the BAdI implementation for the respective extension. At the moment there are implementations for extensions CSV, XLS, XLSX, and now XML! Please keep in mind that to read XLSX files you'll need to install abap2xlsx. More info on that here abap2xlsx
- The "reader" will convert the char like table into the internal format following the pattern: It will call a BAdI to allow the developer to override the default conversion per data element. If this BAdI is not implemented, it will check for a conversion routine. If the data element does not have a conversion routine, it will run a standard conversion algorithm (implemented in a BAdI) per data type.
This does a lot, but I need more, how can I enhance it?
I created the following BAdIs that allow you to enhance this file reader to best fit your needs:
- Z_BD_FILE_READER_COL_TYPE - This BAdI allows you to override the data type used in the char like structure. Basically, if you need more than 50 characters, use this.
- Z_BD_FILE_READER_CONV - This BAdI allows you to override the default conversion algorithm, in case you need to convert some data in a special way. You need to use filter F_ROLLNAME by providing the data element of the column you wish to override.
- Z_BD_FILE_READER_EXECUTE - This BAdI allows you to create a new extension reader. As I mentioned, I have already implemented a reader for CSV, XLS and XLSX files. If you need another extension, you can implement this BAdI yourself. Feel free to share it with me if you want to contribute :smile:
- Z_BD_FILE_READER_TYPE_CONV - If you don't override the default conversion, and if there's no SAP standard conversion routine, I have implemented a few conversion routines for the most "popular" types. These are text, date and numbers. The date conversion routine accepts pretty much every format (dd/mm/yyyy, yyyy/mm/dd, yyyymmdd...) and the number routine gets the format (decimal and thousands separator) from the user options, so as long as it matches the format in the file, it should work. If you encounter some fancy data type you'll have to implement the conversion yourself. If you feel like contributing, feel free to share :smile:
Some credit where credit is due
I'd like to thank Aaron Pennington for his help and inspiration, and I'd like to thank Christian Skoda also for his help and inspiration, and for being the installation manual tester :smile:
Of course, I'd like to thank as well the abap2xlsx team for sharing their xlsx reader.
Any comments or suggestions are appreciated as usual. If you have any questions, feel free to ask.
Best,
Bruno
EDIT 01.02.2015: Now the file reader also reads XML files. One example is attached for your reference. Also, as kevin.barter3 suggested, import parameter IM_FIRST_ROW replaced parameter IM_SKIP_FIRST_LINE, to give you more control. Beware this update is NOT backward compatible.
