Excel to Internal table

[Former Member]

Experts,

I see many posts by Sandra and other ABAP experts where the recommendation is to use ABAP2XLSX. I followed the installation page from GITHUB and installed ABAP2XLSX.

As a next step, I want to get to the code where I can upload an excel file in the selection screen of my program and eventually get the excel data in an internal table.

In one of the blog posts, I saw a reply from Gregor Wolf that we can use program in this link to achieve this excel to internal table functionality. When I try to activate the program from that link, I am getting an error that include zdemo_excel_outputopt_incl does not exist. Does this mean I have not installed ABAP2XLSX correctly?

Also I want to move my custom program to PRD that will perform excel to internal table functionality based on ABAP2XLSX. Does this mean, I need to install ABAP2XLSX in PRD as well?

NOTE: I have created the program ZABAPGIT_STANDALONE as per ABAP2XLSX installation instructions in this link

I have been using other OLE-based function modules and I want to walk away from that approach as I see that it's not the right approach.

I am so sorry if this question is very basic. I want to shift to ABAP2XLSX once and for all so that I can use the best approach in all my upcoming projects. Can you please help me successfully implement ABAP2XLSX?

BR,

Bharath

[Sandra_Rossi]

It's not that it's the right approach, it's that SAP doesn't support OLE function modules. At least, abap2xlsx is supported by the SAP community for a long time, and usually has less drawbacks than the SAP function modules.

To install:

• First install abapGit. It seems you did it successfully.
• Then install abap2xlsx with abapGit.
• To benefit from the demo programs, install the demos repository with abapGit.

To read an Excel file into an internal table, first store that Excel file into an XSTRING variable, then loop at the worksheets:

DATA(lo_reader) = NEW zcl_excel_reader_2007( ).
DATA(lo_excel) = lo_reader->zif_excel_reader~load( i_excel2007 = xstring ).
DATA(i) = 1.
WHILE i <= lo_excel->get_worksheets_size( ).
  DATA(lo_worksheet) = lo_excel->get_worksheet_by_index( i ).
  i = i + 1.
ENDWHILE.

For each sheet, you can read its cells using the sheet_content attribute:

LOOP AT lo_worksheet->sheet_content REFERENCE INTO DATA(cell).
  " cell->cell_row, cell->cell_column, cell->cell_value, ...
ENDLOOP.

[Former Member]

Hi Sandra,

Thank you so much for your valuable reply.

I am not sure what I have installed so far. I have a program called ZABAPGIT_STANDALONE in my system.

Executing the program gives me screen as below,

I would like to install demo programs to get the full code that I can copy and paste to run a test scenario. This is what I see when I click demos folder in github,

I am not sure how I can install demo programs as I am not sure where I can find the instructions in this folder.

I see that include zdemo_excel_outputopt_incl is missing in my system. This include has been reference in one of the demo programs. This makes me believe that I have missed some steps.

BR,

Bharath

[Former Member]

When I try to install ABAP git, using program ZABAPGIT_STANDALONE, this is what I see in SE38,

I am following this link to install ABAP git.

BR,

Bharath

[Former Member]

Sandra,

I copied the code from this demo program and pasted in my SE38.

I see that I have structure zexcel_s_table_settings in my system. But I don't have include zdemo_excel_outputopt_incl in my system. This is the issue that I am facing.

BR,

Bharath

[abo]

A few random comments:

bfeeb8ed7fa64a7d95efc21f74a8c135 come quickly! (semicit.), the impossible happened: someone is paying close attention to the forum posts! 🙂 (and this makes me very happy and willing to help!)

sandra.rossi depending on the application, one could also use the load_file method, which should work for both local and remote files

aspire.wf you'll need abap2xlsx in the PRD system, correct, but you may now install the demos as a separate local package to avoid transporting those, if you wish.

ZABAPGIT_STANDALONE is the single source version of abapGit: it works perfecly fine, it gets regenerated every night but if you want to upgrade it you have to download it again like you did the first time. As an end-user, you should be ok with this version.

To install:

