topic Re: Reading Excel file from Application Server in Application Development and Automation Discussions

Reading Excel file from Application Server

former_member382811 — Thu, 23 Jul 2015 07:23:36 GMT

Hi Experts,

I am reading Excel file from Application server using OPEN DATASET IN TEXT or BINARY MODE but got dump like

Runtime Errors CONVT_CODEPAGE

Except. CX_SY_CONVERSION_CODEPAGE

Please suggest how to read Excel file from Application server into internal table,kindly suggest me.

Re: Reading Excel file from Application Server

hiriyappa_myageri — Thu, 23 Jul 2015 07:28:49 GMT

Hi,

Please refer this link it surely help you..

https://scn.sap.com/thread/3714770

Re: Reading Excel file from Application Server

SimoneMilesi — Thu, 23 Jul 2015 07:40:18 GMT

Or refer to abap2xlsx - ABAP Development - SCN Wiki

Re: Reading Excel file from Application Server

jrg_wulf — Thu, 23 Jul 2015 08:19:09 GMT

Bad news first. There is no such thing as an easy approach to working with excel!

Assumed you got the import (in binary format) alright - what would you do then?

You can never be certain that the contents matches the extension. And excel is not equal to excel.

Through the generations, the format has undergone several changes, probably the reason, why there is no FM dealing with that (which on?) format directly.

Older versions have a proprietory format, which is accessible (with reasonable effort) only by OLE integration.

The provided links will give you some insight but they don't cover the topic by far.

If you really have to read the file from application server, OLE is not an option. On the lucky side, you usually don't have true excel on the application server, unless they got there by some file transfer.

So first step for you - analyse an examplary file on byte level. Which format does it really have.

Some "excel" files are in truth csv-files, only provided with the wrong extension.

If it is really an excel, you can only hope, that it's an xlsx, since they do have a readable format.

Xlsx-files are in truth zip archives, containing a number of xml-files, that can be interpreted.

Don't get me wrong, it's still a lot of work but can be done.

If bad comes to worst and you're stuck with a proprietory xls, you might think of an intermedeate step, involving transferring to presentation server and then, depending on the actual business case, reopening from there by means of OLE, or converting to csv (involving VB-macro?) and process as plain text.

Again, depending on the actual case, there are limitations to this, regarding the file size.

There is plenty of stuff here on scn and you can dump a lot of hours only by gathering the basics.

On the other hand, you can try to change the outer conditions towards a more easy to handle format (csv)

In any case - good luck.

Best regards - Jörg

Re: Reading Excel file from Application Server

former_member382811 — Thu, 23 Jul 2015 08:42:30 GMT

Yes your right.already done with VB macros to XLS to CSV its working fine.we suggested client to place files in FTP only .csv but they are not agreed to that.we searching to any possibility using abap code to convert.thanks for your answer.

Re: Reading Excel file from Application Server

Former Member — Thu, 23 Jul 2015 08:58:02 GMT

You can even ask them to convert the XLS to TAB delimited text file or. It's pretty is easy using "SAVE AS" option in Microsoft excel.

I understand that there might be a situation when description may contain comma in it and .csv causes issue. . But tab delimited should work in any situation.

Re: Reading Excel file from Application Server

SimoneMilesi — Thu, 23 Jul 2015 09:02:00 GMT

Then try to ask they AT LEAST manage them as XLSX which is, basically XML, and then you can use one of the thousand solutions provided here in SCN.

Otherwise you can pretty hardly find a working solution.

Re: Reading Excel file from Application Server

jrg_wulf — Thu, 23 Jul 2015 10:55:45 GMT

In that case your only hope is xlsx.

For that, there are some ready to use aproaches here on scn.

I have done that and it's not too hard.

Basically it consists of three steps:

read the content in binary format as xstring
unzip it (using class CL_OPENXML_ZIP)
from the contained files, you mainly need two - the worksheetfile and the sharedstrings

You have to know, that xlsx store all strings of all sheets in one file "sharedstrings.xml" and references them by index.

So basically you have to read the strings into an internal table and whenever a string reference in your worksheet occurs, read the respective line from your indexed table.

I found the class cl_sxml_string_reader to be very helpful with this.

BR - Jörg