How to Clean and Export a Data Extract
Data arrives messy. Trailing spaces, inconsistent capitalisation, duplicate rows, blanks where there should be values. Here are the SQL recipes that fix each one, and the loop that makes the whole job quick.
The loop
Select the file. Query it. Export. Repeat. Because nothing is saved and the source file is never modified, you can iterate as recklessly as you like — there is no state to corrupt and no undo needed.
One button makes this genuinely fast: the ↻ reload control on each file row re-reads the file from disk. Fix something in the source, hit reload, re-run your query. No removing and re-adding.
First, see the damage
SELECT * FROM D LIMIT 50
Then count what you are dealing with:
SELECT COUNT(*) AS total_rows FROM D
The recipes
Trailing and leading whitespace
The most common invisible problem — "Austin " and "Austin" are different values, which quietly breaks grouping and joins.
SELECT TRIM(city) AS city, COUNT(*) AS n
FROM D
GROUP BY TRIM(city)
ORDER BY n DESC
To find out whether you have the problem at all, compare the counts:
SELECT COUNT(DISTINCT city) AS raw,
COUNT(DISTINCT TRIM(city)) AS trimmed
FROM D
If those two numbers differ, whitespace is splitting your categories.
Inconsistent capitalisation
SELECT UPPER(TRIM(country)) AS country, COUNT(*) AS n
FROM D
GROUP BY UPPER(TRIM(country))
Combining UPPER and TRIM catches both problems at once, which is usually what you want.
Duplicate rows
Find them first — never delete blind:
SELECT email, COUNT(*) AS n
FROM D
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC
Then export a deduplicated set:
SELECT DISTINCT * FROM D
Or, to keep one row per key with chosen columns:
SELECT email, MIN(first_name) AS first_name, MAX(signup_date) AS signup_date
FROM D
GROUP BY email
Blanks and nulls
-- how bad is it?
SELECT COUNT(*) AS missing FROM D WHERE phone IS NULL OR phone = ''
-- drop those rows
SELECT * FROM D WHERE phone IS NOT NULL AND phone <> ''
-- or fill them
SELECT name, COALESCE(phone, 'not provided') AS phone FROM D
Renaming and reordering columns
Export-ready naming, without touching the source:
SELECT customer_id AS "Customer ID",
TRIM(name) AS "Customer Name",
amount AS "Order Value"
FROM D
AS total will fail with a parse error. Use AS total_value, or double-quote it as AS "Total".Categorising rows
SELECT name,
amount,
CASE WHEN amount >= 1000 THEN 'large'
WHEN amount >= 100 THEN 'medium'
ELSE 'small' END AS band
FROM D
Finding the outliers
-- suspiciously large or negative
SELECT * FROM D WHERE amount < 0 OR amount > 100000
-- values that do not look like an email
SELECT * FROM D WHERE email NOT LIKE '%@%'
Clean a file without ever modifying the original.
Open the tool →Putting it together
A realistic cleaning pass in one statement:
SELECT DISTINCT
TRIM(customer_id) AS customer_id,
TRIM(name) AS name,
UPPER(TRIM(country)) AS country,
COALESCE(phone, '') AS phone,
amount
FROM D
WHERE customer_id IS NOT NULL
AND TRIM(customer_id) <> ''
AND amount >= 0
ORDER BY name
Exporting the result
Click Export and choose CSV, JSON, Excel or Parquet. You are exporting the result of your query, not the original file — so the cleaned, filtered, renamed version is what lands in your downloads folder. The source file on disk is untouched.
A sanity check before you send it
Compare before and after. If the row count moved more than you expected, find out why before passing the file on:
SELECT COUNT(*) AS rows_after
FROM (SELECT DISTINCT * FROM D WHERE amount >= 0)