Validate Salesforce Picklist Fields Before CSV Import
Salesforce administrators configure Country and State/Province fields as dependent picklists with strict value restrictions — meaning the system only accepts exact matches from a predefined list. When importing leads from Apollo, ZoomInfo, or event badge scans, the source data invariably contains variations: United States, US, U.S.A., USA, and United States of America all refer to the same country, but Salesforce will reject any value that doesn't exactly match the configured picklist value. A single mismatched picklist value causes the entire import batch to fail — Salesforce doesn't skip invalid rows and continue; it rejects the entire file with an error log listing each problematic row. This workflow cross-references your Country and State columns against Salesforce's standard picklist values, applies regex-based normalization to map common variations, and generates a validation report.
Why this matters?
A demand generation team importing 14,200 trade show leads into Salesforce encountered a complete import rejection because 1,847 rows contained United States variations that didn't match the org's configured picklist value. The Salesforce admin spent 4 hours manually mapping country and state variations in Excel using nested SUBSTITUTE formulas, only to have the second import attempt fail again because 312 rows had Quebec instead of the picklist's QC abbreviation. The two-day import delay meant the sales team missed the critical 48-hour post-event follow-up window, and the event's ROI dropped by an estimated 40% based on historical conversion data. Maintaining a VLOOKUP mapping table for 250+ country variations across every import is operationally unsustainable in Excel.
The 3-Step Solution
Follow this streamlined workflow to transform your raw data export into a clean, analysis-ready dataset. Each step leverages our browser-based tools to ensure your sensitive data never leaves your device.
By following these three steps, you eliminate manual data wrangling, reduce human error, and maintain full GDPR compliance throughout the process.
Ready to clean your data?
100% local processing. Zero uploads. Blazing fast.
Recommended Tools
Related Workflows
How to Anonymize Customer Data Before Sharing with ChatGPT or Developers
A step-by-step SOP for stripping PII (names, emails, phone numbers, physical addresses) from production datasets while preserving the statistical shape of the data. Includes guidance on which columns to mask, which to drop, and how to verify the output is truly de-identified.
How to Convert Stripe Payout Reports into QuickBooks Import Format
Stripe's payout CSV has 15+ columns that don't map to QuickBooks' expected schema. This guide shows how to separate gross revenue from processing fees, reformat dates to MM/DD/YYYY, and produce a clean CSV that QuickBooks accepts without manual reconciliation.
Reconcile Facebook Ads Spend with Shopify Revenue
Attributing Facebook Ads spend to actual Shopify revenue is a nightmare because Shopify exports Name (e.g., #1042) and Order ID, but completely strips UTM parameters or Campaign IDs from the standard order CSV. Meanwhile, your Facebook Ads export groups spend by Campaign ID and Ad Set Name. You cannot directly VLOOKUP these two datasets. This workflow walks you through extracting UTM tags from Shopify's Note or Tags fields, parsing them with regex, and executing a memory-safe local VLOOKUP to bridge ad spend and realized revenue without touching a cloud server.
Clean WooCommerce Exports for Financial System Import
WooCommerce's native CSV export is notoriously hostile to accounting workflows. Product descriptions in the post_content column arrive wrapped in raw HTML tags and shortcodes like [woocommerce_reviews]. Custom meta fields (e.g., _regular_price, _sku) frequently shift columns when plugins inject hidden postmeta, and the post_date column alternates between Y-m-d H:i:s and Unix timestamps depending on your WordPress version. This SOP strips HTML remnants via regex, realigns displaced meta columns, and standardizes all date fields to ISO 8601 so QuickBooks and Xero accept the import without throwing schema validation errors.
Sample Datasets
Facebook Ads Campaign Report Sample
A Facebook Ads Manager export with 100 rows spanning 3 campaigns, 12 ad sets, and key metrics (impressions, clicks, spend, conversions, ROAS). Includes intentionally messy rows with zero-spend days and broken attribution windows for testing the Ads ROAS Pivot tool.
Stripe Payout Export Sample
A Stripe payout CSV with 80 transactions including charges, refunds, disputes, and fee breakdowns across USD and EUR. Timestamps are in raw UTC format. Use this to test the Stripe Payout Formatter and validate QuickBooks-compatible output.
Apollo.io B2B Leads Export Sample
An Apollo.io lead list export with 300 contacts including duplicate emails, role-based addresses (info@, admin@), invalid domains, and inconsistent company name casing. Perfect for testing the Apollo Leads Cleaner and CSV Deduplicator on a realistic dirty dataset.