keepcalculator

06 Numbers and Powers

Export & Validate SAP Data Before ERP Switch

Card No. 06·70880 · Updated · Standard formula, shown below

This guide walks you through exporting your SAP data and validating it before migrating to a new ERP system. You’ll learn how to extract data cleanly, check it for completeness and accuracy, and resolve issues before they cause problems in your new system. You’ll need SAP access with the appropriate roles to run transactions, a spreadsheet tool like Excel, and access to your new ERP’s data templates.

What You’ll Need

  • Access to SAP with at least display authorization for the modules you’re exporting (FI, MM, SD, etc.)
  • A spreadsheet application (Excel, Google Sheets, or similar) for data review
  • The target ERP’s data import templates or field mapping documents
  • A dedicated storage location for exported files (shared drive, cloud folder)
  • Time to run validation queries and cross-check with business users

Step-by-Step

  1. Define the data scope. List which data objects you need: master data (customers, vendors, materials, fixed assets), transactional data (open purchase orders, sales orders, contracts), and configuration (company codes, plants, chart of accounts). Prioritize by what the new ERP requires for go-live.
  2. Identify the source tables and transactions. Use transactions like SE16N (table browser), SE11 (data dictionary), or standard reports (e.g., ZLIST for customers, MM60 for materials). For large volumes, consider using SAP Query or ABAP reports to extract data in a structured format.
  3. Export data to a neutral format. Use the ALV grid export function (Excel, CSV) or create a custom ABAP program to output files. Ensure you export all fields needed for the target system, not just the visible columns. For tables, use SE16N and export all rows—set the maximum rows to a high value or use a background job.
  4. Check for completeness. Compare row counts between your export and SAP’s total records (e.g., via SE16N row count or a custom count query). Verify that no records were truncated—especially if you used a report that limits output.
  5. Validate data quality. Run checks for mandatory fields: for example, material descriptions, customer names, vendor tax IDs, and GL account descriptions. Use spreadsheet filters to find blanks, duplicates, and obvious errors like negative quantities or invalid dates.
  6. Cross-reference with business users. Have key users from each department review the exported data for accuracy. They can spot incorrect statuses, outdated addresses, or inactive records that should be excluded.
  7. Map fields to the new ERP. Create a mapping document that shows each SAP field and its corresponding field in the target system. Note any transformations (e.g., date format changes, unit conversions).
  8. Perform a test load. Import a sample of the data into a sandbox or test environment of the new ERP. Verify that the data appears correctly and that no errors are thrown.
  9. Document and archive. Save the final exported files with a clear naming convention (e.g., SAP_Export_MM_Materials_20240101.csv). Keep a copy of the mapping document and validation results for audit purposes.

Common Mistakes

  • Exporting only the visible columns from ALV reports, missing hidden fields that are required.
  • Ignoring row count limits—standard reports often cap at 999 rows, leading to incomplete exports.
  • Not checking for trailing spaces or leading zeros in fields like material numbers or GL accounts.
  • Skipping validation of date formats—SAP stores dates as YYYYMMDD, but Excel may convert them to date serials.
  • Forgetting to include inactive or archived data that the new system still needs for historical reporting.

Tips

  • Use SE16N with the ‘Export’ button to get all rows—it handles large datasets better than ALV reports.
  • Create a validation checklist for each data object: row count, required fields, duplicates, and date ranges.
  • Work in batches: export and validate one module at a time to avoid overwhelming your team.
  • Use Excel’s ‘Text to Columns’ to split concatenated fields, and use ‘Remove Duplicates’ to clean up.
  • Keep a log of all issues found and how they were resolved—this helps during the actual migration.

Troubleshooting

Export is incomplete: If you’re using a report, check its selection criteria and ensure you’re not filtering out records. For SE16N, clear any filters and set the maximum rows to 0 (unlimited) or use a background job for very large tables.

Data appears garbled in Excel: SAP exports UTF-8 or system code page. Open the CSV in a text editor first, then import into Excel using the ‘From Text/CSV’ feature and choose the correct encoding (usually 65001: UTF-8).

Date fields show as numbers: Convert SAP’s YYYYMMDD format to a readable date using Excel formula: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)).

Duplicates found: Use Excel’s ‘Remove Duplicates’ feature, but first identify the key fields (e.g., customer number). If duplicates are valid (e.g., multiple addresses), ensure the target system can handle them.

Mapping errors: If a field doesn’t map cleanly, document it and decide whether to transform the data or leave it null. Test with a small sample before mass conversion.

Frequently Asked Questions

Can I export directly from SAP to the new ERP?

Yes, but it’s risky. Most ERPs require data in a specific import format, so you’ll likely need to transform the data using middleware or a custom script. Exporting to an intermediate file first gives you a chance to validate.

How do I know if my export is complete?

Compare row counts with SAP’s total record count. You can run a quick count via SE16N (the bottom status bar shows the number of rows) or use a custom ABAP count program.

What if I don’t have ABAP access?

Use standard SAP transactions like SE16N (if you have the role) or request a custom report from your SAP basis team. Alternatively, use SAP Query (SQ01/SQ02) to create a query without ABAP coding.

Should I export configuration data?

Yes, but only if your new ERP will use it. Configuration like chart of accounts and plant definitions is often recreated manually, but exporting it can serve as a reference.

How long does this process take?

It depends on data volume and complexity. A small company with a few thousand records might take a week; a large enterprise with millions of records could take several months. Start early and allocate dedicated resources.

What if I find errors in the source data?

Fix them in SAP before exporting, if possible. If not, document the errors and decide whether to correct them during transformation or after go-live. Never assume the new system will clean up dirty data.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →