Upload data to SAP and download data to Excel?

Objective

SAP Download and SAP Upload are the more popular keywords around SAP in the search engines.

We compile below various approaches to simplify upload of data to SAP, and download toExcel or other popular file formats.

]

Summary

SAP upload: if you have hundreds of records to create or modify, the solutions available are:

• Create a custom report using the open source framework xlsx2abap
• Build a program in ABAP. Use FM "ALSM_EXCEL_TO_INTERNAL_TABLE" to upload excel to internal table
• Use one SAP tool called LSMW (Legacy Systems Migration Workbench).
• Use one internal test tool called CATT (Computer Aided Test Tool) for older than ECC5 SAP releases.
• Use some easy to use third party tools (see list in #Commercial Tools section below).
• For some business objects, use the SAP mass maintenance transactions such as MASS or MM17:
• If SAPGui scripting is enabled, the user can "script" an upload / modify job by looping through say an Excel table with vbscript or use transactionSHUTTLE, the SAP certified tool from Winshuttle or Process Runner from Innowera. SeeUse_Script_Recording_and_Playback_to_automate_user_updates. Requires a medium level of skill to edit a recorded script
• Use the GUIXT tool from Synactive Inc. built into SAPGui, but with the InputAssistant add-on (expensive!)

The SAP internal solutions are usually not available to end users, and require some strong technical knowledge.

[

To download from SAP to Excel:

• Create a custom report using the open source framework abap2xlsx
• Many SAP reports have direct download function to Excel - search for the menu option / button.
• SAP Query and SAP QuickViewer are excellent tools for Downloading (BvdR AO).
• Transaction SE16N allows you to access any SAP table and download result to Excel
• SAP RFC functions can be called directly from Excel / VBA (requires special authorization).
• Use querySHUTTLE from Winshuttle that extracts data from one or many SAP tables, and enforces security rules.
• Process Runner also offers an easy way to find tables joins via Data Dictionary SOX compliant searchable XML look up of over 80K SAP tables and views. You can even use Excel data as a filter to limit your downloads.

Use transaction BAPI in SAP to find your business object and study the function.

If you know the SAP table the RFC RFC_READ_TABLE offers a SQL query of the table. This is only usable with single tables - joins cannot be used here. A workaround is ot create a query in the SAP database with the required joins and use the query in RFC_READ_TABLE.

There are known limitations of RFC_READ_TABLE such as total buffer width of 512 characters, older releases do not support floating point fields for extractions, short dump on incorrect where clause etc.

The Business Objects product (now acquired by SAP) offers an alternative way of querying and reporting against multiple tables in the SAP database.

[]

Upload data from Excel into SAP

• Some of the Business functions (BAPI's / RFC Functions) offer data upload
• The general tool in SAP for data upload is the Batch input / call transactions

In the batch input file you specify screen by screen and field by field the input to SAP.

SAP has a recorder to create the sample file. Just run trans SHDB. Within this transaction, you run a case of the changes you want to do. On exit of the trans you will have a batch input file that you can download and from excel. Manipulate the file before you call the RFC function RFC_CALL_TRANSACTION.

Formatting the downloads

Frequently the data from SAP is downloaded as text values, which cannot be summarized / analyzed in Excel. The data needs to be cast into true numeric format to facilitate Excel's powerful analytics. The following macro will format data downloaded from SAP in a usable way. It operates on all cells in the slected data range. Negative numbers with a leading - sign are also handled: Sub toNumber() On Error Resume Next For Each cl In Selection

cl.Value = CDbl(Trim(cl.Value))

Next End Sub

Open Source Tools

• abap2xlsx published on SDN Community Network on July 2010 and shared on SAP Code Exchange

]

Commercial Tools

Note: This section is usually updated by third pary folks - so read it with grain of salt on claims made here!

• For finance departments, GL Wand is the out the box solution that uses Excel based formula to refresh GL balances live from SAP without overwriting existing formatting. Secure and with drill to details. Download the free trial. No implementation.www.excel4apps.com/sap

• The fastest growing and remarkably powerful is Process Runner from Innowera, an SAP Software Solution Partner - it allows you to download and upload from SAP Excel using Transaction, BAPI, RFM, GUI Scripting. Supports Test runs, parallel processing, handles vertically placed condition and pricing records, automatically splits accounting documents larger than 999 lines and more.
• The first in the market and most used today is the set transactionSHUTTLE and runSHUTTLE (its runtime for end users) from WinShuttle. Previously called TxShuttle, it provides since 2001 a shuttling mechanism (upload and download) between Excel/Access and SAP in all releases. It is currently used everyday by thousands of people from hundreds of companies worldwide.
• Process Runner DB allows you to perform upload and download between MS Access, MS-SQL Server, Oracle, Sybase, My-SQL and more.
• ERP² from Quadrate provides powerful SAP data uploading capability from Excel or Access without any need for ABAP experience.
• Qubed from Quadrate provides drag-and-drop data extraction and reporting in a multitude of easy to use formats.
• To be able to Upload/Download from the one place see EzySolutions from Gesix
• For SAP downloads directly to Excel try the SAP Data Downloader MS Access application. You can download the demo version from Opal Software Solutions.
• Z Option - For Uploading GL, AR, AP, or other FI entries, see GLSU. Never record a script - just choose your fields and post to SAP - all without leaving Excel.
• See also TxShuttle that is now replaced by transactionSHUTTLE.
• EzySolutions from Gesix
• WinShuttle
• DSM ERP from EPI-USE
• PDM Personal Data Miner for SAP.
• SAPDataManagement