ActiveManage Docs
← Back to activemanage.co.uk

File Import Routines

Overview of File Import Routines

File import routines are configurable workflows that take a file from an upload (or an SFTP drop, or an email attachment) and load its contents into a datastore. They're how you migrate from spreadsheets, sync from suppliers, or accept ad-hoc batch updates from users.

Flowchart: File source (upload/SFTP/email) → Validation (format, size) → Column mapping → Row transformations → Insert or update → Summary report

Supported Source File Formats

  • CSV (UTF-8, latin-1, with auto-detect)
  • TSV / pipe-delimited
  • Excel (.xlsx, .xls)
  • JSON (array of objects)
  • XML (with XPath mapping)
  • Fixed-width text

What Happens

  1. File is uploaded or fetched from the configured source.
  2. Format auto-detected; headers parsed if present.
  3. Each row mapped per the column-mapping rules to a destination row schema.
  4. Row-level transforms applied (date parsing, lookups, default values).
  5. Validation runs — required fields, type checks, referential checks.
  6. Action taken: insert new rows, update existing rows (matched by key), upsert (do both), or write to a staging table for review.
  7. Summary report generated with counts and per-row errors.

Worked Examples

  • Customer migration: One-off import of 50,000 customers from the old CRM's CSV export. Map columns, upsert by email, run.
  • Daily price feed: Supplier drops a CSV onto SFTP each morning at 04:00. Sync prices and stock levels onto the Products datastore.
  • User self-service bulk update: Sales rep uploads a CSV of leads they collected at a trade show; routine inserts into Leads with the rep's user ID attached.
  • Email-attachment import: Reports@ mailbox receives daily XLSX files; routine parses and feeds into the reporting datastore.

Mapping Columns to Datastore Fields

The mapping step is where source columns become target fields. Good mappings are accurate, robust to slightly-different source variants, and contain enough transforms to turn raw input into clean data.

Column mapping UI showing source column 'Cust Name' mapped to target field 'name', 'EMAIL' mapped to 'email' with transform 'lowercase + trim', 'Sign-up Date' mapped to 'signed_up_at' with transform 'parse date DD/MM/YYYY'

How to Map

  1. Open the routine and click Mapping.
  2. Each source column appears with a “Maps to” dropdown of target fields.
  3. Pick the target. Some mappings are auto-suggested by name similarity.
  4. Optionally add a transform pipeline (per column or per row).
  5. Mark required source columns — if missing in a future file, the import is aborted.

Useful Transforms

  • Trim whitespace — removes accidental spaces around values.
  • Case conversion — lowercase for emails, upper for codes.
  • Date parsing — specify input format (DD/MM/YYYY, MM/DD/YYYY, ISO).
  • Number parsing — handle thousand separators and decimal points by locale.
  • Lookup — replace a value via a lookup table (e.g. country code → country name).
  • Default if blank — provide a fallback for missing values.
  • Custom function — PHP snippet for anything else.

Handling Variant Files

Real suppliers occasionally rename columns or change layout. Make mappings tolerant:

  • Match column names case-insensitively.
  • Accept alias names (e.g. “Email” or “E-mail” or “Email Address” all map to email).
  • Ignore unknown extra columns rather than erroring.

Worked Examples

  • Customer import: Map “Cust Name” → name, “EMAIL” → email (lowercase + trim), “Sign-up Date” → signed_up_at (parse DD/MM/YYYY).
  • Product feed: Map “SKU” → sku (upper-case), “PRICE (USD)” → price_usd (number with comma separator), “Country” → country (lookup table converting full names to ISO codes).
  • Bulk address import: Map source columns plus a custom transform that splits “London, UK” into city and country.