Dataset Cleaner

Normalize messy CRM exports, scraper output, and lead lists before they move into analytics, enrichment, email tools, or AI pipelines.

This Actor takes an array of JSON rows, standardizes common fields, rejects rows that fail required checks, deduplicates after normalization, and writes an audit-friendly dataset containing cleaned rows, rejected rows, and one summary row.

What It Cleans

• Trims, collapses, and removes control characters from selected text columns or all string columns
• Normalizes email addresses by trimming and lowercasing, then validates with a pragmatic format check
• Parses phone numbers with libphonenumber-js, using a configurable default country for local numbers
• Outputs valid phone numbers in E.164 format and adds national format, country, and phone type metadata when available
• Normalizes websites, URLs, and domains by adding https:// when needed and lowercasing hostnames
• Converts unambiguous dates to YYYY-MM-DD
• Converts safe numeric strings such as $1,234.50 to numbers
• Removes empty rows and deduplicates by one or more normalized fields
• Adds row-level changes, warnings, errors, and summary counts

Phone validation runs locally with the bundled library. No external API, paid provider, or API key is required.

Example Input

{
  "data": [
    {
      "name": "  Alice   Adams  ",
      "email": " ALICE@EXAMPLE.COM ",
      "phone": "(415) 555-2671",
      "website": "Example.COM/contact",
      "revenue": "$1,234.50"
    },
    {
      "name": "Alice Adams",
      "email": "alice@example.com",
      "phone": "+1 415 555 2671",
      "website": "https://example.com/contact",
      "revenue": "1234.50"
    },
    {
      "name": "Broken Lead",
      "email": "broken@example.com",
      "phone": "not a phone",
      "website": "broken.example"
    }
  ],
  "dedupKeys": ["email"],
  "phoneColumns": ["phone"],
  "numberColumns": ["revenue"],
  "requireValidPhone": true,
  "outputRejectedRows": true
}

Example Output

[
  {
    "__rowType": "cleaned",
    "name": "Alice Adams",
    "email": "alice@example.com",
    "phone": "+14155552671",
    "website": "https://example.com/contact",
    "revenue": 1234.5,
    "phone_national": "(415) 555-2671",
    "phone_country": "US",
    "phone_type": "FIXED_LINE_OR_MOBILE",
    "__changes": [
      { "field": "name", "operation": "text-clean", "before": "  Alice   Adams  ", "after": "Alice Adams" },
      { "field": "email", "operation": "email-normalize", "before": "ALICE@EXAMPLE.COM", "after": "alice@example.com" },
      { "field": "phone", "operation": "phone-normalize", "before": "(415) 555-2671", "after": "+14155552671" },
      { "field": "website", "operation": "url-normalize", "before": "Example.COM/contact", "after": "https://example.com/contact" },
      { "field": "revenue", "operation": "number-normalize", "before": "$1,234.50", "after": 1234.5 }
    ],
    "__warnings": []
  },
  {
    "__rowType": "rejected",
    "__errors": ["Duplicate row by email"],
    "__warnings": [],
    "__originalRow": {
      "name": "Alice Adams",
      "email": "alice@example.com",
      "phone": "+1 415 555 2671",
      "website": "https://example.com/contact",
      "revenue": "1234.50"
    }
  },
  {
    "__rowType": "rejected",
    "__errors": ["Invalid phone in phone"],
    "__warnings": [],
    "__originalRow": {
      "name": "Broken Lead",
      "email": "broken@example.com",
      "phone": "not a phone",
      "website": "broken.example"
    },
    "name": "Broken Lead",
    "email": "broken@example.com",
    "phone": "not a phone",
    "website": "https://broken.example/"
  },
  {
    "__rowType": "summary",
    "originalRows": 3,
    "cleanedRows": 1,
    "rejectedRows": 2,
    "duplicateRows": 1,
    "invalidPhones": 1,
    "changedFields": 10
  }
]

Important Options

• textColumns: specific fields to trim and clean. Leave empty to clean all string fields.
• emailColumns: specific email fields. Leave empty to auto-detect field names containing email.
• phoneColumns: specific phone fields. Leave empty to auto-detect phone, mobile, and tel fields.
• defaultPhoneCountry: ISO-2 country code used when a phone number has no + country prefix. Defaults to US.
• requireValidEmail and requireValidPhone: reject bad rows when enabled. Otherwise, keep the row and add warnings.
• urlColumns: specific URL/domain fields. Leave empty to auto-detect url, website, and domain fields.
• dateColumns and numberColumns: explicit fields to normalize as dates or numbers.
• dedupKeys: one or more fields used together for duplicate detection after normalization.
• outputRejectedRows: include rejected rows in the dataset with errors and original input.
• includeCleaningMetadata: include __changes and __warnings on cleaned rows.

Output Rows

Every output item has a __rowType:

• cleaned: accepted normalized row
• rejected: dropped row with __errors, optional __warnings, and __originalRow
• summary: final row with counts for input rows, cleaned rows, rejected rows, duplicates, warnings, and invalid field types

Common Use Cases

• Clean CRM imports before sending leads to HubSpot, Salesforce, Pipedrive, Airtable, or outreach tools
• Normalize scraper output before exporting CSVs or loading a warehouse
• QA lead generation results by rejecting invalid phone numbers or emails
• Prepare consistent structured data for dashboards, enrichment, embeddings, and AI workflows