Ecommerce order CSV cleanup for reporting and import
Order exports drive fulfillment, refunds, revenue reporting, and inventory reconciliation. A small formatting issue in order totals or dates can create mismatches across several tools.
Protect order identifiers
Order IDs, SKUs, tracking numbers, and payment references should be treated as text even when they look numeric. They may contain leading zeros, prefixes, or marketplace-specific separators.
- Check that order_id remains unique after cleanup.
- Do not remove leading zeros from SKUs or tracking references.
- Keep line-item identifiers if one order can contain multiple rows.
Normalize dates and totals
Ecommerce systems can export regional date formats and totals with thousands separators. Normalize order dates to ISO format and remove number separators only after confirming the column is truly numeric.
- Convert order_date, paid_at, shipped_at, and refunded_at consistently.
- Keep currency code in a separate column when possible.
- Compare total revenue before and after cleaning to catch accidental parsing changes.
Clean fulfillment status
Status fields often arrive with mixed labels such as Shipped, shipped, fulfilled, and complete. Map them before dashboarding so open orders and completed orders are not split across duplicate categories.
- Use a fixed status list for pending, paid, fulfilled, cancelled, refunded, and returned.
- Keep blank status values for manual review.
- Export a small sample and verify it in the warehouse or reporting tool first.
Recommended local workflow
- Load the order export sample on the main page or paste your own export.
- Use delimiter auto-detection when the source comes from a regional spreadsheet.
- Enable header normalization, duplicate removal, and date/number cleanup.
- Download the change log and compare row count, order count, and total revenue.
Example QA checks before import
After cleaning, compare three totals against the original platform export: number of unique orders, number of line items, and gross order value. If any count changes unexpectedly, inspect duplicate removal and number parsing before using the file for fulfillment or finance.
- Order IDs: should remain text when they include prefixes, marketplace codes, or leading zeros.
- Line items: should not be deduplicated when two rows share the same order but different SKU.
- Totals: should be numeric only after currency symbols and thousands separators are handled deliberately.
- Statuses: should map to a small list that operations and finance both understand.