Planning a Prophet 21 data import

A data import into Prophet 21 is rarely hard because of the file format. It is hard because records depend on other records, and because the import tools will sometimes do something other than what you assumed. It is also hard because "the load finished" is not the same as "the data is right." We follow the plan below whether the source is a legacy ERP, an accounting package or a spreadsheet someone has maintained for 10 years.

It applies to go-live migrations and to large one-off loads into a live system, such as a new product line or an acquired customer list.

The three rules of a trustworthy import

Every import we run follows three rules.

Rule	Why
Load in dependency order	A record cannot reference something that does not exist yet
Rehearse in a non-production environment until it is boring	The first load always teaches you something. Learn it in play
Reconcile before you declare victory	Counts and balances must tie back to the source, in writing

Everything below is detail on those three.

Load in dependency order

Prophet 21 records form a graph. Customers reference terms, tax settings and salespeople. Items reference product groups, units of measure and suppliers. Open orders reference customers, items and locations.

Load a child before its parent and the import either rejects the row or, worse, fills a default you did not intend.

The usual order, from the ground up:

Tier	What	Why it comes here
1. Foundation	Company, accounting periods, chart of accounts, locations	Nearly everything posts to or belongs to these
2. Code tables	Terms, carriers and ship methods, tax setup, salespeople, units of measure, product groups, classes	Master records reference them by code
3. Parties	Customers and ship-to addresses, suppliers and their vendor records, contacts	Transactions and items reference them
4. Items	Item master, then item-location records, then item-supplier links	Stock, cost and purchasing depend on all three
5. Pricing	Price pages, contracts, customer-specific pricing	Needs customers, items and suppliers
6. Balances and open transactions	Inventory quantities and cost, open receivables, open payables, open sales orders, open purchase orders	Must reference everything above

On an empty system, the foundation tier is required. On a new install, creating suppliers or customers can fail with unhelpful errors until accounting setup exists.

Open balances come last and are loaded as balances. For go-live, you almost never migrate transaction history as live transactions. You bring open items forward and keep history available for lookup elsewhere.

Write your own version of this table for the project, with the file for each tier and the person who owns its data. That table becomes the load runbook.

Choose the load method per object

Prophet 21 offers more than one way to get data in: the built-in import windows and layouts, APIs and scheduled imports that pick files up from a folder. For a migration, the right choice varies by object:

Method	Suits
Import layouts	Large volumes of master data where a standard layout exists. Usually the fastest route
APIs	Objects that need application logic applied as if a user entered them, or per-record error handling
Entry in the application, or a small script against an API	Objects with no convenient path

Test how the load matches existing records

Some load paths will look up an existing record by a business key and update it instead of creating a new one. That is useful on purpose and damaging by accident. Never assume a load is create-only until you have proven it in play.

Do not load by direct inserts into Prophet 21 tables. They skip application logic, leave related tables inconsistent and are hard to unwind.

Rehearse until it is boring

Plan for at least three full rehearsals in a non-production environment that was refreshed from a clean baseline:

1. Discovery load. Expect failures. The goal is a list of every rejection and every field that did not land where you expected.
2. Corrected load. Apply the mapping and cleansing fixes. Rejections should be rare and explained.
3. Dress rehearsal. Run it exactly as you will on cutover day, from the same scripts, in the same order, with the same people. Time every step.

For each rehearsal, reset the target to the same baseline. Loading on top of a previous attempt hides problems, because the second load finds records the first one created.

Keep a log for every run: files and their row counts, start and end times, rejections with reasons and what changed before the next run. The log is how you estimate the cutover window and how you prove the process is stable.

Fix bad data in the source or the mapping

When a rehearsal exposes bad data (duplicate customers, items with no unit of measure, suppliers with no remit address), fix it upstream: in the source system, or in a documented transformation step. Do not fix it by hand in the play environment. Hand fixes vanish on the next refresh and resurface on cutover day.

Reconcile before you call it done

A load is complete when it reconciles, not when the progress bar stops. Decide the reconciliation checks before the first rehearsal, and agree with the business owner which ones must pass.

Check	How	Pass condition
Record counts	Rows in the source extract versus records created in P21, per object	Match, or every difference explained
Spot checks	A sample of records compared field by field, chosen by the business, not the loader	No unexplained differences
Inventory	Quantity and extended value by location, source versus P21	Ties within an agreed tolerance
Receivables	Open AR total and aging buckets by customer	Ties to the source aging at cutover
Payables	Open AP total by supplier	Ties to the source at cutover
General ledger	Opening trial balance	Ties to the source trial balance
Open orders	Count and value of open sales and purchase orders	Match

The financial checks are the ones that decide whether finance will sign off, so bring the controller in early and use their reports as the source numbers.

Write the reconciliation up. A short document showing each check, the source figure, the P21 figure and the explanation for any difference is what turns "we think it is right" into a go-live decision.

Cutover day

If the rehearsals did their job, cutover is the least interesting day of the project:

1. Freeze the source system at an agreed time.
2. Extract, transform and load in the rehearsed order.
3. Run the reconciliation checks.
4. Hold a go or no-go decision with the business owner against the agreed pass conditions.

Agree the rollback plan in advance. If a check fails, you need to know before the day starts whether you will fix forward or stay on the old system.

Common import mistakes

• Loading open transactions before the masters they reference are verified.
• Treating a successful load status as proof of correctness.
• Cleansing data by hand in the target instead of in the source or mapping.
• Skipping rehearsal on "small" objects like terms or carriers, which then block larger loads.
• Reconciling against numbers the migration team produced rather than numbers the business already trusts.