Keep the whitespaces in the internal table, which receives data from Excel

[hagit]

Hello experts,

We have a Z program, which uploads XLSX file to SAP. The program calls FM ALSM_EXCEL_TO_INTERNAL_TABLE, which uploads the excel file to SAP. According to the debugger - in an inner step (9) DP_GET_STREAM_FROM_FRONT calls to FM DP_GET_CLIENT_TABLE45A.

In DP_GET_STREAM_FROM_FRONT

When DP_GET_CLIENT_TABLE45A finds a whitespace in the file, the FM replaces it by # character. i.e., the internal table DATA (line 152 above), which is returned by the FM, has # instead of white space.

Later when ALSM_EXCEL_TO_INTERNAL_TABLE receives this internal table, it considers the # as separator and therefore divides the data into two. For example if the cell in the Excel file contains o/<whitespace>p it is sperated into “o/ AND p”

The code in ALSM_EXCEL_TO_INTERNAL_TABLE, which divides the data into two is:

This behavior (that the data in one cell is divided into two) disrupt the data, which is uploaded into SAP.

My goal is to find the whitespaces in the file and to notify the user. (He will fix the file). The problem is that the data which, is returned from the FMs, do not include the whitespaces (they were replaced by #).

How can I upload Excel file to internal table with the whitespace? (and then check if it has whitespace by cl_abap_char_utilities=>get_simple_spaces_for_cur_cp( ))

FYI

When looking in the excel file

1. 1. In the cell the whitespace is not seen (red)

2. In the yellow area you can see the whitespace

Thank you in advance

Hagit

[matt]

The problem is that the data which, is returned from the FMs, do not include the whitespaces (they were replaced by #).

This is unlikely to be the case. # is merely how SAPGui renders an undisplayable character.

If you look at the hexadecimal representation of your data in debug you'll see that the # are in fact x09 - i.e. the tab character. Easily accessible in ABAP with CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

One question - is it actually an xlsx file or just named that. If you open in notepad, is it human readable?

[hagit]

matthew.billingham Thank you for your answer.

As I wrote in the above comment, code in ALSM_EXCEL_TO_INTERNAL_TABLE can find the whitespace and warn about it. I cannot replace ALSM_EXCEL_TO_INTERNAL_TABLE by ABAP2XLSX because the Z program, which calls the FM, is very complicated and has many options (It will take a long time to test it again). So, my question is – How can I interfere in the code of ALSM_EXCEL_TO_INTERNAL_TABLE? Is there a user exit or BADI for it?

Thank you

Hagit

[abo]

hagit it takes 1 hour to install the package if it's your first time and about another hour to replace that function module, because one of the examples is essentially a drop-in replacement

[hagit]

c5e08e0478aa4727abc4482f5be390b2 and matthew.billingham

Does ABAP2XLSX refers to non-displayable character in a different way (other way than ALSM_EXCEL_TO_INTERNAL_TABLE)? Does ABAP2XLSX keep the # in the internal table, so the code , which would be added to the call to ABAP2XLSX , can catch the # (the whitespace)?

If the answer is no then, it would not help to replace ALSM_EXCEL_TO_INTERNAL_TABLE by ABAP2XLSX.

Thank you

Hagit

[abo]

Why don't you just try it? The upload demo is ZDEMO_EXCEL15.

[hagit]

c5e08e0478aa4727abc4482f5be390b2

I am not sure that my company allow to upload from GitHub (from security point of view)

[abo]

matthew.billingham has a cool story regarding this very issue, if I am not mistaken.

[matt]

I used to work for a pharmaceutical company, which had validated systems. That meant tight controls over the software process, following object naming conventions, together with audits from the likes of the FDA that we were following our processes correctly. If we weren't, the FDA had the power to shut down drug production, so this is serious stuff.

I had a program to write, involving taking spool files and converting them to formatted Excel, before sending them to distribution lists. I was an external developer, so in the scheme of thing, no power, the lowest of the low. I contact the head of software development, and explain what I needed to do, and that abap2xlsx, from GitHub would a) introduce a powerful tool usable for all SAP software development and b) reduce the amount of time to develop my program by days, if not weeks.

