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.