Clean and standardize data for import

Not every workflow needs a match. Sometimes you just have a messy file and you want a clean one — standardized casing, normalized phone numbers, trimmed whitespace, uniform date formats, duplicates removed — so you can import it into Salesforce, HubSpot, NetSuite, or wherever it needs to go without spending an afternoon in Excel.

This guide walks you through using ListMatchGenie as a cleanse-only tool. No master file, no match — just clean output.

When to use this guide

Use this workflow when:

• You have a messy CSV or Excel file that needs to be cleaned before going somewhere.
• You don't need to look the data up against anything — you just want it standardized.
• You want the output in the same shape as your input (same columns, same rows, cleaned values).

Before you start

• Know which system you're going to import into — the ideal format for each destination is slightly different (Salesforce wants ISO dates, some platforms want MM/DD/YYYY, etc.).
• Trim columns you don't need. Smaller files cleanse faster and are easier to audit.
• Keep a backup of the original — ListMatchGenie never modifies your uploads, but you'll want to diff at the end.

The workflow

1. Upload the file

   From the dashboard, click New Match. Drop your file on the Source tile.

   For this workflow you don't need a master file. Skip the master slot entirely.

2. Fix the column profile

   The Genie profiles every column. Check each one and override if the detection is wrong:

   • A column of account numbers detected as "number" should be "identifier" (otherwise leading zeros get stripped)
   • A column of dates detected as "text" should be "date"
   • A column meant to be preserved exactly (SKU, code) can be marked "raw" to skip cleansing

   This is the most important step. Everything downstream uses the column profile.

3. Configure cleansing rules

   On the Cleanse step, the Genie shows you what it plans to do. You can:

   • Accept defaults — for most files, the defaults are right.
   • Tweak per-column — change date format interpretation, toggle abbreviation expansion, preserve specific casings.
   • Disable a rule — e.g. skip phone normalization if your downstream system has its own rules.

   Pay attention to the date format setting. The Genie defaults to US (MM/DD/YYYY) when ambiguous — if your file is European (DD/MM/YYYY), change it here.

4. Review the cleansing report

   The cleansing report shows exactly what will change. Read the summary narrative, then scan the issue list for anything surprising.

   If something looks off, go back and adjust column profiles or rules. This is the free-rejection point — once you advance, cleansing applies to the whole file.

5. Skip matching

   Since you don't have a master, advance through the Configure and Match steps without action. The Genie recognizes this pattern and offers a Skip matching option that jumps straight to Export.

   If your tier includes match jobs, a cleanse-only run doesn't count against that quota. Cleansing is unlimited.

6. Export the cleaned file

   Pick your format:

   • CSV — UTF-8 with BOM, works in Excel and every modern data tool.
   • XLSX — multi-sheet workbook with the cleaned data, a summary sheet, and the cleansing report.

   The output has the same columns in the same order as your input. Cleansed values replace the originals in the export (you can toggle to keep both as original_<col> and <col> if you want an audit column — see the "Include originals" toggle on export).

What cleansing does

The defaults cover most cases. In detail:

Text normalization

• Trim leading/trailing whitespace on every cell
• Collapse internal whitespace runs to a single space
• Remove zero-width and non-printing characters
• Normalize casing by column type (emails lowercase, names title case, codes uppercase)

Format standardization

• Phone numbers → digits-only, with country code preserved
• Dates → ISO 8601 (YYYY-MM-DD), with optional original format preserved
• ZIP codes → 5-digit US format with padding, optional +4 suffix retained
• SSN → dashed format with leading zero preservation
• Currency → numeric with currency code column split out
• Boolean-ish values (Yes/Y/true/1) → normalized to a consistent form

Abbreviation handling

• Street types: St → Street, Ave → Avenue, Blvd → Boulevard
• State codes: both 2-letter and full expanded forms supported, defaults to 2-letter
• Company suffixes: Inc., Incorporated, LLC, Ltd — normalized to a canonical form per column setting

Duplicate removal

• Exact duplicates removed by default
• Near-exact duplicates merged by default (keeping the most complete values)
• Fuzzy duplicates flagged but not removed — see the dedup report

Character handling

• Non-UTF-8 files auto-detected and converted
• Accented characters preserved in output, transliterated in a shadow column for matching
• CSV-injection-risk prefixes (=, +, -, @) escaped for Excel safety

What cleansing does NOT do

• Fill missing values. Empty cells stay empty.
• Guess at intent. If a column has mixed types (dates and strings), cleansing fixes each cell independently but won't unify them.
• Rename columns. Your column names are preserved exactly.
• Reorder rows. Row order is preserved (minus duplicates).

Common pitfalls

Leading zeros stripped

If account numbers, ZIPs, or codes get treated as numbers, leading zeros vanish. Always override these columns to identifier type.

European dates parsed as US

03/04/2026 is March 4th to the Genie by default. If your data is European, set the date format explicitly on the column.

Phone numbers with extensions

(555) 123-4567 x42 is ambiguous — is the extension part of the number? The Genie strips it by default and puts the extension in a shadow column (<col>_extension). Disable this on a per-column basis if you want the raw string preserved.

Related reading

• Cleansing report — detailed report structure
• Dedup report — the duplicate detection side of cleansing
• Encoding and characters — international data handling