How to export your data from SAP Business One

SAP Business One runs on either Microsoft SQL Server or SAP HANA, and in most smaller installations you or your reseller already have database access. That makes it one of the friendlier legacy systems to leave — once you get past the table naming, which is where everyone loses their afternoon.

1.Pin the cutover, then pull five things

On-hand is only true at an instant, so agree a freeze point and pull quantities after it — then either key the gap in or reconcile with a cycle count on day one. What you want: item master (with the inactive flag), on-hand per item as of a stated time, average cost, customer and vendor master with addresses and terms, and open orders if the cutover isn't on a quiet day.

2.Three routes

Route A — Query Generator (recommended)

1. Open Tools → Queries → Query Generator, or Query Manager if you want to save it — you will want a second pull at cutover.
2. Write a SELECT against the master tables in §3 and execute it.
3. From the results grid use Copy Data, or File → Export → MS Excel.

Route B — Export any grid (small catalogues)

Open Item Master Data or Business Partner Master Data, enter * in the code field to list every record, then export the grid to Excel. Fast, but limited to the columns the list happens to show.

Route C — Direct SQL

If you have read access to the company database, query it directly — the most complete route. Note that Data Transfer Workbench is an import tool, not an export tool. Its templates are useful as a reference for what fields exist, but it will not pull data out.

3.The tables that matter

4.Gotchas

CardType is your party_type

OCRD mixes customers and vendors in one table. Split on CardType, map C → customer and S → vendor. Leads (L) are usually not worth bringing across.

Addresses are rows, not columns

CRD1 holds many addresses per partner. Pick the default ship-to and bill-to and concatenate the parts into our single free-text address fields — don't assume one row per partner.

Prices are not on the item

They live in ITM1, one row per price list. Ask which price list is the real selling price before you pick one; picking the wrong list is invisible until a customer queries an invoice.

HANA is not T-SQL

HANA installs need HANA SQL. The field names are identical; the syntax around them is not. A query written against a SQL Server install will not simply run on HANA.

5.What happens on our side

Two spreadsheets — items, and parties — matched on sku and name, so a re-import updates rather than duplicates. Import vendors before items so the vendor column links instead of warning.

AegisSwift costs inventory on a moving average, seeded from your opening cost — which is why AvgPrice from OITW is the column to bring. If your books use a different method, expect a permanent small divergence by design rather than a reconciliation error.

6.Questions people ask

Do I need a consultant to get my data out?

Only if nobody has Query Generator or database access. For a small catalogue the grid export in Route B needs neither.

Can I use Data Transfer Workbench?

Not for exporting — it only imports. Its templates are useful documentation, nothing more.

How long does this take?

Half a day for a typical install, most of it spent confirming which price list and which warehouse rows are authoritative. Expect to import twice.