Create internal table from EXCEL file without limitations

[former_member195402]

Hello Community,

yes, this has been asked so many times before. I've read so many threads, but they all point to the same improper function modules.

I need a function (function module, method) that supports the same path length 1024 as method FILE_OPEN_DIALOG of class CL_GUI_FRONTEND_SERVICES, that also supports full cell value length and delivers all used rows and columns.

List of the improper function modules:

KCD_EXCEL_OLE_TO_INT_CONVERT

• Not released
• Needs number of columns and rows
• Supports only 32 characters per cell value
• Filename supports only 128 characters

ALSM_EXCEL_TO_INTERNAL_TABLE

• Not released
• Needs number of columns and rows
• Supports only 50 characters per cell value
• Filename supports only 128 characters

TEXT_CONVERT_XLS_TO_SAP

• Not released
• Filename supports only 128 characters

Main issues:

Path length 128 is much too short.

EXCEL supports cell values up to 32767 characters.

What I need is full EXCEL support for path length 1024 characters and cell value support for 32767 characters.

Dow you know any SAP standard tools that could help me? Method GUI_UPLOAD works fine to upload the file, but I don't know how to extract the data from it.

Regards,

Klaus

[SimoneMilesi]

Hi Klaus,

Using only standard tools is mandatory?

In this case, you have to replicate what ABAP2XLSX's reader class do.

i crashed on the same issue many times but finally my customer accepted to install ABAP2XLSX and now they cannot live without all the available features they got

ABAP2XLSX "just" organize the procedure to manage XLSX file with standard tools.
You can do the same but replicate it sounds a bit "silly" like re-inventing the wheel.

[former_member195402]

Hi Simone,

many thanks for your answer. I've also found this blog about XLSX2ABAP,  which looks very interesting. But I'm not allowed to install foreign software addons for CERT and security reasons.

I fear the worst, that there will be no SAP standard solution for this issue available.

Best regards,

Klaus

[SimoneMilesi]

No, there is a standard solution: a bunch of code (ABAP2XLSX package uses only native abap to manage xlsx).

For this i said "reinveinting the wheel"

They did what you have to do and organized the code.

Maybe could help you somehow.

[_IvanFemia_]

Unfortunately no standard solution

I'm trying to have abap2xlsx included in the SAP standard, but without any success so far. Your voice can help to support the cause!

I had many discussion with several consultants and customers on this topic, and you should check this thread [abap2xlsx] How to argue for Security in abap2xlsx

Ivan

[former_member195402]

Hi Ivan,

I have no authorization for that thread.

Regards,

Klaus

[matt]

I managed to get it installed in a CSV GxP relevant system. I just had a chat with the development manager. Even the naming convention being at variance with the defined standard wasn't an issue. Maybe it's just my winning personality... but I suspect it was the fact it was a business requirement.

[matt]

I can't see it either, so I've raised it in the moderator space.

[matt]

I got a reply from

"The whole Code Exchange was shut down. In order to retrieve content from there you need to reach out to devcenter@sap.com ( I can introduce you). To where should we migrate the content?"

[former_member195402]

Looks like they need a man with a concept .

[Juwin]

Check if your system has CL_FDT_XL_SPREADSHEET class. This is related to FDT (Formula and Derivation Tool) and not intended to read generic spreadsheet, but it works in most cases.

If it exists,

Use IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES method, to get the worksheet names from the excel spreadsheet.

For each of the worksheet names, use IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET method to get the contents of the worksheet into internal table.

Thanks,

Juwin

[former_member195402]

Hi Juwin,

many thanks for your reply, but this class is not available in our systems.

Regards,

Klaus

[Juwin]

Ok, even without this class, reading an XLSX file is easy. All you have to know is how to parse XML files.

Thanks,

Juwin

[Clemenss]

There is just one huge mis-approach in the SAP code:

Internally, SAPs CL_FDT_XL_SPREADSHEET->GET_ITAB_FROM_SHEET uses

METHOD cl_alv_table_create=>create_dynamic_table

This method, instead of simply using RTTS dynamic type services, creates a fieldcatalog and generates the tables in a dymaic subroutine pool one by one. Thus, there is as (SAP-typical) limit of 36 tables. If the excel sheet hast more than 36 tabs, you get a dump with a completely misleading error message.

I could not try abap2xlsx because I still can't work with the abapgit standalone program (that's another theme). But I'm afraid it won't help because it uses the same SAP standard methods.

Is there any SAP standard alternative that does not use this f... cl_alv_table_create=>create_dynamic_table?

Thank you all.

Regards Clemens

[former_member195402]

Hi,

meanwhile I've made a copy of fm  ALSM_EXCEL_TO_INTERNAL_TABLE and changed the interface and some internal tables.

Changes made:

• Importing parameter filename supports 1024 characters
• Result table value field is of type string
• Importing column and row parameters are optional. If empty, begin values are set to 1, end values are set to max (XLS: 65536 rows / 256 columns; XLSX: 1048576 rows / 16384 columns)

It's working fine for me, also cells containing long text can be read, but I don't think that the max cell content of 32767 characters will work now. There might be still some limitations remaining.

Thank you all for your good ideas.

Regards,

Klaus

[matt]

Re: external not allowed for security reasons. Copying a standard FM is, in my opinion far more risky and expensive. Cloning is last possible option and should only be done as a last last last resort.

[Tomas_Buryanek]

Well, I agree. But SAP note 933420 recommends to do so (copy module to customer name space).

Anyway, this FM is not released 🙂

It would be nice to have better cooperation SAP vs. Microsoft overall.

[former_member195402]

Hi Matthew,

for myself I agree with you. But the copy into our custom namespace makes it a  custom program code (no addon or foreign code) and will be also scanned by our security tools. In addition to SAP CERT notes we have a custom tool in our company to scan all code other than SAP code. This is weekly done by our CIT for all our production systems all over the world by remote access.

We have several classes of code that may contain security lacks:

• SAP code will be reported to SAP by our CIT.
• Custom code will be reported for improvement to the local IT, which has the maintenance for the production system.
• Addon code maintained by our CIT will be improved by the CIT itself.
• Other addon code will also be reported to the IT, which maintains the production system, to forward it to the code manufacturers for improvement.

So it's more easier to use custom code than addon code.

Regards,

Klaus