Logistics and distribution
SAP Business One data export: getting orders, items and history out
By SourceX Editorial · Updated
Short answer
SAP Business One data export works best from a map of business objects to their database tables and the right route for each job. Grid exports and saved queries suit small pulls; a full migration or archive needs read-only SQL or the Service Layer, with the links between quotes, orders, deliveries, invoices and returns preserved.
Key takeaways
- Each B1 sales or purchasing document has a header table and a lines table, and both are needed for a usable export.
- Base document fields on each line carry the chain from quote to order to delivery to invoice or return.
- Saved queries suit recurring reports; a migration or archive needs read-only SQL against a copy or the Service Layer.
- Keep the legacy database readable after cutover, because migrations usually move open items and balances, not full history.
- Remarks, activity notes, attachments and user-defined fields hold the most context and the most sensitive content.
What can you export from SAP Business One?#
SAP Business One can export every business object it stores, because each one lives in documented tables in the company database. The practical question is which objects you need and how they link, not whether the data can come out.
Your partner may also have added user-defined fields and tables. List those separately, because they often hold business-specific context such as job numbers, approval codes or delivery instructions that the standard fields miss. Confirm table names against your own version and database, since add-ons and partner customizations vary.
| Business object | Header and lines tables | What it holds |
|---|---|---|
| Sales quotations | OQUT, QUT1 | Quoted items, prices, validity, sales employee |
| Sales orders | ORDR, RDR1 | Ordered items, quantities, prices, dates, ship-to |
| Deliveries | ODLN, DLN1 | Shipped quantities, warehouse, batches or serials |
| Returns | ORDN, RDN1 | Returned items and quantities; reasons often sit in remarks or user-defined fields |
| A/R invoices | OINV, INV1 | Billed lines, tax, payment terms |
| A/R credit memos | ORIN, RIN1 | Credits, often tied to returns or pricing disputes |
| Purchase orders and goods receipts | OPOR, POR1; OPDN, PDN1 | Supplier orders, received quantities, costs and warehouses |
| Item master data | OITM, with OITW by warehouse | Items, groups, units of measure, stock by warehouse |
| Business partners | OCRD, with OCPR for contacts | Customers, vendors, contacts, payment terms |
| Special prices | OSPP, with SPP1 for period and volume discounts | Customer-specific prices and discounts, often the most sensitive pricing |
| Activities | OCLG | Calls, meetings, notes and tasks linked to partners and documents |
| Service calls | OSCL | Customer issues, resolutions and linked items |
Which export route fits which job?#
The right SAP Business One export route depends on volume and purpose: grid exports for one-off lists, saved queries for repeatable reports, and read-only SQL or the Service Layer for migrations and archives. Using the wrong route is the most common reason exports come out incomplete.
For a full history, restore a recent database backup to a separate server and extract from that copy. It avoids load on the live system and gives a fixed point in time to reconcile against, which matters when auditors or a new ERP team later ask where a number came from.
| Route | Best for | Watch out for |
|---|---|---|
| Export from a grid to Excel | Quick lists and spot checks | Only what the grid shows; large exports get unwieldy |
| Query Generator and saved queries | Repeatable reports joining header and lines | Needs SQL skill; heavy queries slow the live system |
| Crystal Reports | Formatted documents and recurring reports | Built for presentation, not bulk extraction |
| Service Layer or DI API | Structured extraction by object | Needs a developer and a license-compliant setup |
| Read-only SQL against a restored copy | Full migration or archive | Use a separate server; never write to production |
| Data Transfer Workbench | Loading data into B1 | Primarily an import tool, not a history export |
How do you keep orders, deliveries and returns linked?#
Orders, deliveries and returns stay linked in an export when you keep the base document fields on every line. In SAP Business One, each document line records the document it was copied from through base type, base entry and base line fields, which is how B1 traces a delivery back to its order and a return back to its delivery.
Drop those fields and the export becomes separate piles of documents. Keep them, and you can rebuild the full chain for any order: quote, order, partial deliveries, invoice, return and credit memo.
- Export header and lines tables for every document type in scope, keyed on DocEntry.
- Keep BaseType, BaseEntry and BaseLine on every lines table.
- Include cancelled documents and their flags, since cancellations explain gaps in the chain.
- Export activities with their linked partner and document fields.
- Pull attachment metadata and copy the attachments folder from the file server.
- Reconcile document counts and totals by year against B1's own reports.
What should happen to the legacy B1 database after migration?#
The legacy SAP Business One database should stay readable after migration, because ERP migrations usually bring over master data, open items and balances rather than full transaction history. Whether you move to SAP S/4HANA Cloud, NetSuite, Acumatica or another platform, closed orders, deliveries, returns and activities usually stay behind.
Three options cover most cases: keep a read-only B1 instance, which may still need licensing, so check with your partner; extract history into a reporting database or warehouse with document links intact; or keep encrypted backups plus a documented extract. Many teams combine the last two.
Before the server is retired, record the B1 version, add-ons, user-defined field definitions and the attachments folder path. A backup without that context is hard to restore when someone needs it years later.
Which B1 fields carry sensitive content?#
The B1 fields that carry the most sensitive content are free text: document remarks, activity notes, contact records and user-defined fields. They also carry the most context, which is why they are reviewed rather than dropped by default.
Business partner and contact tables hold names, emails and phone numbers. Remarks and activity notes can include pricing promises, complaints and personal details. Attachments may include signed contracts, customer POs and credit applications with financial information.
For an internal migration, those fields move as they are under your existing controls. For any outside use, including licensing, they need review: names and contacts are replaced with tokens, customer-specific pricing is generalized, and attachments are excluded unless specifically scoped.
Illustrative: a fastener distributor leaves SAP Business One#
Illustrative: a fictional fastener and industrial supply distributor with about 140 employees runs SAP Business One on SQL Server with 12 years of quotes, orders, deliveries and returns. It is moving to a cloud ERP, and the new system will take open orders, items, partners and balances only.
The IT director restores a backup to a separate server, extracts header and lines tables for every sales and purchasing document with base document fields, exports activities and service calls, and copies the attachments folder. A reconciliation report compares counts and totals by year with B1's own reports.
The CFO then asks whether closed history has value beyond reporting. A metadata review shows linked quote-to-return chains and activity notes explaining substitutions and backorders; customer names and special prices would need removal before any licensing discussion.
Can legacy B1 history be licensed?#
Legacy SAP Business One history can sometimes be licensed to AI developers when it shows real decisions with outcomes and the company holds the rights to use it. Order-to-cash chains, returns with reasons, and activities explaining substitutions or delays are the kinds of records developers of procurement, order management and enterprise agent models may look for, though interest depends on depth, linkage and rights.
SourceX assesses that history with the SourceX Enterprise Data Value Framework and handles any license through the SourceX five-step transaction: Supply, Rights, Preparation, Approval and Delivery. The first fit check uses metadata only, customer and supplier details are removed in Preparation, and the company approves every step. Data is licensed, not sold, and the archive can stay in the company's own storage.
Frequently asked questions
Can I export SAP Business One data without my partner?
Usually yes for grid exports and queries, if your user has the right authorizations. Direct SQL access, the Service Layer and DI API can raise licensing and support questions, so check your agreements and involve your partner before building automated extraction against the company database.
Could an earlier archiving run affect my export?
It can. If anyone has run an archive process that removes closed documents from the company database, older transactions may exist only in an archive database or an older backup. Check with your partner and the system history before assuming the live database holds every year.
Should exports include cancelled documents?
Yes. Cancelled orders and deliveries explain gaps and reversals in the document chain, and they often record customer changes, pricing errors or stock problems. Keep cancellation flags and dates so anyone reading the archive can tell a cancelled document from an active one.
How should we handle user-defined fields?
Export them with the tables they extend and document what each one means. User-defined fields often hold job numbers, approval codes or delivery instructions. Without definitions they become unexplained columns, so capture descriptions from the B1 setup before the system is retired.
What format should the exported history use?
Use CSV or a database dump in an open format, one file or table per B1 table, plus a data dictionary and a note describing keys and links. Keep the original backup too, along with the B1 version and add-on list, in case a full restore is ever needed.
Related resources
See if your company qualifies
A short company assessment. No data uploads are needed.