DEV Community

April
April

Posted on

How to Clean a Messy Stripe / Shopify / Gumroad CSV Before Google Sheets (or Make)

#csv #spreadsheet #productivity #solopreneur

If you sell online, you've probably exported a CSV from Stripe, Shopify, Gumroad, or a CRM and dropped it into Google Sheets — only to watch duplicates pile up, phones arrive in five formats, and Make mappings break on header names with spaces.

This is a practical walkthrough of how I clean those exports by hand. Every step works in Sheets (or Excel) with no special tools. At the end I'll mention a small browser helper for when the file is big enough that manual cleanup gets tedious.

Who this is for: solopreneurs and ops folks who need a Make- or Sheets-ready file before an import — not a data-engineering pipeline.

What "messy" usually looks like

A typical export mixes:

  • Headers with spaces or Title Case (Full Name, Customer Email)
  • Leading/trailing spaces in cells
  • Blank rows left over from the export
  • Duplicate people (same email after a refund + repurchase, or a double export)
  • Mixed-case emails (Jane.DOE@Example.com)
  • Phones in every format ((555) 123-4567, 555-123-4567, +1 555 123 4567)

Tiny example:

Full Name,Email,Phone,Notes,Amount,Status
Jane Doe,jane.DOE@Example.com,1-555-123-4567,"VIP , client",49.00,paid
Jane Doe,jane.doe@example.com,5551234567,duplicate row,49,paid
  John Smith  , JOHN@ACME.IO ,(555) 987-6543,,120.50,paid
,,,,blank row should go,
Ava Lee,ava@shop.io,555.222.3333,Shopify export,19.00,paid
Enter fullscreen mode Exit fullscreen mode

Same person twice, inconsistent phones, mixed-case emails, a blank row, and headers that Make will choke on if you map them literally.

Step 1 — Fix the headers first

Before you touch rows, make column names boring and predictable: snake_case, no spaces, lowercase.

Before After
Full Name full_name
Email email
Phone phone
Amount amount
Status status

Freeze the header row, rename by hand (or paste values from a helper row). Make field mappers and Sheets QUERY ranges stay stable when headers are identifiers, not display labels. Keep a one-row "dictionary" tab mapping original export headers → your canonical names so the next Stripe dump is faster.

Step 2 — Trim whitespace and drop blank rows

Trailing spaces look identical in the UI and fail equality checks. For a text column:

=TRIM(A2)
Enter fullscreen mode Exit fullscreen mode

Fill down, then Paste special → Values only over the original.

Remove empty rows: Data → Create a filter → on email, filter Blanks → delete those rows → clear the filter. Also catch rows that are only commas or spaces.

Step 3 — Normalize emails

Lowercase and trim. Domains are case-insensitive; treating Jane@Example.com and jane@example.com as different people creates fake duplicates.

=LOWER(TRIM(B2))
Enter fullscreen mode Exit fullscreen mode

Light checks before import: exactly one @, no spaces. I usually skip heavy regex validation in Sheets — lowercase + trim is enough for Make/Sheets and avoids false negatives on unusual but real addresses.

Step 4 — Normalize phones (US-ish exports)

Pick one target format. For US lists going into Sheets/Make I use: digits only, 10 digits when possible, stored as text so Sheets doesn't eat leading zeros or turn values scientific.

  1. Set the phone column to Plain text before pasting cleaned values.
  2. Strip non-digits: 
=REGEXREPLACE(C2, "[^0-9]", "")
Enter fullscreen mode Exit fullscreen mode
  1. If the result is 11 digits and starts with 1, drop the leading 1.
  2. If it isn't 10 digits after that, flag the row — don't invent a number.

Add a phone_ok column (TRUE/FALSE) so Make can route bad rows to "needs review" instead of failing the whole scenario.

Step 5 — Dedupe on the right key

Don't dedupe on name. Use email for customer lists, or order id for transactions.

In Sheets: Data → Data cleanup → Remove duplicates → check only email (or order_id) → review the removed count before closing.

If two rows share an email but different amounts or statuses (refund vs charge), decide the keep rule up front: latest by date, status paid, or keep both and mark one duplicate_of. Blind dedupe without a rule is how refunds disappear from a finance sheet.

Step 6 — Split names only when required

If Make or your CRM wants first_name / last_name and the export only has Full Name, split carefully — multi-part names still need a human pass. Don't over-automate if you're mailing real people. Keeping full_name is often cleaner for Sheets reporting.

Step 7 — Export a clean UTF-8 CSV

When the sheet looks right: File → Download → Comma-separated values (.csv). Open it once in a plain text editor and glance at the first 20 lines — no blank header names, emails lowercased, phones consistent. That file is what you feed Make's CSV module or re-import as the "clean" tab your automations read.

Reusable checklist

  • [ ] Headers → snake_case
  • [ ] TRIM all text columns
  • [ ] Drop blank rows
  • [ ] Lowercase emails
  • [ ] Phones → digits, 10-digit US where possible, stored as text
  • [ ] Dedupe on email (customers) or order id (orders) with an explicit keep rule
  • [ ] Split names only if required
  • [ ] Spot-check 10 random rows
  • [ ] Download UTF-8 CSV and peek in a text editor

When the file is bigger than "I'll just fix it in Sheets"

Manual cleanup is fine under a few hundred rows. Past that — or when you're cleaning the same Stripe/Shopify/Gumroad shape every week — a small, repeatable cleaner saves time.

I built OpsCSV for this workflow: drop a CSV in the browser, trim, dedupe, normalize phones/emails, optionally apply a Stripe / Shopify / Gumroad / Make / Sheets-oriented pass, then download. Processing stays on your device (nothing uploaded).

Use the checklist either way. The tool just runs the same steps faster.

If you're also wiring cleaned CSVs into Make + Sheets on a schedule, the companion Ops Kit (code FIRST10) covers that side — optional, not required for cleaning.

Wrap-up

Messy CSVs aren't a character flaw — they're what payment and store exports look like. Fix headers, trim, normalize email/phone, dedupe on a real key, then export. Do it the same way every time and Make/Sheets stop being fragile.

Questions about a specific export shape (Stripe Balance vs Payments, Shopify customers vs orders)? Leave a comment with the platform and roughly how many rows — happy to sketch the column map.

Top comments (0)