• demos: use the correct link, https://github.com/abap2xlsx/demos
• abapgit dev. version: again, the correct link: https://github.com/abapGit/abapGit

To get the link: click the green code button, in the HTTPS tab you'll get the link and then a button you can click to copy the link, right at the end of the text field.

[Former Member]

Andrea,

Thanks a lot for your reply. In ZABAPGIT_STANDALONE, I select the row where it says demos and clicked on Pull. I got a popup that shows a list of programs. as below,

After selecting all the programs and clicking green tick mark, I am prompted to give a transport no. I select one and it gives me a message as below,

I want to change the package name from ABAP2XLSX to $TMP. I am not sure how I can change the package name at this point. I tried to delete the package ABAP2XLSX. I am getting a message that the package still contains objects and cannot be deleted. I don't see any object inside the package in SE80.

I wanted to start all over again by uninstalling entire ABAP2XLSX. I see the steps in this link to uninstall. When I go to ZABAPGIT Tcode, I don't see a master menu from which I can select uninstall.

I am stuck with this incorrect package name which is not letting me pull the demo programs.

BR,

Bharath

[Former Member]

Andrea,

This time around, I didn't select the program ZDEMO_EXCEL_WDA01 from the popup that shows up when I click Pull after selecting demos row. I was expecting that the rest of the demo programs will get Pulled to my system. I entered the transport no. I didn't get any error message. But when I go to SE38 and search Z*DEMO*. I don't see any demo program.

BR,

Bharath

[abo]

You should use different package names for different projects, that's a general rule. EDIT: you did, the screenshot was too hard to read for me)

You may have, for example, ZABAP2XLSX for the main library (a transportable package) and $ZABAP2XLSDEMOS for the demos alone (a local package, since it begins with $)

Also, when pulling to an empty package, mark all objects, else you'll get errors for missing parts.

[Former Member]

Andrea,

Can you please tell me what this error message means?

BR,

Bharath

[Former Member]

Andrea,

Thanks a lot for your reply. I am not sure how I can post a larger-sized image. I will type the contents in the image here and will ask my doubt.

We have a column called package in the above image. For abap2xlsx, it shows that I have given the package name as $ABAP2XLSX. For abapgit, I have given the package name as $ABAPGIT. For demos, I have given the package name as ABAP2XLSX.

Thanks for letting me know that I should select all the objects in the popup. For me to do that, I should somehow rename the package ABAP2XLSX to a package name that either starts with '$' or Z/Y. This is what SAP message says.

Can you please tell me how I can rename the package to the correct name?

If renaming is not possible, can you please tell me how I can start installation all over again from scratch so that this time around, I can give the package names correctly.

BR,

Bharath

[abo]

• Clean each project with "uninstall"
• prefer using Z as starting letter for transportable package names and $ for local ones
• abapGit and demos should be local, abap2xlsx should be transportable and must be transported together or before the programs using it
• pull all obejcts in abap2xlsx first, then pull demos, again all objects

[Former Member]

Andrea,

Thanks a lot for your reply. You have suggested to clean each project with unintsall. I checked this blog where the discussion is about uninstallation abap2xlsx.

Key points:

1) I have not written any custom code so far. I am still in the early stage of trying to get demo programs in my system so that I can refer them and convert excel to internal table

2) Even in the blog, I don't see how to enter the project view to delete abap2xlsx.

As far as I can see, I don't see an option in ZABAPGIT_STANDALONE program output for uninstallation. Can you please tell me how cleaning/uninstallation can be done. I tried ZABAPGIT Tcode as well which shows the same output. There is no option to uninstall.

3) github documentation has steps with screenshots for uninstallation. I don't see the menu option which says 'Master' as shown in the documentation link,

BR,

Bharath

[Former Member]

Andrea,

My abap2xlsx output screen looks as below,

I am not sure if you can see the screen clearly with the image resolution. If you see the screen, I don't see an option for uninstall.

BR,

Bharath

[abo]

uninstall is an option for a specific project: you need to be in the project view to use it!

click the project name to enter this view.

[Former Member]

Andrea,

