Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

I need to convert EXCEL file that is in XSTRING format to internal table data

Former Member
0 Kudos
10,973

Yes, i have a requirment like getting data excal fie in XSTRING format and i want to extract data from that XSTRING to internal table column wise.

is there any way?

Thanks for your intrest..

20 REPLIES 20

rosenberg_eitan
Active Contributor
0 Kudos
1,640

Hi,

Can you share the code ?

If you save the xstring to a file and open it with text editor can you see the content clearly ?(Show a picture)

Regards .

0 Kudos
1,640

Hi Eitan,

this is how data would be if i download xstring data of excel to text file.

i need to convert this data to internal table format in column wise as in excel or the way the funnction module give.

0 Kudos
1,640

0 Kudos
1,640

Hi,

What I am trying to find out is what's hidden in that string .

Is that a native excel file or some CSV file (a plain text file) .

Regards.

0 Kudos
1,640

Hi,

I have the feeling that this is a native excel .

If this is the case you can save the string to a file and then use function 'ALSM_EXCEL_TO_INTERNAL_TABLE'  .

Regards.

0 Kudos
1,640

i have to handle it dynamically i dont get chance to store in desktop.

yes it is native excel file XLSX format

0 Kudos
1,640

Hi,


i dont get chance to store in desktop

Why not ?

Regards.

0 Kudos
1,640

because i  will get XSTRING of EXCEL file from PI and i have to convert XSTRING to internal table data and have to do some calculations and send final data to UI5 through service. these all dynamic.

0 Kudos
1,640

Hi,

Why use Excel format and not some other more easy to process data format like CSV or tab separated value.

Any way good luck......

Regards.

Former Member
0 Kudos
1,640

1. Fist conver xstring to binary.

          SCMS_XSTRING_TO_BINARY

2. Binary to string.

          SCMS_BINARY_TO_STRING.

3. last convert  string to itab

CALL FUNCTION 'RKD_WORD_WRAP'

      EXPORTING

        textline            = input_string

        delimiter           = ' '

        outputlen           = 100

      TABLES

        out_lines           = output_itab

      EXCEPTIONS

        outputlen_too_large = 1

        OTHERS              = 2.

0 Kudos
1,640

at second step i am getting data like this for string.

i will get xstring of whole Excel file(not separate data).

is there any other conversions i tried to debug ALSM_EXCEL_TO_INTERNAL_TABLE but its allowing to see code completly

raymond_giuseppi
Active Contributor
0 Kudos
1,640

Perform some search on abap2xlsx , or play with class related to xml and zipped files (*), if that is a true XLSX file upload, for old XLS, Communication with Excel (OLE) is required.

Regardf,

Raymond

(*) To understand copy a sample XLSX file on your desktop, rename it a type ZIP file and open it with explorer.

0 Kudos
1,640

Hi Raymond,

Are you using abap2xlsx at DASSAULT in production ?

Regards.

0 Kudos
1,640

Yes, we are, but only for some simple upload/download transfer, most Excel interaction are executed thru OLE (on SAP GUI) .

Context : We are in a project with implementation of Talend ESB, so in a short (or not) delay most of those file transfers will be executed with Talend connectors.

Regards,

Raymond

0 Kudos
1,640

Thanks.

Regards.

Eitan

juan_suros
Contributor
0 Kudos
1,640

Sriharsha,

If I understand you correctly, you need to extract data from a binary Excel file using only the SAP application server. Your PI application has no access to a Win32 client machine, so it cannot use ALSM_EXCEL_TO_INTERNAL_TABLE to run Excel on a client machine.

It is still possible to use Excel for this purpose if your SAP application server is a Win32 machine with the Excel application installed on it. You will need to define a system command in SM69 that calls a command line program with the file path of your xlsx file as an argument. Use function  SXPG_COMMAND_EXECUTE to call the system command.

The steps are as follows:

  1. Use OPEN DATASET ... EXPORT to move your data into a binary file on the SAP application server.
  2. Use function  SXPG_COMMAND_EXECUTE to execute a command line process that transforms the relevant part of the xlsx file to text.
  3. Use OPEN DATASET ... IMPORT to import the text file into SAP for processing

I have done this using the scripting host command "WScript", with first parameter a vbs file and second parameter the filepath to the data file. It would probably be faster to compile a .NET program that is called from the command line.

This approach will only work if your SAP application server is a Windows server machine. If it is some type of UNIX, it may be possible to use OpenOffice. That question is beyond me.

Good Luck,

Juan

0 Kudos
1,640

Hi,

The idea of installing office on the application server is nice.

I guess that you can use it in back ground jobs as well.

I use Java to do that.

There is  Apache POI - http://poi.apache.org/

that allow you to read/create office documents.

See:

Regards .

Former Member
0 Kudos
1,640

Hi all,

nthng is working but i am able to  convert the XSTRING of XLSX file to XML string i found transformations or useful in this i dont know how to do that you guys have any information.

Thank you.

0 Kudos
1,640

Sriharsha,

You have asked us how you can extract information from a .xlsx file passed to SAP from PI. Your process runs only in the "background", with no connection to SAPGUI. The community has given you good advice above.

To summarize:

In ABAP world:  you need to find a way to extract the information you want from the xml data in an ".xlsx" file. A user above suggested this tool.

In (Windows) application server world:  You can install Excel on application server and use COM automation to tell Excel to extract the data you want from any .xlsx file into a format that can be read directly into SAP. Both source and target files are moved to application server directory using OPEN DATASET.

In (Unix) application server world:   Use OpenOffice to transform .xlsx files. Both source and target files are moved to application server directory using OPEN DATASET.

vijaybhaskarraju_vegesana
Active Participant
0 Kudos
1,640

This message was moderated.