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:

  1. 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.
  2. 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
  3. 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 🙂
  • 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 🙂

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 🙂

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 Barter suggested, import parameter IM_FIRST_ROW replaced parameter IM_SKIP_FIRST_LINE, to give you more control. Beware this update is NOT backward compatible.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Kevin Barter

    What great timing.  I was just going to write such a class this morning when I got back from breakfast.

    So far it looks like it will work perfectly.

    Thanks,

    Kevin

    (0) 
    1. Bruno Esperança Post author

      Haha, you’re welcome 🙂

      Give it a try and let me know what you think. The installation procedure could be easier, but SAPLINK seems to have a “chicken and the egg” issue with enhancement spots. If you have trouble let me know.

      Best,

      Bruno

      (0) 
      1. Kevin Barter

        Hi Bruno,

        I had a requirement to skip multiple lines at the start of a file, not just the first line).  I added a parameter to the interface, and some code to the reader classes to make this work.

        I would like to provide you with the code changes I made in case you would like to incorporate the changes (or have a better way of doing it).

        What is the best way to send the changes to you?

        Thanks,

        Kevin

        (0) 
        1. Bruno Esperança Post author

          Hi Kevin!

          Does that mean you have installed everything and got it to work? What do you think of it so far?

          I’ll add you on SCN and we can discuss it privately 🙂 Thanks!

          Best regards,

          Bruno

          (0) 
          1. Kevin Barter

            Everything installed fine, and the class is being used in my program.  No issues on the install, and it is really easy to use.

            Thanks for sharing the code.

            Kevin

            (0) 
            1. Bruno Esperança Post author

              Great, cool!

              If eventually you need to improve some of the conversions or if you implement a new extension reader, let me know 🙂

              Best regards,

              Bruno

              (0) 
  2. Stephen Herlick

    Hi Bruno

    I tried to install it, but I get an error:

    Start import of nugget FILE_READER_PART2

    There is no installed SAPlink plugin for object type ENHS

    I am using version 0.1.4 of SAPLink

    Stephen

    (0) 
    1. Bruno Esperança Post author

      Hi Stephen!

      First of all, thanks for showing interest in trying this! I will appreciate your feedback on it 🙂

      You need to update your SAPLINK with all the plugins. To be honest, it’s really hard to find the right files for SAPLINK, but let me help you 🙂

      The most recent version of SAPLINK is here:

      https://www.assembla.com/code/saplink/subversion/nodes/389/trunk/build

      The nugget containing all the plugins in the most recent version is here:

      https://www.assembla.com/code/saplink-plugins/subversion/nodes/127/build

      If you need further help let me know.

      Best,

      Bruno

      (0) 
      1. Stephen Herlick

        Hi Bruno;

        Thank you for the links.  I have updated my SAPLINK program and installed your Nuggets.  When I run the program ZZZ_TEST_XLS_READER, I get an error:

        Class ZCL_FILE_READER “Field” “LINES(” is unknown.

        In SE80 when I do a syntax check on class ZCL_FILE_READER, it seems to be in method GET_FILE_EXTENSION, Line 8 “INDEX lines(.

        Note, I am on SAP ECC 6.0 EHP4.  Could this be the issue?

        Stephen

        (0) 
        1. Bruno Esperança Post author

          Hi Stephen!

          Yes, it looks like your system doesn’t have the LINES function. I use this to get the extension of a file. Basically, you will need to replace that with the “classic” DESCRIBE TABLE statement and get the number of lines into a variable, and then put that variable where the LINES function is.

          If you need further help let me know, I will give you the code!

          Best regards,

          Bruno

          (0) 
          1. Bruno Esperança Post author

            I also think you can avoid declaring a temporary variable because the number of lines in a table gets populated in some SYST field when you use the DESCRIBE TABLE statement, but I don’t remember which one exactly right now 🙂

            (0) 
            1. Stephen Herlick

              It seems that the version of SAP that I am on doesn’t like the following type of syntax:

              IF zcl_xls_reader=>translate_to_upper( zcl_xls_reader=>get_file_extension( filename_str ) ) <> zcl_xls_reader=>xls_ext.


              I broke this up into individual lines and got it working. 


              We are working on our upgrade.


              Thank you for uploading this code.  I see lots of potential uses for it.



              Stephen

              (0) 
              1. Bruno Esperança Post author

                Stephen,

                I have updated the Readme file with the issues you pointed out, and gave you credit for it.

                Thanks again for your feedback!

                Best regards,

                Bruno

                (0) 
  3. Dmitry Kostin

    Hello, I’m trying to install your application.

    When I import nugg3 file, i get the error: “The type “ZCL_EXCEL” is unknown.;”.

    What Im doing wrong?

    (0) 
    1. Bruno Esperança Post author

      Hi Dmitry!

      Class ZCL_EXCEL is needed to read .XLSX files. If you need this, please follow the link above to project abap2xlsx and install it.

      Otherwise, I think you can simply “activate anyway” and the reader will still work for the remaining type of files.

      Let me know if you have any trouble!

      Kind regards,

      Bruno

      (0) 
  4. Shai Sinai

    Good job!

    Thanks for sharing your work.

    Few points I would like to add:

    1. You may use standard FM RS_DS_CONV_IN_2_EX to convert external value to internal value according to its’ Data element.

    2. Moreover, there are some extra validation checks based on table-field level, like check table (foreign key). This can be validated by FM DDUT_INPUT_CHECK.

    In order to retrieve the reference field you may use the “secret”

    DESCRIBE FIELD lv_field HELP-ID lv_help_id command.

    3. MMDDYYYY is also a valid date format (even quite popular 😛 ).

    I think you should introduce an extra parameter, or, even better, an extra method (set_date_format) in order to set the expected date format.

    (0) 
    1. Bruno Esperança Post author

      Hi Shai,

      Sorry for taking so long to reply to your comment, lately I’ve been avoiding work related topics… trying to separate leisure from work 🙂

      Your last point is a very valid point. If you’d like to implement this feature and share the code, I can make you a contributor to the project.

      As for your other points, even though valid, I’m not sure if the benefits are worth spending the time to implement them.

      Cheers,

      Bruno

      (0) 

Leave a Reply