Issue with reading XLSX with multiple sheets file in application Server

[Former Member]

Hi,

We're currently implementing SAP CRM 7.40.

In one requirement we have to process an XLSX file and load its information to a set of Z tables.

The file has 6 sheets of data to process, each sheet with distinct number of columns and types (each sheet will be upload to a different Z table).

We're able to process the XLSX using the Desktop Office integration for local files.

However, we must also be able to process files on the application server.

We tried the following approaches without success

- The DOI library doesn't seem to support remote files processing:

- OPEN DATASET. with the dataset we're able to read a remote file, however since the file format is XLSX, the information we read in the dataset comes in an unreadable format

- FTP. we tried to use FTP to download the remote file to a local folder so that we could then use the DOI library to process the file. However, the client only allows SFTP connections and SAPFTP doesn't provide SFTP

There's another approache we're considering to try which is to have a perl script on the application server to split the Excel file into CSV files (one for each tab) and then use OPEN DATASET to read the contents of each file and work from there. This approach seems quite cumbersom

The approach with DOI would be the best one but either we're missing a key configuration or it truly can't be used for files. Are there any special requirements that we should take into account for this option?

We'd like to know if you have any other approach that proves successfull or if any of the above can work but with some aditional adjustments.

Thanks in advance.

Best regards.

[Private_Member_14913]

Hello Ricardo,

Go through below link.

You can try below approach if it is feasible for you.

Turning ".XLSX" into XML & read XML file through application.

XLSX Upload - a Unified Approach - SCN - SAP.com

Thanks!

Sameer

[Former Member]

Hi Sameer.

I've reada both links and have the following doubts.

The second link option is only valid for files on the presentation server and online usage, or it can also be used for background processing of files in the application server.

The first link option is basically what I'm currently trying to do: split the XLSX file, with a perl script, to multiple CSV files and then process each one of them accordingly.

[Private_Member_14913]

Hello<

I have not tried it with application server.

But after you created XML file, You can read with "CALL TRANSFORMATION".

Regards

[Former Member]

I've to be able to process the XLSX file on the application server in background since in background only the path of the file is known. there will be no user intervention.