Blog

How to Clean a Messy CSV Export Before You Analyze It

A worked example of cleaning a real-looking CSV export in Excel or Google Sheets: repeated headers, totals rows, duplicates, mixed dates, and text amounts.

7 min read

All posts

Short answer: Before you chart or summarize a CSV export, do these steps:

  1. Import it with types turned off.
  2. Delete repeated header rows and totals rows.
  3. Remove true duplicates. Check whole rows, not just IDs.
  4. Standardize text with TRIM and PROPER.
  5. Convert amounts and dates explicitly.
  6. Leave blanks blank instead of turning them into zeros.
  7. Check outliers against the source.

Then compare your total with the source system's total.

It sounds like busywork. The worked example below shows why it isn't: on ten rows of sample data, a quick SUM gives $32,100. The cleaned answer is $5,740.

The example file

Example data, made up for this post. It's an October orders export that two people put together from two systems, which is how most real exports end up messy.

A: order_id B: order_date C: region D: customer E: amount
2 A-101 2026-10-03 West␠ Acme Co 1200
3 A-102 10/04/2026 west Birch LLC 450
4 A-103 2026-10-05 East Cedar Inc USD 980.00
5 A-103 2026-10-05 East Cedar Inc USD 980.00
6 A-104 05/10/2026 EAST Delta Partners
7 A-105 2026-10-07 North Elm & Co 12000
8 order_id order_date region customer amount
9 A-106 2026-10-08 North Fir Studio 760
10 A-107 2026-10-09 ␠West Acme Co 1150
11 Total 16540

(␠ marks a space you can't see in the cell.)

=SUM(E2:E11) returns 32,100. It skips the two "USD 980.00" cells because they're text. It counts a mistyped 12000. And it adds the export's own Total row on top of everything else. A pivot table by region shows six regions: "West ", "west", " West", "East", "EAST", and "North".

Step 1: Import with type conversion off

Spreadsheets guess types when you open a CSV. They turn some dates into numbers and leave others as text, and they drop leading zeros from IDs and ZIP codes. Import the file instead of double-clicking it:

  • Google Sheets: File → Import → Upload, then untick "Convert text to numbers, dates, and formulas."
  • Excel: Data → From Text/CSV → Transform Data, then set the date and amount columns to Text before loading.

Now every cell shows exactly what the export contained, and you convert each column on purpose.

Step 2: Delete rows that aren't records

Every row should be one record. Two rows in the example aren't:

  • Row 8 is a repeated header. That happens when someone pastes a second export underneath the first.
  • Row 11 is the source system's totals row.

Find them with a filter on order_id. Look for the value order_id and for blanks. Delete both rows.

Don't skip this step because "the SUM ignores text anyway". Row 11's 16540 is a number, and it alone doubles your total.

Step 3: Remove true duplicates, not just repeated IDs

Rows 4 and 5 are identical. That's a real duplicate. But a repeated ID doesn't always mean a duplicate. Many exports, including Shopify's orders export, give each line item of one order its own row, all with the same order number.

So flag duplicates across the whole row, not the ID alone. In a helper column F:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$D$2:D2,D2,$E$2:E2,E2)>1

This returns TRUE on the second and any later copy of an identical row. Filter for TRUE and delete those rows. In Excel, Data → Remove Duplicates with every column ticked does the same job. In Sheets, it's Data → Data cleanup → Remove duplicates.

Step 4: Standardize text

Region has three problems: a trailing space, a leading space, and inconsistent capitals. In a helper column:

=PROPER(TRIM(SUBSTITUTE(C2,CHAR(160)," ")))

The SUBSTITUTE handles non-breaking spaces, which often come along when text is copied from a web page. Microsoft's documentation notes that TRIM by itself does not remove the non-breaking space character (character 160). If TRIM "isn't working," that's usually why.

Paste the results back over column C as values. The pivot now shows three regions.

Step 5: Convert amounts to numbers

=IF(E2="","",VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(E2,"USD",""),",",""))))

This strips the currency code and thousands separators, then converts the result. "USD 980.00" becomes 980. If your export uses a comma as the decimal separator (1.200,50), swap the separators before you convert, or the result will be wrong.