She agreed we would do it. The benefits were just too good not to.

We had a process for deviating from the standard process, where the reason for deviating from the standard process is documented, and various checks are made to avoid any malware getting on the system. And that was that. All done.

Pragmatically, using upload from GitHub is no different from someone typing in the code in the GitHub by hand.

It is funny that some companies have this idea that somehow Open Source ABAP applications are dangerous, yet Open Source Linux is used to run the SAP servers...

[hagit]

matthew.billingham and c5e08e0478aa4727abc4482f5be390b2

I downloaded abap2xlsx from GitHub and used it instead of the FM. Then I could identify the white spaces in the excel file.

Thank you for your great help

Hagit

[Sandra_Rossi]

It looks like that the file is a text file with tab-separated values, so why using ALSM_EXCEL_TO_INTERNAL_TABLE?

[hagit]

matthew.billingham thank you for your answer.

One question - is it actually an xlsx file or just named that. If you open in notepad, is it human readable?

Is it not human readable.

What does it mean?

You are right – In ALSM_EXCEL_TO_INTERNAL_TABLE the hexadecimal value of the # is 0900

ALSM_EXCEL_TO_INTERNAL_TABLE is a standard function, that cannot be changed. So, I copied it to a Z function, and add code. The code searches for whitespace and alert if whitespace is found.

I add lines 94-106 after lines 82-92 which returns table excel_tab with the Excel data.

* read clipboard into ABAP
  CALL METHOD cl_gui_frontend_services=>clipboard_import "line 82
    IMPORTING
      data       = excel_tab
    EXCEPTIONS
      cntl_error = 1
*     ERROR_NO_GUI         = 2
*     NOT_SUPPORTED_BY_GUI = 3
      OTHERS     = 4.
  IF sy-subrc <> 0.
    MESSAGE a037(alsmex).
  ENDIF.  "line 92

***********************************hag ********************* line 94
  DATA(lv_spaces) = cl_abap_char_utilities=>get_simple_spaces_for_cur_cp( ) .
  lv_spaces = lv_spaces+1. "do not include regular space (‘ ‘)
  CONCATENATE lv_spaces cl_abap_char_utilities=>backspace  cl_abap_char_utilities=>cr_lf INTO lv_spaces.
  LOOP AT excel_tab INTO DATA(ls_excel_tab).
*    IF ls_excel_tab CA cl_abap_char_utilities=>horizontal_tab .
    IF ls_excel_tab-line CA lv_spaces.
*            MESSAGE sy-fdpos+1 && '  ' && ls_excel_tab-line+sy-fdpos(1)  TYPE 'I'.
      MESSAGE 'There is whitespace'  TYPE 'I'.
    ELSE.
      MESSAGE 'There is not whitespace' TYPE 'I'.
    ENDIF.
  ENDLOOP.   "line 106

This solves my problem.

Do you think it is a good idea to copy ALSM_EXCEL_TO_INTERNAL_TABLE to ZALSM_EXCEL_TO_INTERNAL_TABLE and use Z*?

Could you please convert your comment to an answer, so I would be able to mark it as the right answer?

Thanks

Hagit

[matt]

Generally, it's a bad idea to copy standard SAP code. I prefer repairs or enhancements over copies, as if SAP change the original, you can reapply your custom code. There's even tools to help you do this.

Best would be to use abap2xlsx

[hagit]

matthew.billingham ,

Dose abap2xlsx convert XLSX to ABAP or does it covert ABAP to XLSX?

I need to convert XLSX to ABAP

[hagit]

matthew.billingham

Is there enhancements for ALSM_EXCEL_TO_INTERNAL_TABLE?

[abo]

hagit abap2xlsx can handle both reading and writing a XLSX files, you're welcome to try the examples.

[hagit]

c5e08e0478aa4727abc4482f5be390b2 Thank you for your comment