Practical Tutorial: Syncing Excel Student List to Kingdee Customers via Qeasy

What This Strategy Solves (Scenario and Value)

In one real project, an education-services company was managing enrolled students in an Excel roster and treating them as quasi-customers, then later needed to issue invoices and reconcile in Kingdee Cloud. Excel was maintained offline, manual import was slow, and duplicates plus wrong organizations were common. A task that should have been one strategy was instead a weekly chore.

This strategy uses the Qeasy Data Integration Platform to treat Excel as a source system, pull deltas via a time-windowed QueryStrategyData, then write them to Kingdee's batchSave. Configure once, run automatically, only watch the error queue afterwards.

Data Flow and Field Mapping (Source → Middle Layer → Target, Key Field Table)

The flow is unidirectional: Excel source → Qeasy middle layer → Kingdee Cloud customer archive. The source is a strategy-based query, the target is a batch save, and the middle layer does field assembly, code mapping, and deduplication.

The source request uses {{LAST_SYNC_TIME}} for created_at_begin and {{CURRENT_TIME}} for created_at_end — this is the standard pattern for an incremental window, explained further below.

How to Configure on Qeasy (Typical Configuration Points)

Source strategy (query): API = QueryStrategyData, type RESTful, effect QUERY. The key is strategy_id, required, which points to the actual strategy holding the Excel data. status is typically 0,3 ("waiting" + "errored" for retry). Keep idCheck on to avoid pulling the same row twice.

Target strategy (write): API = batchSave, effect EXECUTE. FNumber binds to the student id, and FName must be assembled into a multi-language array object per Kingdee's structure (English 1033, Chinese 2052). Many customers miss the foreign-language key on their first attempt, and the Kingdee side ends up with only the Chinese value.

Variable management: A common pattern at Qeasy customers is "centralized code mapping management" — all orgs, customer categories, currencies and similar constants live in a variable table; field mappings only reference variable names, so changing one place propagates across the chain.

Implementation Steps (Phased Scheduling)

Step 1, Initialization. Run one manual full sync to push all historical students into Kingdee. Keep the incremental start disabled here and use a fixed wide time window as a safety net.

Step 2, Set the incremental start point. After the full sync completes, switch the source created_at_begin to {{LAST_SYNC_TIME}} so that only new/changed rows are pulled from then on. This is the pivot from "full + incremental dual track" to "incremental-led".

Step 3, Configure the schedule. Source crontab = 3 8,15 * * * (08:03 and 15:03 daily). Offset the target by 2 minutes to 5 8,15 * * *. The stagger lets the source buffer stabilize the batch before the target picks it up.

Step 4, Monitoring and re-runs. Each day, inspect Qeasy run logs. Rows with status 3 (errored) should either be retried or fixed manually and re-pushed; rows with status 1 (duplicate) usually indicate a code-mapping conflict.

Lessons from the Field

1. FName written as a single string instead of multi-language array. The typical mistake is concatenating FName into one long string — Kingdee then reads it under the default language and the English environment ends up with a blank name. The safe approach is to assemble [{"Key":1033,"Value":...},{"Key":2052,"Value":...}].

2. Forgetting to switch to the incremental start point. After the full sync, if you keep using a fixed time window, the same historical batch is pulled repeatedly, wasting API quota and triggering Kingdee's duplicate checks.

3. Hardcoding organization codes. Writing FCreateOrgId and FUseOrgId as literal constants means every new business unit requires editing a pile of strategies. Any field with an "organization" semantic in the header should go through a variable — this "header/body staged" pattern is essentially standard among Qeasy multi-org customers.

4. Status filter too narrow. Setting status to just 0 permanently stalls errored rows. The recommended value is 0,3 so errored rows re-enter the retry queue, and after manual intervention they flip to 2 (done).

5. Time-window drift across time zones. created_at_begin/end uses the source system's time zone, so under cross-timezone scheduling the window can "drift" past data. The safe approach in Qeasy is to unify the timestamps to UTC before handing them to the source for parsing.

Where This Applies and Where It Doesn't

Applies: Excel/CSV as master data source, incremental time-window sync into ERP customer archive, ten-thousand-row scale, fixed org dimension, small/medium business. Does not apply: high-concurrency OLTP source (use CDC, not Excel as a relay); customer archives with complex approval workflows and >50 fields (exceeds a single strategy's capacity); cross-legal-entity scenarios requiring differentiated mapping.

Additional Notes

• Name strategies with "source → target" semantics, e.g. "Excel Student → Kingdee Customer", for easier search later.
• Keep idCheck always on — it is the lightest duplicate-data defense on the Qeasy side.
• Don't delete errored rows directly; first flip them to status=0 and re-run, then determine whether the cause is data or mapping.

Key Takeaways (English Summary)

1. Source QueryStrategyData paired with target batchSave is the canonical pattern for Excel-to-ERP master-data sync.
2. Always build FName as a multi-language array (1033/2052), never as a single string.
3. After the initial full sync, switch created_at_begin to {{LAST_SYNC_TIME}} to enable true incremental sync.
4. Stagger the target schedule by ~2 minutes so the source batch can stabilize.
5. Use the 0,3 status filter so errored rows re-enter the retry queue.