You and Sandra have been helping me patiently. I feel so bad that I am unable to figure out ABAP2XLSX. I see so many threads in SCN where everyone is appreciating how cool this magical utility is.

I have been kicking the can down the road whenever I got an excel based on requirement that I will read about ABAP2XLSX someday when I have more time in hand. I am totally wrong as many experts like Paul Hardy, Sandra and others have repeatedly mentioned that setting this up is very easy and that it only takes a few minutes.

Challenges that I am facing right now:

1) I am not sure why, how and when I gave the package name as ABAP2XLSX instead of a name with a '$' or Z/Y. I have to rename this package for which I need to delete or uninstall this utility and start over again with the correct package names.

2) Can you please tell me what a Project is? Sorry, if this a basic question. I don't remember creating any projects so far and writing any code using this utility. I am still in the installation or setup phase. Also, ZABAPGIT_STANDALONE program output screen does not mention any column/option which says 'Project'. You have clearly mentioned the correct packaging names in an earlier reply. If I am able to start over again, I can stick to that naming convention.

BR,

Bharath

[abo]

Sorry, my bad: I used "project" as a synonim for "repository", you need to click on each of the names on the left.

[Sandra_Rossi]

Just use Advanced > Remove to remove the abap2xlsx line from abapGit, then add it again and indicate the right package name $abap2xlsx. After that, pull the objects.

[Former Member]

Thank you so much, Andrea. I selected the repository and I see the option to choose Advanced-> Uninstall.

I was able to delete 2 repositories successfully. When I delete the 3rd repository demos, I am getting an error message as below,

Is this behavior expected? Can you please let me know how to address this.

BR,

Bharath

[Former Member]

Sandra,

Sorry, I didn't see your reply and that is why I didn't follow your instructions. I am unable to change my ex-employers email ID in my profile due to which I am not getting notified whenever there is a reply.

I have already done uninstall instead of remove 😞

And now its stuck midway as I am unable to uninstall demos repository.

[Former Member]

For demos package, I tried remove instead of uninstall and it worked perfectly. ABAPGit looks clean now without any repositories. Thank you so much, Andrea and Sandra.

Is it ok to keep this thread open for a few more hours as I would like to redo the steps and see if it goes through without any issues?

BR,

Bharath

[abo]

The thread is yours, you decide 🙂

Anyway, look at the screenshots of the abapGit documentation: that's where I saw "Project" 🙂

[Former Member]

Since the ABAP2XLSX programs opened in ADT, I wrongly assumed you are talking about Eclipse Project.

[Former Member]

Sandra and Andrea,

I am extremely thankful to you both for your patience and invaluable support. I am now able to get the data from the excel file in the selection screen to an internal table in my program. This requirement is simple and I could have used any of the class or FMs as this is a purely SAP GUI-only scenario. I have been ignoring ABAP2XLSX for way too long without realizing its power. I read a comment by Paul Hardy on one of the blogs where he said something along the lines of,

"Many hesitate to implement ABAP2XLSX because it's scary to see several dozen objects in our transport from this utility just to get a simply requirement done. But once we understand the power of this utility, we will end up using this magical tool again and again in many projects."

Just like others before me, I can now happily say that ABAP2XLSX is sheer magic. Hearty thanks to the entire team of ABAP experts who have built this utility and generously allowed developers like me to consume it.

My final code to get excel data into the internal table GT_FINAL_DATA looks as below:

    CLEAR: gt_file_data.
    DATA: lo_reader    TYPE REF TO zif_excel_reader,
          lo_excel     TYPE REF TO zcl_excel,
          lo_worksheet TYPE REF TO zcl_excel_worksheet,
          lo_error     TYPE REF TO cx_root.

    TRY.
        CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.
        lo_excel =  lo_reader->load_file( p_file ).
        lo_worksheet = lo_excel->get_worksheet_by_index( iv_index = 1 ).
        lo_worksheet->get_table(
          EXPORTING
            iv_skip_bottom_empty_rows = abap_true
          IMPORTING
            et_table                  = gt_file_data ).

      CATCH cx_root INTO lo_error.
        MESSAGE lo_error TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.

BR,

Bharath