Step 6: Fix dates, and don't guess the format

Look at rows 3 and 6:

  • A-102 says 10/04/2026.
  • A-104 says 05/10/2026.

Is 10/04 the 10th of April or October 4th? Is 05/10 May 10th or October 5th? The file is an October export, and the order IDs run in sequence. A-102 falls between Oct 3 and Oct 5, so it's October 4 (month first). A-104 falls between Oct 5 and Oct 7, so it's October 5 (day first). Two systems, two formats.

That's why you read context instead of trusting a guess. A quick test for any date column: if any value has a first number above 12, that column is day-first. If neither part ever goes above 12, check the IDs, the sequence, or the source system's settings.

Build the dates explicitly so nothing depends on your computer's locale:

Month-first text (A-102):  =DATE(RIGHT(B3,4),LEFT(B3,2),MID(B3,4,2))
Day-first text (A-104):    =DATE(RIGHT(B6,4),MID(B6,4,2),LEFT(B6,2))
ISO text (2026-10-03):     =DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))

Step 7: Leave blanks blank

A-104 has no amount. It's tempting to type 0. Don't. A zero says "this order was worth nothing" and pulls averages down. A blank says "we don't know," and AVERAGE and SUM skip it correctly. Flag it in a notes column and get the real value from the source.

Step 8: Check outliers against the source, then decide

A-105 is 12,000, while every other order is between 450 and 1,200. After pasting the converted amounts back into column E, flag values far from the median:

=IF(E2="","",E2/MEDIAN($E$2:$E$9)>5)

The median of the six amounts is 1,065, so 12,000 is about 11 times typical. Flagging tells you where to look. It doesn't tell you the answer. Large orders happen. In this example, the source invoice shows 1,200, so the 12,000 has an extra zero. Fix it and note the change. If the invoice had said 12,000, you'd keep it.

Step 9: Reconcile with the source

After cleaning:

Region Orders Amount
West 3 $2,800
East 2 (1 missing amount) $980
North 2 $1,960
Total 7 $5,740

The source system said 16,540. That figure included the 12,000 typo, so the corrected source total is 16,540 − 12,000 + 1,200 = 5,740. That matches. When your number and the source's don't match, find out why before you share anything. A short note like "1 order missing an amount, 1 typo corrected, 1 duplicate removed" makes your analysis easy to trust.

A cleaning checklist you can reuse

  1. Import with type conversion off.
  2. Delete repeated headers, totals, and note rows.
  3. Remove duplicates by whole row, not by ID.
  4. TRIM, remove CHAR(160), and fix capitals in text columns.
  5. Convert amounts explicitly.
  6. Work out each date format from context, then build dates with DATE().
  7. Leave unknowns blank and flag them.
  8. Flag outliers, check the source, then decide.
  9. Reconcile your total with the source total.

Where a tool helps, and where it doesn't

Software can speed up the finding part. Chartlet.AI, for example, shows an overview of each column after upload, and finds missing values and outliers when you ask, so you can see what needs fixing before you build anything. The judgment calls in Steps 6 and 8 are still yours: which date format a system used, and whether an order really was 12,000. If you want to see a profile of your own file, upload a CSV free. Chartlet.AI's Free plan needs no credit card.

FAQ

What is the first step in cleaning data?

Import the file without automatic type conversion, then delete rows that aren't records, such as repeated headers and totals rows. Everything else depends on having one record per row.

Should I replace blank values with zero?

No, unless a blank really means zero in your source system. A blank means unknown. Zeros distort averages and totals.

Why doesn't TRIM remove all the spaces?

The leftover spaces are usually non-breaking spaces (character 160). Use SUBSTITUTE(cell,CHAR(160)," ") before TRIM.

How do I tell if dates are day-first or month-first?

Look for any value where the first number is above 12. If none exists, use context: the order sequence, the export's date range, or the source system's locale settings.

Is a repeated order ID always a duplicate?

No. Line-item exports repeat the order ID on purpose. Compare whole rows before deleting anything.

Try it on your own file.

Add a spreadsheet, ask your question, and have your first chart in about a minute.

Free plan · No credit card · Cancel any time