Getting your data out of Prophet 21® (on-premise)

If you run Prophet 21® on-premise, here's the good news nobody leads with: your data is already yours, in your building, in a standard Microsoft SQL Server database. Epicor describes it as an open SQL Server database, and it is — no proprietary file format, no vendor gatekeeping the extract. Of the legacy systems we see, on-prem P21 is one of the cleanest to get data out of.

Any conversion needs three things: access to the raw data, a way to export it to CSV or Excel, and the table layouts so you know what each field means. P21 gives you all three.

1 · Where the raw data lives

Everything is in SQL Server tables, organized the way the P21 screens are. The names are readable once you know the pattern — orders split into a header table and a lines table:

• oe_hdr / oe_line — sales orders and their lines
• invoice_hdr / invoice_line — invoices
• inv_mast — the item master
• plus customer, supplier, and address tables

P21 also ships pre-built SQL views that join the related tables for you, which saves whoever does the extract from working out the joins by hand.

2 · Three ways to export

SQL Server Management Studio (the full extract)

SSMS is Microsoft's free database tool, and it's the right way to pull complete tables. Whoever admins your server connects to the P21 database, runs a query, and saves the results to CSV. This is the path for full order history — everything, not just what a screen shows.

ODBC (the familiar-tools route)

Because it's plain SQL Server, Excel, Access, and Power BI can connect directly through a standard ODBC or OLE DB connection and pull tables themselves. If your office already has someone who builds Excel reports against P21, they already have everything they need.

Right-click grid export (the quick lists)

Inside P21, most search and inquiry grids support right-click → Export to Excel, and "Select Visible Columns" controls what you get. Two catches worth knowing:

• Two checkboxes in User Maintenance gate this and are off by default ("Allow RMB Save As to automatically open the file" and "Allow RMB Save As to save selected columns"). Turn them on, then fully exit and reopen P21.
• Grid exports only capture what's on screen after your filters — fine for a customer list, the wrong tool for ten years of order history.

3 · Getting the table layouts

• Epicor publishes an official P21 data dictionary on its customer portal (EpicWeb/EpicCare) — table and column descriptions, including possible field values. You need your customer login.
• No portal access? SQL Server describes itself: querying INFORMATION_SCHEMA.COLUMNS in SSMS lists every table and field name in your actual database — which beats documentation anyway, since names can vary a little between P21 versions.

What to pull for a migration

For a move into a distribution system, this is the shopping list: customers and ship-tos with their contacts, the item master, price tables and quantity breaks, customer part-number cross-references, open orders, open A/R, open POs, vendors, and as many years of order history as you want to keep. (Then verify the extract — here's the verification routine: counts first, then tie out open order value, A/R, A/P, and open PO balances.)

Prophet 21® is a registered trademark of its owner. Its use here is for identification only and implies no affiliation or endorsement.